"Users that have run a specific metered software program" for longer than selected month/year

Question

Looking for a way to use the built in Software Metering report 'Users that have run a specific metered software program' to show usage of a selected metered software for either multiple months or date range that could be as long as 6 months.

Answer 1

A starting point...

DECLARE @NameInMeteredRules nvarchar(100) = 'What you called it in the console for that rule name'

select

mu.FullName, u.Full_User_Name0

,mus.TimeKey as 'YearMonth'

,s1.Netbios_Name0 as 'ComputerName'

from v_MeteredUser mu

left join v_R_User u ON mu.FullName = u.Unique_User_Name0

join v_MonthlyUsageSummary mus on mu.MeteredUserID=mus.MeteredUserID

join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID

join v_r_system s1 on s1.resourceid=mus.ResourceID

where mf.SecurityKey = (select SecurityKey from v_MeteredProductRule where ProductName = @NameInMeteredRules)

and mus.timekey in (202404,202405)

Example... mus.timekey in ( ) is the yearmonths you want. i.e., if you want November, December 2024, and January 2025, it would be mus.timekey (202412,202411,202501)

This is because for this specific report, you can't easily put in a time range of 2024-01-01 to 2024-03-15 (for example), but since the summarization oh so conveniently (for us) happens to name the timekey as yearmonth, like 202412, you can guess what you need to put in that where clause.

Answer 2

Hi, @MPPool

Thank you for posting in Microsoft Q&A forum.

You may try to run Sherry's query from SQL Server Management Studio like below, new a query from CM_<Site code>.