SCCM Collection Query

CMILLER
CMILLER used Ask the Experts™
on
I need to build a collection based on software or a service NOT being installed or running.

The software is McAfee HIPS and the service is firesvc.exe.

How do I create this collection?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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.

Commented:
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"

Author

Commented:
I like that query but I am wanting to know which computers that it is NOT running on.
Commented:
After thinking about a "subselect (NOT) query" is perfect for this. It will require two "queries" not a collection but one can be created after the queries have been created.

Here is a link that explains a subselect (NOT) query.

http://www.myitforum.com/articles/1/view.asp?id=179 

Ok below is your Query 1 say All McAfee HIPS Clients

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%"

Now for Query 2 all the computers that are not returned with Query 1. All NON McAfee HIPS Clients

select distinct SMS_R_System.Name, SMS_G_System_SERVICE.StartMode, SMS_G_System_SERVICE.Status from  SMS_R_System inner join SMS_G_System_SERVICE on SMS_G_System_SERVICE.ResourceID = SMS_R_System.ResourceId 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%")

Commented:
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%")

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:

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%")

Commented:
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.  

Author

Commented:
Ok, Thanks.

Author

Commented:
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.

Commented:
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.

Author

Commented:
Cool thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial