I like this but the ccm.resourceid is boggerred.
From: listsadmin@lists.myitforum.com [mailto:listsadmin@lists.myitforum.com] On Behalf Of Sherry Kissinger Sent: Monday, February 8, 2016 11:18 AM To: ms...@lists.myitforum.com; Patch Management Mailing List (patchmanagem...@listserv.patchmanagement.org) <patchmanagem...@listserv.patchmanagement.org> Subject: Re: [mssms] RE: SQL help Personally, I think you are having SQL work much harder than it needs to. If I understand what question you are trying to ask of SQL, it's "Show me details about valid machines which have not submitted UpdateComplainceStatus, ever.. Their IP address isn't a limitation of "who hasn't", but I do want to know that factoid in the resulting report". Is that the question you want asked of SQL? SELECT DISTINCT sys1.[NetBIOS_name0] ,ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_r_system_valid] sys1 join CM_SC1.dbo.v_RA_System_IPAddresses ip on ip.ResourceID=ccm.ResourceID where ip.resourceid not in ( Select resourceid from cm_sc1.dbo.v_Update_ComplianceStatus uc) and IP.IP_Addresses0 like '10.0.%' order by ip.IP_Addresses0 You may or may not want to (in the subselect) continue to use collectionid and ip address there. I think it's superfluous, personally. On Monday, February 8, 2016 9:33 AM, David McSpadden <dav...@imcu.com<mailto:dav...@imcu.com>> wrote: 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: listsadmin@lists.myitforum.com<mailto:listsadmin@lists.myitforum.com> [mailto:listsadmin@lists.myitforum.com] On Behalf Of David McSpadden Sent: Monday, February 8, 2016 10:14 AM To: Patch Management Mailing List (patchmanagem...@listserv.patchmanagement.org<mailto:patchmanagem...@listserv.patchmanagement.org>) <patchmanagem...@listserv.patchmanagement.org<mailto:patchmanagem...@listserv.patchmanagement.org>>; ms...@lists.myitforum.com<mailto:ms...@lists.myitforum.com> 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. 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.
