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=sys1.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
On Monday, February 8, 2016 10:30 AM, David McSpadden <[email protected]>
wrote:
<!--#yiv9526967973 _filtered #yiv9526967973 {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;} _filtered #yiv9526967973 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv9526967973
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv9526967973
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;} _filtered #yiv9526967973
{font-family:Montserrat;}#yiv9526967973 #yiv9526967973
p.yiv9526967973MsoNormal, #yiv9526967973 li.yiv9526967973MsoNormal,
#yiv9526967973 div.yiv9526967973MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv9526967973 a:link, #yiv9526967973
span.yiv9526967973MsoHyperlink
{color:blue;text-decoration:underline;}#yiv9526967973 a:visited, #yiv9526967973
span.yiv9526967973MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv9526967973 p
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv9526967973 p.yiv9526967973msonormal, #yiv9526967973
li.yiv9526967973msonormal, #yiv9526967973 div.yiv9526967973msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv9526967973 p.yiv9526967973msochpdefault, #yiv9526967973
li.yiv9526967973msochpdefault, #yiv9526967973 div.yiv9526967973msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv9526967973 span.yiv9526967973msohyperlink {}#yiv9526967973
span.yiv9526967973msohyperlinkfollowed {}#yiv9526967973
span.yiv9526967973emailstyle17 {}#yiv9526967973 span.yiv9526967973emailstyle19
{}#yiv9526967973 p.yiv9526967973msonormal1, #yiv9526967973
li.yiv9526967973msonormal1, #yiv9526967973 div.yiv9526967973msonormal1
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv9526967973 span.yiv9526967973msohyperlink1
{color:#0563C1;text-decoration:underline;}#yiv9526967973
span.yiv9526967973msohyperlinkfollowed1
{color:#954F72;text-decoration:underline;}#yiv9526967973
span.yiv9526967973emailstyle171 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv9526967973 span.yiv9526967973emailstyle191
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv9526967973
p.yiv9526967973msochpdefault1, #yiv9526967973 li.yiv9526967973msochpdefault1,
#yiv9526967973 div.yiv9526967973msochpdefault1
{margin-right:0in;margin-left:0in;font-size:10.0pt;font-family:"Times New
Roman", serif;}#yiv9526967973 span.yiv9526967973EmailStyle30
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv9526967973
.yiv9526967973MsoChpDefault {font-size:10.0pt;} _filtered #yiv9526967973
{margin:1.0in 1.0in 1.0in 1.0in;}#yiv9526967973 div.yiv9526967973WordSection1
{}-->I like this but the ccm.resourceid is boggerred. From:
[email protected] [mailto:[email protected]]On Behalf
Of Sherry Kissinger
Sent: Monday, February 8, 2016 11:18 AM
To: [email protected]; Patch Management Mailing List
([email protected])
<[email protected]>
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 <[email protected]> wrote: 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 ip on ip.ResourceID=ccm.ResourceID where
ip.IP_Addresses0notin( SELECTDISTINCT 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_Addresses0like'10.0.%' ) and
IP.IP_Addresses0like'10.0.%' orderby 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 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_Addresses0like'10.0.%' orderby
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 ip on ip.ResourceID=ccm.ResourceID
where ccm.CollectionID='sms00001' and IP.IP_Addresses0like'10.0.%'
orderby 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. 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.
