Software Metering reports

[dinci5]

Hi there,

 

I really, really dislike the Software Metering reports in ConfigMgr.

I'm not that skilled in WQL/SQL to create custom queries myself.

 

Does anybody have an idea how to create a custom query that will:

1. List all devices where a metered software has run in the last 90 days
2. List all devices where a metered software has NOT run in the last 90 days
3. Include in report: Device Name, Username, mast run date

Thank you

[GarthMJ]

Have you looked at the built-in reports for this?

1. Computers that have run a specific metered software program
2. Computers that have a metered program installed but have not run the program since a specified date

[dinci5]

 

Have you looked at the built-in reports for this?

1. Computers that have run a specific metered software program
2. Computers that have a metered program installed but have not run the program since a specified date

 

 

Hi Garth,

 

In the first report you have to choose a month and a year.

So you have to export multiple reports and combine them manually.

 

In both reports you only see the hostnames and no logged on users.

 

The reports are very limited.

We, for instance, need to check if a software is not used in the last 3 months and uninstall it from that computer so we can use that license for someone who will actually use the software.

 

---

 

What I really want is to have an SQL query to read this data so I can refresh it in Excel using Power Query.

I do this for many other reports. With a push of a button you have the latest stats.

 

Now, I have created a WQL reporting query that gives me exactly what I need.

 

 

The query is:

select SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.LastLogonUserName,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.OperatingSystemNameandVersion,
SMS_MonthlyUsageSummary.LastUsage

from SMS_R_SYSTEM 
inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID = SMS_MonthlyUsageSummary.ResourceID
inner join SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID

WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 90 AND SMS_MeteredFiles.RuleID = 16777318

-> TIP: The RuleID is actually the Internal Rule ID (you have to show that culomn in the Assets and Compliance > Software Metering view)

 

The only problem is that It displays some hostnames multiple times with different "Last Usage" time.

 

---

 

Can I, somehow, get hold of the query that is executed when a Software Metering report is generated?

[GarthMJ]

So clone that report and edit it to give you the last 90 days.

 

It is fairly easy to add the last logon user or top console user. Again, if you clone the report you can add anything.

 

That is what I love about SSRS report you can create any report that you like and it will do anything that you want, it just takes time to create it.

 

Personally I would avoid using Excel only because you are adding an extra step each month, instead I would create a SSRS will all the data that is need and formatted exactly how you want it to reduce the effort involved in this.

 

I took the time to convert and clean up your query to give you the best results. I commented out the Rule # for my testing, you will need to undo that.

select

RV.Netbios_Name0,

RV.User_Name0,

RV.Resource_Domain_OR_Workgr0,

OS.Caption0,

MUS.LastUsage

from

v_R_SYSTEM_Valid RV

inner join v_MonthlyUsageSummary MUS on RV.ResourceID = MUS.ResourceID

inner join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID

left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID

WHERE

DateDiff(day, MUS.LastUsage, GetDate()) < 90 --AND MF.RuleID = 16777318

 

 

You can edit the report to get the query within any report.

[dinci5]

So clone that report and edit it to give you the last 90 days.

 

It is fairly easy to add the last logon user or top console user. Again, if you clone the report you can add anything.

 

That is what I love about SSRS report you can create any report that you like and it will do anything that you want, it just takes time to create it.

 

Personally I would avoid using Excel only because you are adding an extra step each month, instead I would create a SSRS will all the data that is need and formatted exactly how you want it to reduce the effort involved in this.

 

I took the time to convert and clean up your query to give you the best results. I commented out the Rule # for my testing, you will need to undo that.

select

RV.Netbios_Name0,

RV.User_Name0,

RV.Resource_Domain_OR_Workgr0,

OS.Caption0,

MUS.LastUsage

from

v_R_SYSTEM_Valid RV

inner join v_MonthlyUsageSummary MUS on RV.ResourceID = MUS.ResourceID

inner join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID

left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID

WHERE

DateDiff(day, MUS.LastUsage, GetDate()) < 90 --AND MF.RuleID = 16777318

 

 

You can edit the report to get the query within any report.

Thank you for cleaning up my code.

 

 

SSRS seems nice indeed, but I really have not that much experience with it not with SQL. As you could see, my code is very messy.

 

I don't know how to duplicate the existing queries so I can edit them

[GarthMJ]

Actually your WQL code wasn't that bad. I only fixed two things (really), ensured that you only get active PCs and that you get the real OS name.

 

If you want to learn more about reporting, <sorry for the shameless self promotion> this book can help you out https://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/ or I will be presenting at IT/Dev connections too http://www.itdevconnections.com/dc16/Public/Enter.aspx

[dinci5]

Actually your WQL code wasn't that bad. I only fixed two things (really), ensured that you only get active PCs and that you get the real OS name.

 

If you want to learn more about reporting, <sorry for the shameless self promotion> this book can help you out https://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/ or I will be presenting at IT/Dev connections too http://www.itdevconnections.com/dc16/Public/Enter.aspx

 

I'm from Europe, so I'll skip the presentation

 

This query is actually good.

Problem is it gives me multiple hostnames as it is a monthly report.

So if a software is used every month, the results will show the most recent usage per month.

 

I now have to figure out how to select only all devices with the most recent LastUsage date

[GarthMJ]

I could have sworn that I added the Max function to this query.

select Distinct
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0,
       max(MUS.LastUsage)
from
       v_R_SYSTEM_Valid RV
       inner join v_MonthlyUsageSummary MUS on RV.ResourceID = MUS.ResourceID
       inner join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID
       left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
WHERE
       DateDiff(day, MUS.LastUsage, GetDate()) < 90 --AND MF.RuleID = 16777318
Group by
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0
Order by
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0

BTW, I will tell you to pay attend to my blog too and I post many helpful tips there too.

[dinci5]

I could have sworn that I added the Max function to this query.

select Distinct
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0,
       max(MUS.LastUsage)
from
       v_R_SYSTEM_Valid RV
       inner join v_MonthlyUsageSummary MUS on RV.ResourceID = MUS.ResourceID
       inner join v_MeteredFiles MF ON MUS.FileID = MF.MeteredFileID
       left outer join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
WHERE
       DateDiff(day, MUS.LastUsage, GetDate()) < 90 --AND MF.RuleID = 16777318
Group by
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0
Order by
       RV.Netbios_Name0,
       RV.User_Name0,
       RV.Resource_Domain_OR_Workgr0,
       OS.Caption0

BTW, I will tell you to pay attend to my blog too and I post many help tips there too.

 

Thanks a lot.

This is exaclty what I need.

I'll check your blog out for sure.