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 <[email protected]>
wrote:
<!--#yiv4319238551 _filtered #yiv4319238551 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4319238551
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv4319238551
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;} _filtered #yiv4319238551
{font-family:Montserrat;}#yiv4319238551 #yiv4319238551
p.yiv4319238551MsoNormal, #yiv4319238551 li.yiv4319238551MsoNormal,
#yiv4319238551 div.yiv4319238551MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv4319238551 a:link, #yiv4319238551
span.yiv4319238551MsoHyperlink
{color:#0563C1;text-decoration:underline;}#yiv4319238551 a:visited,
#yiv4319238551 span.yiv4319238551MsoHyperlinkFollowed
{color:#954F72;text-decoration:underline;}#yiv4319238551 p
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv4319238551 span.yiv4319238551EmailStyle17
{font-family:"Calibri", sans-serif;color:windowtext;}#yiv4319238551
span.yiv4319238551EmailStyle19 {font-family:"Calibri",
sans-serif;color:#1F497D;}#yiv4319238551 .yiv4319238551MsoChpDefault
{font-size:10.0pt;} _filtered #yiv4319238551 {margin:1.0in 1.0in 1.0in
1.0in;}#yiv4319238551 div.yiv4319238551WordSection1 {}-->I think this is
getting me my results: /****** IP's not reporting to SCCM Updates ******/
SELECTDISTINCT ccm.[Name],ip.IP_Addresses0 FROM
[CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join
CM_SC1.dbo.v_RA_System_IPAddresses ipon ip.ResourceID=ccm.ResourceID where
ip.IP_Addresses0not in( SELECTDISTINCT ip.IP_Addresses0 FROM
[CM_SC1].[dbo].[v_ClientCollectionMembers] ccm join
CM_SC1.dbo.v_RA_System_IPAddresses ipon ip.ResourceID=ccm.ResourceID join
cm_sc1.dbo.v_Update_ComplianceStatus ucon uc.ResourceID=ccm.ResourceID join
CM_SC1.dbo.v_UpdateInfo uion ui.CI_id=uc.ci_id where
ccm.CollectionID='sms00001' and IP.IP_Addresses0like '10.0.%' ) and
IP.IP_Addresses0like '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: SELECTDISTINCT
ccm.[Name],ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers]
ccm join CM_SC1.dbo.v_RA_System_IPAddresses ipon ip.ResourceID=ccm.ResourceID
join cm_sc1.dbo.v_Update_ComplianceStatus ucon uc.ResourceID=ccm.ResourceID
join CM_SC1.dbo.v_UpdateInfo uion ui.CI_id=uc.ci_id where
ccm.CollectionID='sms00001' and IP.IP_Addresses0like '10.0.%' order by
ip.IP_Addresses0 And this one gives me all 595 rows: SELECTDISTINCT
ccm.[Name],ip.IP_Addresses0 FROM [CM_SC1].[dbo].[v_ClientCollectionMembers]
ccm join CM_SC1.dbo.v_RA_System_IPAddresses ipon ip.ResourceID=ccm.ResourceID
where ccm.CollectionID='sms00001' and IP.IP_Addresses0like '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 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.
