Well, to break it down for that specific query, it's joining up a bunch of
tables:
v_updateComplianceStatus ucsjoin v_r_system s1 on
s1.resourceid=ucs.ResourceIDjoin v_updateinfo ui on ui.ci_id=ucs.ci_idjoin
v_RA_System_IPAddresses n on n.ResourceID=ucs.ResourceIDjoin
v_ClientCollectionMembers ccm on ccm.ResourceID=ucs.ResourceID .
The ones we care about, in this case, are the ones that are doing joins on
"resourceid".If the resourceid for that specific box isn't in any one of these,
then it's not going to be part of the result set:v_updateComplianceStatus
(and for that resourceid there's no status !=
3)v_r_systemv_ra_system_ipaddresses (and for that resourceid, it's got an
ip_addresses0 like %10.0.%')v_clientcollectionmembers (and for that resourceid
it's in the SC100042 collectionid)
If I were going to t-shoot the "why"; i'd start by getting the resourceid for
that specific box from v_r_system. Now that I know it; then check each view
separately; like...
Select * from v_updatecompliancestatus where resourceid = 'that exact
resourceid' and status != 3' (and if zero results, then remove the status !=;
and see if you get results then)
select * from v_ra_system_ipaddresses where resourceid = 'that exact
resourceid' and ip_addresses0 like '%10.0.%' (and if zero results, then remove
the ipaddresses0 like... and see if you get results then).
Basically--deconstruct the joins and figure out where the logic fell down for
that specific box. Maybe it's not really an ipaddress like %10.0.%'. Maybe
it's never reported software updates--via CM. etc. etc. Once you know where
it's NOT then you can work it out as to the "why not". Whether or not there is
anything to fix at the client; maybe. Maybe not.
On Friday, February 5, 2016 3:05 PM, David McSpadden <[email protected]>
wrote:
<!--#yiv1213865485 _filtered #yiv1213865485 {font-family:Helvetica;panose-1:2
11 6 4 2 2 2 2 2 4;} _filtered #yiv1213865485 {font-family:"Cambria
Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv1213865485
{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv1213865485
{font-family:Consolas;panose-1:2 11 6 9 2 2 4 3 2 4;} _filtered #yiv1213865485
{font-family:Montserrat;}#yiv1213865485 #yiv1213865485
p.yiv1213865485MsoNormal, #yiv1213865485 li.yiv1213865485MsoNormal,
#yiv1213865485 div.yiv1213865485MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv1213865485 a:link, #yiv1213865485
span.yiv1213865485MsoHyperlink
{color:blue;text-decoration:underline;}#yiv1213865485 a:visited, #yiv1213865485
span.yiv1213865485MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv1213865485
p.yiv1213865485msonormal, #yiv1213865485 li.yiv1213865485msonormal,
#yiv1213865485 div.yiv1213865485msonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv1213865485 p.yiv1213865485msochpdefault, #yiv1213865485
li.yiv1213865485msochpdefault, #yiv1213865485 div.yiv1213865485msochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New
Roman", serif;}#yiv1213865485 span.yiv1213865485msohyperlink {}#yiv1213865485
span.yiv1213865485msohyperlinkfollowed {}#yiv1213865485
span.yiv1213865485emailstyle17 {}#yiv1213865485 p.yiv1213865485msonormal1,
#yiv1213865485 li.yiv1213865485msonormal1, #yiv1213865485
div.yiv1213865485msonormal1
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri",
sans-serif;}#yiv1213865485 span.yiv1213865485msohyperlink1
{color:#0563C1;text-decoration:underline;}#yiv1213865485
span.yiv1213865485msohyperlinkfollowed1
{color:#954F72;text-decoration:underline;}#yiv1213865485
span.yiv1213865485emailstyle171 {font-family:"Calibri",
sans-serif;color:windowtext;}#yiv1213865485 p.yiv1213865485msochpdefault1,
#yiv1213865485 li.yiv1213865485msochpdefault1, #yiv1213865485
div.yiv1213865485msochpdefault1
{margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Calibri",
sans-serif;}#yiv1213865485 span.yiv1213865485EmailStyle27
{font-family:"Calibri", sans-serif;color:#1F497D;}#yiv1213865485
.yiv1213865485MsoChpDefault {font-size:10.0pt;} _filtered #yiv1213865485
{margin:1.0in 1.0in 1.0in 1.0in;}#yiv1213865485 div.yiv1213865485WordSection1
{}-->Using this I find all patches for a group of pc’s in a collection (Thank
you very much for this by the way.) select s1.netbios_name0, s1.User_Name0,
n.IP_Addresses0, ccm.CollectionID, ucs.ci_id, ucs.status, casewhen
ucs.status=3then 'installed' when ucs.status=2then 'required/missing' when
ucs.status=0then 'unknown' else'something different' endas 'result', ui.title,
ui.articleid,ui.DateRevised from v_updateComplianceStatus ucs join v_r_system
s1on s1.resourceid=ucs.ResourceID join v_updateinfo uion ui.ci_id=ucs.ci_id
join v_RA_System_IPAddresses non n.ResourceID=ucs.ResourceID join
v_ClientCollectionMembers ccmon ccm.ResourceID=ucs.ResourceID where
ucs.status!=3 and n.IP_Addresses0like '%10.0.%' and
ccm.CollectionID='SC100042' /*** SC10009F Patch 1 SC1000A1
Patch 2 SC10009E Patch 3 SC1000A7 Live A SC1000A8 Live B
SC1000A6 Branch Servers SC100042 Branch 3 Brownseburg ***/ /***
Replace Collectionid with the collection you are looking at Select * from
Collections to find a collectionid.***/ and (ui.title not like '%Adobe%' and
ui.title not like '%Definition%' and ui.title not like '%Internet Explorer 11%'
and ui.title not like '%4.6.1%' and ui.title not like '%Reader%' and ui.title
not like '%Acrobat%') /***Not looking at anything with Adobe, Internet
Explorer 4.6.1, .NET 4.6.1 or Definition in the titles right now. ***/ and
ui.ArticleID != '' /*** Not looking for titles of SUGs ***/ and ui.DateRevised
< '2016-01-13 00:00.000' /*** Not looking at anything past January 12 2016 ***/
orderby n.IP_Addresses0 However, early today we found a machine in this
collection that was missing 121 updates (using Belarc to validate) but not
showing up here. So I went to the collection table for SC100042 and the client
shows here? The CL_ID is in the collection table. No real reason to speak
about it other than I find the CL_ID in a bunch of other tables. What I really
need is to understand why it is not populating the query above. From:
[email protected] [mailto:[email protected]]On Behalf
Of Sherry Kissinger
Sent: Friday, February 5, 2016 3:26 PM
To: [email protected]; Patch Management Mailing List
([email protected])
<[email protected]>
Subject: Re: [mssms] PC shows in Collection but not in UpdateCompliaanceStatus
Did it scan? (check logs at the client). You can also look at the box using
Roger Zander's Client Center, and it'll show you the results of a CM software
update scan, the local results. That would help you know if the client scanned
at all, and when. "CL_ID " ? do you mean the resourceid of the target, or
you really mean the ci_id of the update? (trying to figure out why/what you are
looking for there) On Friday, February 5, 2016 1:57 PM, David McSpadden
<[email protected]> wrote: I can see the my target PC in the table
collectionxx but I am looked to find it in v_Update_ComplianceStatus and the
CL_ID is not showing up? Why would a workstation not show in the
v_Update_ComplianceStatus or the v_UpdateComplianceStatus views??? Trying to
determine if all my clients are reporting updates stats back to the SCCM 2012
R2 database??? 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.
