none
Help with custom SCCM report - Software Metering over a specific time

    שאלה

  • Hello all.  I am needing some guidance as my SQL abilities are limited.  I am needing to make a SCCM report that shows the data for a specific software metering rule, using data for the past two months.  What fields are important to us in the report results, is the PC name, Total Usages, Total Duration (min) and last usage.

     

    I have some experience with creating reports, but I am pretty much learning as I go.  I just need these report a little quicker than what it will take me to figure it out.  Any help would be appreciated!

    יום שלישי 07 יוני 2011 16:09

תשובות

כל התגובות

  • why dont use the default reports with category "software metering" ?
    //Eswar Koneti @ www.eskonr.com
    יום רביעי 08 יוני 2011 05:24
  • I completely agree with Eswar.

     

    Report 115 should do the trick for you I would say. 

    יום רביעי 08 יוני 2011 07:19
  • I completely agree with Eswar.

     

    Report 115 should do the trick for you I would say. 

    Report 115 is "Users that have run a specific metered software program", but Report 116 is "Computers that have run a specific metered software program", so Report 115 definitely wouldn't do the trick. :)

     

    And the reason (which I should have specified above) that I can't use the report 116, is that it doesn't not give me the data I want.  It allows me to specify a specific month to pull data, but instead I want to take the past 6 months of data and get results for this metered software.  Also, another reason I don't want to use this report 116 is that I won't be able to throw that in a dashboard, since it has variable that you have to input before opening the report.  

    I am making a dashboard for a specific application that we are about to upgrade.  I want to be able to give the link of this dashboard to the IT support for this application, instead of telling them to go to the SCCM reporting page, go to this report, type this, blah blah...I want it to be, here is a link...click it and here is all your info.

     

    Please advise what I could do to achieve this.  Thanks!

    יום רביעי 08 יוני 2011 14:49
  • The way to achieve this is to clone report 116 and remove the prompts, them have it limited to the last 6 months using the SQL Getdate and DateDiff functions.


    http://www.enhansoft.com/
    יום ראשון 21 אוגוסט 2011 16:54
    מנחה דיון
  • and how do you remove the prompt and replace with a metered rule?
    יום חמישי 24 מאי 2012 15:46
  • So Radamass, I’m not sure what answer you are looking for or how best to answer this.

    • Clone the report
    • Replace mf.ProductName = @RuleName with mf.ProductName = <Rule name>
    • Delete RuleName prompt

    http://www.enhansoft.com/

    יום שישי 25 מאי 2012 10:50
    מנחה דיון
  • The way to achieve this is to clone report 116 and remove the prompts, them have it limited to the last 6 months using the SQL Getdate and DateDiff functions.


    http://www.enhansoft.com/

    Garth, would you mind listing exactly how I would limit it to X number of months using GetDate and DateDiff functions in a cloned 115 report (or 116)? Or even better, if you could post the full SQL query. I've tried to edit a clone of 115 and I can't seem to get the date to work correctly. I need to get a usage report over X number of months. It'd be nice if I could prompt for a specific date range but even if the month is hardcoded, that would be better than my current process. Right now, in order to get a usage report over multiple months, I have to run 115 for each individual month and then copy the results for each month into Excel and massage all the data. Or am I missing something and there's an easier way?
    יום חמישי 18 אפריל 2013 22:24
  • There are lots of example on my blog of how to use datediff within Reporting or collections.

    http://smsug.ca/search/SearchResults.aspx?q=datediff


    http://www.enhansoft.com/

    שבת 20 אפריל 2013 16:07
    מנחה דיון
  • why dont use the default reports with category "software metering" ?
    //Eswar Koneti @ www.eskonr.com

    Because you have to select "one" month. You cannot select multiple months.

    I also want to gather a report like this. The default reports in SCCM for software metering are pretty limited. 

    יום רביעי 14 אוגוסט 2013 07:50
  • Because you have to select "one" month. You cannot select multiple months.

    I also want to gather a report like this. The default reports in SCCM for software metering are pretty limited. 

    I finally figured out how to do this. Here's the SQL if you want to try it. You have to make a prompt for "RuleName", "Month", and "Year" and this'll show you all of the metering data from the month/year you select until the current date. I never could figure out how to make it work between two specific dates though.

    declare @TimeKey int
    declare @days float
    declare @currTimeKey int
    declare @currYear float
    declare @currMonth float
    declare @currDay float
    set @TimeKey=100*@Year+@Month
    select @CurrYear=datepart(yyyy, getDate())
    select @CurrMonth=datepart(m, getDate())
    select @CurrDay=datepart(d, getDate())
    set @currTimeKey=100*@currYear+@currMonth
    select @Days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
    from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @currTimeKey
    set @Days = @Days+@CurrDay
    if IsNULL(@days,0) > 0
      select sys.Netbios_Name0, sys.User_Name0,
           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_R_System sys
      join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
      join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
      left join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceID
      where mf.ProductName = @RuleName
      and mus.TimeKey >=@TimeKey
      group by sys.Netbios_Name0, sys.User_Name0
      having SUM(UsageCount) + SUM(TSUsageCount) > 0
      order by sys.Netbios_Name0

    יום רביעי 14 אוגוסט 2013 14:23
  • Thanks for the SQL.

    When I use your SQL I get an error:
    Argument data type nvarchar is invalid for argument 2 of dateadd function.

    Do I need to change something else?

    יום רביעי 29 ינואר 2014 11:59
  • Thanks for the hints. 

    Do we need to modify the line from "select @Days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @currTimeKey" to "select @Days=@Days+DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @currTimeKey"

    If not do that, @Days will only be set base on the last matched "IntervalStart"?

    שבת 21 מרץ 2015 02:24
  • Hi,

    effectively , number of days is always 30+ current day...

    I tried with @days=@Days +...   no record are find

    יום רביעי 20 יולי 2016 19:27
  • Hi

    Did anyone ever get this working.

    If so do you have a copy of the RDL report you could share as I cannot get mine to work.

    • הוצע כתשובה על-ידי watson49 יום שלישי 20 יוני 2017 14:12
    • הצעה כתשובה בוטלה על-ידי watson49 יום שלישי 20 יוני 2017 14:12
    יום שלישי 20 יוני 2017 13:12
  • Same question as watson49, did anyone ever get this working?

    If so can you please supply the answer?

    יום רביעי 02 מאי 2018 14:40
  • Here's the SQL I use. I assume you could make it into a report but I use it straight from SQL Management Studio. The only two things you need to modify for the report to run are the <NAME OF SWM RULE> near the end of the code. That's self-explanatory - just has to be the name of the SW metering rule. The 2nd thing to edit is the @TimeKey on line 8. That needs to be in the format of 100*YYYY*MM as shown below. That's the date from which you want the SWM to go back to and it will return all data from that date until today. I've yet to find a way to get metering data between two specific dates (ie: 2018-01 and 2018-04). I can only get it to return a specific date and today. Hope this helps. 

    declare @TimeKey int
    declare @days float
    declare @currTimeKey int
    declare @currYear float
    declare @currMonth float
    declare @currDay float
    
    set @TimeKey=100*2018+01
    
    select @CurrYear=datepart(yyyy, getDate())
    select @CurrMonth=datepart(m, getDate())
    select @CurrDay=datepart(d, getDate())
    
    set @currTimeKey=100*@currYear+@currMonth
    
    select @Days=DATEDIFF(day,IntervalStart,DATEADD(month,1,IntervalStart))
    from v_SummarizationInterval where TimeKey>=@TimeKey and TimeKey< @currTimeKey
    
    set @Days = @Days+@CurrDay
    
    if IsNULL(@days,0) > 0
    	select sys.Netbios_Name0 'Asset Tag', 
    	UPPER(SUBSTRING(SCU.TopConsoleUser0, CHARINDEX('\', SCU.TopConsoleUser0) + 1, LEN(SCU.TopConsoleUser0))) as 'User ID',
    	USR.Full_User_Name0 'Full Name', USR.physicalDeliveryOfficeNam0 'Office', mf.ProductName, 
           DATEADD(ss,-500,MAX(mus.LastUsage)) as 'Last Usage',
           SUM(UsageCount) + SUM(TSUsageCount) as 'Total Usage',
           --ROUND((SUM(UsageCount) + SUM(TSUsageCount))/@days,2) as 'Avg Duration (min)',
           ROUND(SUM(UsageTime)/60.0,2) as 'Usage (min)'
    from v_R_System sys
      LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE AS SCU ON SCU.ResourceID = sys.ResourceID
      JOIN v_R_User USR on SUBSTRING(SCU.TopConsoleUser0, CHARINDEX('\', SCU.TopConsoleUser0) + 1, LEN(SCU.TopConsoleUser0)) = USR.User_Name0
      join v_MonthlyUsageSummary mus on sys.ResourceID=mus.ResourceID
      join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID
      left join v_RA_System_SMSInstalledSites inst on sys.ResourceID=inst.ResourceID
      where mf.ProductName like '%<NAME OF SWM RULE>%'
      and mus.TimeKey >=@TimeKey
      group by sys.Netbios_Name0, SCU.TopConsoleUser0, USR.Full_User_Name0,mf.ProductName,USR.physicalDeliveryOfficeNam0
      having SUM(UsageCount) + SUM(TSUsageCount) > 3
      order by 'Last Usage', 'Total Usage'

    יום חמישי 03 מאי 2018 18:17
  • Thank you JBinNC, this has answered my question.
    יום שישי 04 מאי 2018 08:01