SCCM Collection Query

This is the query I use to collect all of our HIPS computers.

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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.PathName like "%firesvc.exe%" and SMS_G_System_SERVICE.StartMode = "Auto" and SMS_G_System_SERVICE.Status = "OK"

You can see this information on individual computers by starting the Resource Explorer then expanding Hardware and clicking on the Services option.

Also to answer your NOT running part. Just change the Status to not equal OK

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_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.PathName like "%firesvc.exe%" and SMS_G_System_SERVICE.StartMode = "Auto" and SMS_G_System_SERVICE.Status != "OK"

I like that query but I am wanting to know which computers that it is NOT running on.

Edit to the above Query 2. When telling it to return StartMode and Status it gives false positive results so leave that out and just return the computer name.

select distinct SMS_R_System.Name 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_SERVICE on SMS_G_System_SERVICE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SERVICE.PathName like "%firesvc.exe%")

Ok, I think I did this right. I also followed the instructions from the link above.

I need to spot check but so far it looks good.

If I use your " Edit to the above Query 2 ", I get false positives.

If I follow the instructions from the link it looks like it is showing correctly.

I chose a computer from the Query 2 and it showed having HIPS installed and the query I created from the link instructions doesnt show that same computer in its list.

Here is the query from the instructions link.

select SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_SYSTEM.Name not in (select distinct SMS_R_System.Name from  SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SERVICE.PathName like "%firesvc.exe%")

The reason that I had to modify my second query was that I was trying to show the StartMode and Status of the service. The only issue with doing this is if HIPS isn’t installed the service doesn’t exist. By removing the two extra display fields the query only reports computer names that are not in the results of Query 1. Actually if you follow the instructions linked and replace msaccess.exe with Firesvc.exe you will get the results that you are looking for.

Sorry for all the confusion.  

Ok, Thanks.

quick question. If I have a computer that hasnt done a hardware & software scan in a couple of days how do I get that computer to do one instantly.

If you download the SCCM Client Center (awesome tool by the way) you can initiate a hardware & software inventory at will remotely on a single computer by connecting to it then drop down "Client Actions" and select what discovery cycle you want to run.

http://sourceforge.net/projects/smsclictr/

The other way is on the client itself go to the Control Panel - Configuration Manager then on the actions tab initiate a hardware inventory cycle and likewise the software inventory cycle.

Cool thanks.