Search code examples
sqlsccmwql

Query to Find path file to executable in specific PC's

Using SCCM 2012- trying to write a query We have a program that has been installed in multiple locations. Sometimes it has been installed in C:\Program Files and other times under C:\Program Files (x86). I want to find for a specific set of computers, where exactly the executable is.

I am able to write a query that pulls the correct set of PC's that I need.

select 
     SMS_R_SYSTEM.ResourceID
    ,SMS_R_SYSTEM.LastLogonUserName
    ,SMS_R_SYSTEM.ResourceType
    ,SMS_R_SYSTEM.Name
    ,SMS_R_SYSTEM.SMSUniqueIdentifier
    ,SMS_R_SYSTEM.ResourceDomainORWorkgroup
    ,SMS_R_SYSTEM.Client 
from SMS_R_System 
where SMS_R_System.LastLogonUserName LIKE "USER%"

When I try to add to this query to find the file path to a program called "appX" I wrote this, but it's not returning any values:

select 
     DISTINCT SMS_R_SYSTEM.LastLogonUserName
     ,SMS_R_SYSTEM.Name
     ,SMS_R_SYSTEM.Client
     ,SMS_G_SYSTEM_SoftwareFile.FileDescription
     ,SMS_G_SYSTEM_SoftwareFile.FileName
     ,SMS_G_SYSTEM_SoftwareFile.FilePath
from SMS_R_System 
    inner join SMS_G_SYSTEM_SoftwareFile 
    on SMS_G_SYSTEM_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where 
    SMS_R_System.LastLogonUserName LIKE "USER%" 
    and SMS_G_SYSTEM_SoftwareFile.FileDescription like "appX"

Thank you.

Solution

  • The issue with this was that you have to ask the sccm client to collect data on either folder paths or specific executables that you want.
    You can change what the client gathers by changing the settings in the administration window. Once I added the name of the executable to the data the client collects, my query above worked. Thank you.