I think this is getting me my results: /****** IP's not reporting to SCCM Updates ******/
SELECT DISTINCT ccm.[Name],ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=ccm.ResourceID where ip.IP_Addresses0 not in ( SELECT DISTINCT ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=ccm.ResourceID join cm_sc1.dbo.v_Update_ComplianceStatus uc on uc.ResourceID=ccm.ResourceID join CM_SC1.dbo.v_UpdateInfo ui on ui.CI_id=uc.ci_id where ccm.CollectionID='sms00001' and IP.IP_Addresses0 like '10.0.%' ) and IP.IP_Addresses0 like '10.0.%' order by ip.IP_Addresses0 From: [email protected] [mailto:[email protected]] On Behalf Of David McSpadden Sent: Monday, February 8, 2016 10:14 AM To: Patch Management Mailing List ([email protected]) <[email protected]>; [email protected] Subject: [mssms] SQL help Ok so this query gives me 310 rows: SELECT DISTINCT ccm.[Name],ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=ccm.ResourceID join cm_sc1.dbo.v_Update_ComplianceStatus uc on uc.ResourceID=ccm.ResourceID join CM_SC1.dbo.v_UpdateInfo ui on ui.CI_id=uc.ci_id where ccm.CollectionID='sms00001' and IP.IP_Addresses0 like '10.0.%' order by ip.IP_Addresses0 And this one gives me all 595 rows: SELECT DISTINCT ccm.[Name],ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=ccm.ResourceID where ccm.CollectionID='sms00001' and IP.IP_Addresses0 like '10.0.%' order by ip.IP_Addresses0 I would like to see the 285 rows though. How do I get the IP's that are missing in the top query? David McSpadden System Administrator Indiana Members Credit Union P: 317.554.8190 [Description: Description: imcu email icon]<http://imcu.com/> [Description: Description: facebook email icon] <https://www.facebook.com/IndianaMembersCU> [Description: Description: twitter email icon] <https://twitter.com/IndMembersCU> [Description: Description: email logo] [http://www.amuletsolutions.com/images/mcp.gif]<http://www.google.com/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwjFztf-tePJAhXK5iYKHcPtAxEQjRwIBw&url=http://www.amuletsolutions.com/awards.aspx&bvm=bv.110151844,d.amc&psig=AFQjCNHkrx8CednTEOOq4zUxYyrRUGzUsg&ust=1450459757284499> This e-mail and any files transmitted with it are property of Indiana Members Credit Union, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please consider the environment before printing this email. This e-mail and any files transmitted with it are property of Indiana Members Credit Union, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipient(s) or otherwise have reason to believe that you have received this message in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing, or copying of this email is strictly prohibited. Please consider the environment before printing this email.
