Need to create custom report for software metering.

TechUST 416 Reputation points
2023-01-18T18:29:15.3833333+00:00

Hi @AllenLiu-MSFT /All,

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 Asset- Software Metering.JPG SQL query to get report as i have attached.

Report Description - List of all devices that have run a specific Software Metering Rule which are member of the selected collection.

Asset - Software Metering.pdf

Its humble request that please help me on this since its highly Urgent.

Microsoft Configuration Manager
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2023-01-19T06:41:16.2266667+00:00

    Hi, @TechUST

    Thank you for posting in Microsoft Q&A forum.

    Have we tried the default reporting:

    Computers that have a metered program installed but have not run the program since a specified data?

    1

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. TechUST 416 Reputation points
    2023-01-19T09:36:57.79+00:00

    Hi @AllenLiu-MSFT Yes i have already this default report this is not what we required Report-software metering.JPG

    I need report as per this snapshot Asset- Software Metering.JPG where i can filter with rule & collection also need column as per attached snapshot. kindly help.

    0 comments
  3. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2023-01-20T06:39:38.9033333+00:00

    Hi, @TechUST

    Thank you for posting in Microsoft Q&A forum.

    I haven't found a view with metered rule name, but we can use file name to replace it, so I wrote the SQL query.

    Replace the FileName and collection name to see if it works:

    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

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 = '

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  4. AllenLiu-MSFT 40,316 Reputation points Microsoft Vendor
    2023-01-26T08:39:26.2666667+00:00

    Hi, @TechUST

    Sorry for the late, I just came back from vacation.

    And I'm sorry I did not check the query again after I copy and paste it, it seems the code blocks have a word limit, so some words missed in the end.

    It should be:

    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'

    The FileName is not the rule name, it's the file name in your metering rule like this:

    1

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Add comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  5. TechUST 416 Reputation points
    2023-01-26T11:13:48.3833333+00:00

    Hi @AllenLiu-MSFT Again thank you for replying on my query.

    I have created report with query suggested by you but unable to get any output also in report i can not see option to select collection and metering rule. if there any way that we can connect on teams .

    User's image