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,
case when ucs.status=3 then 'installed'
when ucs.status=2 then 'required/missing'
when ucs.status=0 then 'unknown'
else 'something different'
end as 'result',
ui.title, ui.articleid,ui.DateRevised
from v_updateComplianceStatus ucs
join v_r_system s1 on s1.resourceid=ucs.ResourceID
join v_updateinfo ui on ui.ci_id=ucs.ci_id
join v_RA_System_IPAddresses n on n.ResourceID=ucs.ResourceID
join v_ClientCollectionMembers ccm on ccm.ResourceID=ucs.ResourceID
where
ucs.status !=3 and
n.IP_Addresses0 like '%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 ***/
order by 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]<mailto:[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
[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.
