Find computers without a certain file with a subselect query.

In this case I want to query all system that doesn’t have the vpc32.exe file to identify computer that doesn’t have a Symantec Antivirus installed. The following query is a subselect query. You can easily replace the “exe” file name with the one you need.

select distinct SMS_R_System.Name, SMS_R_System.ADSiteName, SMS_R_System.IPAddresses from  SMS_R_System where SMS_R_System.Name not in (select distinct SMS_R_System.Name 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 = “vpc32.exe”)