StevensNet.com

...Because someone's gotta tell the story...

Blogs

To return to the main Blog List, click Full Blog Listing.

Tags

SCCM
SQL
System Center

Finding computers not in collections via SQL

Wednesday, May 8, 2013 in System Center (Views: 2737)
A quickie, again. I think most of my technical blogs are probably going to take this model, but...

If you are looking for a quick way to find computers not in a specific collection using SQL, run this query against your database:

Note: This collection is excluding a few things, just so you can see how it works. The first, is excluding any computer not called "Client1" or "Client2". The other part is that the system object can not be a member of any collection known as XYZ00001, XYZ00002, XYZ00003, or XYZ00004 (XYZ of course, being your site code).

SELECT v.ResourceID,
v.ResourceType,
v.Name0,v.SMS_Unique_Identifier0,
v.Resource_Domain_OR_Workgr0,v.Client0
from v_R_System v
where v.Name0 not in ('CLIENT1','CLIENT2')
and
(v.ResourceID not in (select ResourceID from v_CM_RES_COLL_XYZ00001)
AND
(v.ResourceID not in (select ResourceID from v_CM_RES_COLL_XYZ00002))
AND
(v.ResourceID not in (select ResourceID from v_CM_RES_COLL_XYZ00003))
AND
(v.ResourceID not in (select ResourceID from v_CM_RES_COLL_XYZ00004))

)

The last ")" is there for readability - meaning that the 4 "AND" statements are there as one statement, not 4 separate ones.

 

Related Blogs You May Be Interested In:


To leave a comment, please log in and/or register.