Today I created this custom query to help look for devices which do not have a specified software installed. I create the query to prompt for the collection and then then enter a value for Software.ARPDisplay Name. Here is the query:
select distinct SMS_R_System.Name, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_CH_ClientSummary.ClientActiveStatus, SMS_R_System.IPAddresses, SMS_R_System.LastLogonUserName from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId
where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_CH_ClientSummary on SMS_G_System_CH_ClientSummary.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName like ##PRM:SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName##)
order by SMS_R_System.Name
Let me know if you find this useful.
Comments