Need to create custom report for software metering.
Poll
Hi expert,
I need help to create custom report for software metering to see usages details of Adobe acrobat Standard & pro. Rule is already created. i need SQL query to get report as i have attached.
Required report
Distinct SCCM Software Metering Report Sections
Details : Shows the name of the device, username
Rule Name : Display the Software Metering rule name
Collection Name : Show the selected collection name
Month without usage: Display the period of time that the software hasn’t run. Green means less than a month.
Last Usage : Shows details about the last date the software has been executed
Last Hardware : The last hardware inventory time
I have written query but not getting any return output.
Select
name0,sud.FileName,c.name,
Case
when sud.EndTimeLocal > GETDATE()-30 then ‘< 1 month’
when sud.EndTimeLocal > GETDATE()-60 and sud.EndTimeLocal < GETDATE()-30 then ‘betweem 1 and 2 months’
when sud.EndTimeLocal > GETDATE()-180 and sud.EndTimeLocal < GETDATE()-60 then ‘betweem 2 and 6 months’
else ‘> 6 months’
end as ‘Months Without Usage’,
sud.EndTimeLocal as ‘Last Usage’
from v_GS_SoftwareUsageData sud
inner join v_R_System vrs on vrs.ResourceID=sud.ResourceID
inner join v_FullCollectionMembership fcm on fcm.ResourceID = vrs.ResourceID
inner join v_Collection c on c.CollectionID = fcm.CollectionID
where sud.FileName = ‘Adobe.exe’
and c.CollectionID = ‘xxxxxxx’
Report Description – List of all devices that have run a specific Software Metering Rule which are member of the selected collection.
Answers ( 2 )
Software Metering has some default report…
you can use that report builder and default reports to make custom reports.. if you want to https://www.anoopcnair.com/sccm-create-custom-report-using-report-builder-configmgr-part-1/
Or you can this tip using log file to build custom reports SCCM metering
https://www.anoopcnair.com/easiest-method-to-create-sql-queries-for-sccm/
thank you for reply, i have rdl file, can you please help me to edit