Powershell/query: get last used time of an application?

Article
2020-02-26

Question

_{Wednesday, February 26, 2020 4:35 PM}

Hi,

I'd need to do some actions on an application (Project.exe) when it was used > 3 months ago.
Software metering works fine for the exe, also the SSRS report from SCCM shows some data (but only per months which is not usable because I'd need for last 3 months, report for reference is SoftwareMetering__Users that have run a specific metered software program.

How can I get the data out of SCCM via Powershell?

Note: found this
https://docs.microsoft.com/en-us/powershell/module/configurationmanager/get-cmsoftwaremeteringrule?view=sccm-ps
which shows me meteringrule but not the result

Also found this: https://sccmtips.co.uk/sql-query-for-summarizing-software-metering-usage
but cannot make that work for me

Please advise.

Jan Hoedt

Update:This gives a result but can't make any sense of it, found it here
https://www.windows-noob.com/forums/topic/14160-software-metering-reports/

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

There is also the report

Software Metering __Computers that have a metered program installed but have not run the program since a specified date

But we deploy to users not computers :-(

All replies (16)

_{Wednesday, February 26, 2020 6:35 PM | 1 vote}

Take a look at recently used apps, you will get better results from it.

Garth Jones

Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

_{Wednesday, February 26, 2020 7:53 PM}

Thanks, but not a clue what you mean by that.

I'd need to know the users that didn't use Project.exe for last 3 months .... Where can this 'recently used apps' (is it a tab somewhere in sccm that I can reach via Powershell? since I do need to automate so need to be able to reach it via Powershell).

The only thing I need is a Powershell table with username, last usage time stamp of the Project.exe

Metering is in place and works fine.

Jan Hoedt

Update: found this post: https://social.technet.microsoft.com/Forums/en-US/e95ccc2f-559a-43b2-afc9-dd9a0a3cb8da/difference-between-software-metering-and-recently-used-applications-in-sccm-2012?forum=configmanagersecurity

And tried this

select
sys.name0,
uap.LastUserName0,
uap.LastUsedTime0,
uap.ProductName0,
uap.ExplorerFileName0,
uap.FileDescription0,
uap.FileSize0
from v_GS_CCM_RECENTLY_USED_APPS uap
join v_R_System sys on uap.ResourceID = sys.ResourceID
where ExplorerFileName0 in ('project.exe')

As a query within SCCM but I get 'invalid view' when I execute it.

... but since it seems it doesn't get the username, it is not usefull for me.

_{Wednesday, February 26, 2020 8:02 PM | 1 vote}

See v_GS_CCM_RECENTLY_USED_APPS

and the result in resource explorer.

Garth Jones

Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

_{Wednesday, February 26, 2020 8:29 PM}

Thanks but please read my post.

There is no username visible in your approach

Jan Hoedt

_{Wednesday, February 26, 2020 9:12 PM | 1 vote}

Thanks but please read my post.

There is no username visible in your approach

Jan Hoedt

Look again. There is a user name listed as the second last column.

Garth Jones

Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

_{Wednesday, February 26, 2020 9:21 PM}

Thanks and sorry about that.

So why are metering rules there then?

Tried this post: https://social.technet.microsoft.com/Forums/en-US/e95ccc2f-559a-43b2-afc9-dd9a0a3cb8da/difference-between-software-metering-and-recently-used-applications-in-sccm-2012?forum=configmanagersecurity

And tried

As a query within SCCM but I get 'invalid view' when I execute it.

Jan Hoedt

_{Wednesday, February 26, 2020 9:45 PM | 1 vote}

Recently used apps (RUA) is different from SWM in that, RUA only record the last usage. SWM records all usages.

RUA needs SWM to just be enabled to work. Where as SWM, needs a rule setup to collect the data.

The query listed above is SQL not WQL (WQL = for a collection) (SQL = Reporting plus you can do some much more with it, including powershell stuff)

Garth Jones

Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

_{Thursday, February 27, 2020 2:10 AM | 1 vote}

Hi Jan,

The query you provided can execute successfully in my environment. My environment is Configuration Manager 1906.

For the error message you get 'invalid view', it seems you don’t have the view v_GS_CCM_RECENTLY_USED_APPS or v_R_System in the environment. Please let us know which version is your configuration manager and find the correct view exists in your CM version to execute the command.

Hope it can help.

Best regards.
Crystal

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected].

_{Thursday, February 27, 2020 8:40 AM}

Awesome! That works indeed.
Thanks a lot!
Now the only concern that remains if I can use this to remove "non used" Project installs (App-V) in SCCM based upon this (since it is hardware inventory).

The idea is to remove Project installs when it is not used for more then 3 months. But if a user recently used it and hardware inventory did not run ... and we remove it because it shows as not used.

Jan Hoedt

_{Friday, February 28, 2020 3:33 AM}

Hi Jan,

For the hardware inventory run time interval, it can be an issue. To reduce the situation you mentioned to happen, you can consider to reduce the hardware inventory run time interval. But this can increase extra workload on the server and network. Please according to your environment to change the value.

Also, there’s some situation that the hardware inventory is not run on the client for a long time which will cause the deletion either. Maybe we can also query the hardware inventory date of client machines to avoid such situation. A link for the reference:

https://social.technet.microsoft.com/Forums/Windows/en-US/3ff57230-f85a-468b-80e1-1c9b59a79706/how-do-i-check-last-hardware-inventory-date-of-client-machines?forum=configmgrgeneral

Hope it can help.

Best regards.
Crystal

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected].

_{Thursday, March 5, 2020 8:49 AM | 1 vote}

Hi,

How's everything going? I am writing to see if there's anything else we can help. if yes, feel free to let us know.

Best regards.
Crystal

Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact [email protected].

_{Friday, March 6, 2020 9:08 AM}

Thanks for following up! It works, but we notice users who recenlty used an application are impacted so metering rule would be better.

Found a blog in which following sql command for metering, but that gives error.

Blog: https://sccmtips.co.uk/sql-query-for-summarizing-software-metering-usage

SELECT MRT.ProductName, VRS.Name0, VRS.operatingSystem0, MUR.FullName, MFS.MeteredFileName, sum(MUS.UsageCount) AS UsageCount, round(sum(MUS.UsageTime)/60,0)+1 AS UsageTime, max(MUS.LastUsage) AS LastUsed, min(MUS.TimeKey) AS FirstScanned

FROM V_MonthlyUsageSummary MUS
JOIN v_R_System VRS ON MUS.ResourceID = VRS.ResourceID
JOIN v_MeteredUser MUR ON MUS.MeteredUserID = MUR.MeteredUserID
JOIN v_MeteredFiles MFS ON MUS.FileID = MFS.MeteredFileID
JOIN MeterRules MRT ON MRT.RuleID = MFS.RuleID
WHERE MRT.ProductName = 'MyMeteringRule' And (VRS.operatingSystem0 like 'Windows 7%' OR VRS.operatingSystem0 like 'Windows 8%' OR VRS.operatingSystem0 like 'Windows 10%')
--and name0 = 'computer name
GROUP BY MRT.ProductName, VRS.Name0, VRS.operatingSystem0, MUR.FullName, MFS.MeteredFileName
--ORDER BY UsageCount ASC
ORDER BY Name0

ERROR:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'operatingSystem0'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'operatingSystem0'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'operatingSystem0'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'operatingSystem0'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'operatingSystem

Jan Hoedt

_{Friday, March 6, 2020 10:37 AM}

Msg 207, Level 16, State 1, Line 8
Invalid column name 'operatingSystem0'.
Msg 207, Level 16, State 1, Line 8

Hi,

operatingSystem0 is not built-in attribute that is enabled by default in the AD system discovery hence you see that error. You need to add it as custom attribute by editing AD system discovery properties.

Eswar Koneti | Configmgr Blog: http://www.eskonr.com | Linkedin: eskonr | Twitter: @eskonr

_{Friday, March 6, 2020 12:18 PM}

Thanks, where do I do that?

Jan Hoedt

_{Friday, March 6, 2020 12:20 PM}

Ok, found that https://www.systemcenterdudes.com/configure-sccm-discovery-methods/

But actually don't need it, how can I build the query without it?

Jan Hoedt

_{Friday, March 6, 2020 1:43 PM}

Thanks, where do I do that?

Jan Hoedt

remove this part of the query.

And (VRS.operatingSystem0 like 'Windows 7%' OR VRS.operatingSystem0 like 'Windows 8%' OR VRS.operatingSystem0 like 'Windows 10%')

Garth Jones

Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

Share via

Powershell/query: get last used time of an application?

Question

All replies (16)

Additional resources