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
