In the results? From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, May 29, 2013 7:48 AM To: [email protected] Subject: Re: RE: [mssms] Help with SQL Query Servers not in X, Y, Z collections
Thank you for your response. Is there a way to include the collection names? Thanks On 05/28/13, [email protected]<mailto:[email protected]> wrote: 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]> [mailto:[email protected]] On Behalf Of [email protected]<mailto:[email protected]> Sent: Tuesday, May 28, 2013 2:56 PM To: [email protected]<mailto:[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
