SCCM Collection Query for software

Question

First off I am by no means an expert when it comes to trying to conduct a query. This is defintely a learning experience here. With that being said, I tried to work on getting a device collection trying to find how to list 150 workstations that has Visio 2019 installed on it. Below is the query that

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName like "Visio.exe" and SMS_G_System_SoftwareFile.FileVersion like "16.%"

Now I got no problem with this part, it lists the 150 workstations without a problem. The issue that I am trying to address is trying to get narrow down to what machines have accessed it within the last 90 days. I came across this from a post in 2017.

FCM.CollectionID = 'CollectionID' and exe.ExecutableName0 LIKE '%ApplicationName%' and Name0 like '%MachineName%'

Any inputs on how I should apply this would be greatly appreciated!

Answer 1

Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId inner join SMS_FullCollectionMembership FCM on FCM.ResourceID = SMS_R_System.ResourceId where FCM.CollectionID = 'Visio 2019 Installed' and SMS_G_System_SoftwareFile.FileName like "Visio.exe"

This is what I end up using. I ran the query and nothing came up at all.

Answer 2

Hi, @robert jenkins

Thank you for posting in Microsoft Q&A forum.

To narrow down a device collection, we can use the class SMS_FullCollectionMembership, and you want to get the machines have accessed visio within the last 90 days, you may need to create software metering for visio, otherwise, we cannot get the time that visio used in a machine.

The query to narrow down a device collection:

Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client

from SMS_R_System 

inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId 

inner join SMS_FullCollectionMembership FCM on FCM.ResourceID = SMS_R_System.ResourceId 

where FCM.CollectionID = 'CollectionID' and SMS_G_System_SoftwareFile.FileName like "Visio.exe" 

---

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".