SCCM Collection Query for software

Robert Jenkins 0 Reputation points
2023-05-02T16:54:59.0333333+00:00

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!

Microsoft Configuration Manager Application
Microsoft Configuration Manager Application
Microsoft Configuration Manager: An integrated solution for for managing large groups of personal computers and servers.Application: A computer program designed to carry out a specific task other than one relating to the operation of the computer itself, typically to be used by end users.
459 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. AllenLiu-MSFT 40,881 Reputation points Microsoft Vendor
    2023-05-04T02:42:03.3733333+00:00

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

    0 comments
  2. Robert Jenkins 0 Reputation points
    2023-05-04T11:43:05.1466667+00:00

    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.