Sccm 2007 collection query for particular service not installed in services.msc

Question

Sccm collection query to get the list of machines which do not have "sms host agent" service installed.

There are lot of queries in google related to this, however, they say the state of a service. But i need help to check if the service is installed in first place?

Adding: If sccm 2007 client is not installed, will the query be of any good?

Answer 1

You'd do something like:

select *  from  SMS_R_System where SMS_R_System.ResourceId NOT IN ( SELECT  SMS_G_System_SERVICE.ResourceId FROM SMS_G_System_SERVICE WHERE SMS_G_System_SERVICE.DisplayName = "SMS Agent Host" )

However, as you rightly point out, no SCCM client = no hardware inventory = no service information.

What you're better off doing is using information on discovered devices in your site with no client:

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 where SMS_R_System.SMSAssignedSites = "XXX" and SMS_R_System.Client = 0

Replacing "XXX" with your site code.

Hope this helps.