Sign in
Microsoft.com
 
System Center Configuration Manager TechCenter
 
 
 
System Center Configuration Manager TechCenter > System Center Configuration Manager Forums > Configuration Manager Inventory > Software Metering - Custom Report
Ask a questionAsk a question
Search Forums:
 

AnswerSoftware Metering - Custom Report

  • Wednesday, October 21, 2009 12:52 PMsveske Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote

    Hi
    I'm trying to add some extra infoemations to one of the standard Software Metering Reports.

    declare @TimeKey int
    declare @days float

    set @TimeKey=100*@Year+@Month

    select @days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
    from v_SummarizationInterval where TimeKey=@TimeKey

    if IsNull(@days,0) > 0
      select mu.FullName,
           DATEADD(ss,@__timezoneoffset,MAX(mus.LastUsage)) as LastUsage,
           SUM(UsageCount) + SUM(TSUsageCount) as C021,
           ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as C022,
           ROUND(SUM(UsageTime)/60.0,2) as C023,
           ROUND(SUM(UsageTime)/60.0/(SUM(UsageCount) + SUM(TSUsageCount)),2) as C024,
           ROUND(SUM(UsageTime)/60.0/@days,2) as C025
      from v_MeteredUser mu
      join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.MeteredUserID
      join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
      where mf.ProductName = @RuleName
      and mus.TimeKey = @TimeKey
      group by mu.FullName
      having SUM(UsageCount) + SUM(TSUsageCount) > 0
      order by  mu.FullName


    I'm trying to customize the report so it will also show:

    NetBios Name
    Filename and Fileversion of the metered software
    Inventoried file "%.xsd" on the machines


    Explanation: The metered software is a softphone that provides telephony functionality and the filename *.xsd is actually the phonenumber on the client.


    Normally, when I make custom reports, I "import" a standard report into SQL and then make the adjustments in there.
    But I'm not able to "import" those "Software Metering" reports without getting some error messages.

    I've tried to make the adjustments manually without any luck, just a lot of errors. And I'm not an SQL-expert.... ;-)


    Best regards 

     

     

Answers

  • Wednesday, October 21, 2009 12:55 PMSherry KissingerMVP, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    I think you are confusing Software Inventory (where you could ask to inventory *.xsd files on a client) and Software Metering (where an executable is launched, and that .exe is tracked for start and stop times).

    Software Metering by it's very nature can only monitor launched applications; not what accompanying, non-application, files are launched with that application.
    Standardize. Simplify. Automate.
    • Proposed As Answer byGarth JonesMVPWednesday, October 21, 2009 1:48 PM
    • Marked As Answer bysveske Tuesday, October 27, 2009 6:28 AM
    •  
     

All Replies

  • Wednesday, October 21, 2009 12:55 PMSherry KissingerMVP, ModeratorUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    I think you are confusing Software Inventory (where you could ask to inventory *.xsd files on a client) and Software Metering (where an executable is launched, and that .exe is tracked for start and stop times).

    Software Metering by it's very nature can only monitor launched applications; not what accompanying, non-application, files are launched with that application.
    Standardize. Simplify. Automate.
    • Proposed As Answer byGarth JonesMVPWednesday, October 21, 2009 1:48 PM
    • Marked As Answer bysveske Tuesday, October 27, 2009 6:28 AM
    •  
     
  • Friday, October 23, 2009 9:18 AMsveske Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Thanks,

    Yes I might have misunderstod something regarding Software Metering. Just started to implement it.. :-)

    Afterwards I have talked with the person who regusted the report.
    He only needs to see when the .exe file is last used in a given month and year.

    Regarding the *.xsd file:
    It's a file which is located in a folder on the clients (I scan for that filetype in Software Inventory)


    I have tried this:

    SELECT     TOP (100) PERCENT SYS.Netbios_Name0, SYS.User_Name0, dbo.v_GS_SoftwareFile.FileName AS Phonenumber, PFI.FileName, PFI.FileVersion,
                          MD.LastUsage
    FROM         dbo.v_MonthlyUsageSummary AS MD INNER JOIN
                          dbo.v_ProductFileInfo AS PFI ON MD.FileID = PFI.FileID INNER JOIN
                          dbo.v_R_System AS SYS ON MD.ResourceID = SYS.ResourceID INNER JOIN
                          dbo.v_GS_SoftwareFile ON SYS.ResourceID = dbo.v_GS_SoftwareFile.ResourceID
    WHERE     (PFI.FileName = 'Siemens.OptiClient.exe') AND (MD.LastUsage > CONVERT(DATETIME, '2009-10-01 00:00:00', 102)) AND
                          (dbo.v_GS_SoftwareFile.FileName LIKE '[0-9]%.xsd')
    ORDER BY SYS.Netbios_Name0, PFI.FileName


    Which actually seems to work.

    But the question regarding this, is if it's possible to prompt for "Month" and "Year" so the result will show when the .exe file has last been executed in a given month, f.ex July.
    In the above, I just put in "MD.LastUsage > CONVERT(DATETIME, '2009-10-01 00:00:00', 102)" to see if I get a result.

    I hope this makes sense. (I have no idea what the person requsting the report, will use it for... :-) )

    Thanks in advance