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
