Need to create custom report for software metering.
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.
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.
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.