If you just want the server names that do not exist in a list of collections, here you go:
Left Join vs. Inner Join doesn’t really matter on this query, but if SCCM inventory doesn’t report properly sometimes numbers can be skewed depending on the joins.
select
distinct sys.resourceid,
sys.name0,
OSYS.Caption0
from v_R_System SYS
Left JOIN v_GS_OPERATING_SYSTEM OSYS on SYS.ResourceID= OSYS.ResourceID
where OSYS.Caption0 like '%Server%'
and sys.ResourceID not in
(
select distinct ResourceID from v_FullCollectionMembership where CollectionID in ('XXX00050','XXX00055')
)
order by sys.name0
From: [email protected] [mailto:[email protected]] On Behalf Of [email protected]
Sent: Tuesday, May 28, 2013 2:56 PM
To: [email protected]
Subject: [mssms] Help with SQL Query Servers not in X, Y, Z collections
I am trying to find servers not in a specific set of collections. I have tried the query below but it gives me a list of every collections that servers are not members of. Can anyone assist me with a query that provides a list of all servers that do not belong to a list of collections.
Here's what I have tried.
select sys.resourceid,FCM.CollectionID, FCM.Name, OSYS.Caption0 AS [Operating System],cc.CollectionName
from v_R_System SYS
INNER JOINv_GS_OPERATING_SYSTEM OSYS on SYS.ResourceID= OSYS.ResourceID
INNER JOINv_FullCollectionMembership FCM ON SYS.ResourceID = FCM.ResourceID
INNER JOIN Collectionscc ON fcm.CollectionID = cc.SiteID
where OSYS.Caption0 like'%Server%' and sys.ResourceID not in
(select ResourceID fromv_FullCollectionMembership where CollectionID in ('FYA0038C', 'FYA00061', 'FYA0038D',
'FYA00062', 'FYA002E1','FYA002E3', 'FYA002EA', 'FYA006B8', 'FYA006B9', 'FYA006BA', 'FYA006BB', 'FYA006AE', 'FYA006AF','FYA006B0', 'FYA006B1', 'FYA0086F', 'FYA00870'))
order by CollectionID
