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!

June 7th, 2011 4:09pm

why dont use the default reports with category "software metering" ?
Free Windows Admin Tool Kit Click here and download it now
June 8th, 2011 5:24am

I completely agree with Eswar.

 

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

June 8th, 2011 7:19am

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!

Free Windows Admin Tool Kit Click here and download it now
June 8th, 2011 2:49pm

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.

August 21st, 2011 4:54pm

and how do you remove the prompt and replace with a metered rule?
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2012 3:46pm

So Radamass, Im 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
May 25th, 2012 10:50am

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.

Free Windows Admin Tool Kit Click here and download it now
April 18th, 2013 10:24pm

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

April 20th, 2013 4:07pm

why dont use the default reports with category "software metering
Free Windows Admin Tool Kit Click here and download it now
August 14th, 2013 7:50am

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

August 14th, 2013 2:23pm

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?

Free Windows Admin Tool Kit Click here and download it now
January 29th, 2014 11:59am

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"?

March 20th, 2015 10:27pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics