Thanks for the responses! I forgot to mention, and have shot myself in the foot because of this, but….
I’ve explicitly declined these updates is WSUS (to keep them from installing), so they don’t show in configuration manager which leaves out the compliance reporting built in. (looking for manual installs) Sherry, Both are super helpful, thanks for revealing your MMS presentation early. You are awesome! Iwill give these a shot and report back. Thanks! From: [email protected] [mailto:[email protected]] On Behalf Of Sherry Kissinger Sent: Wednesday, January 06, 2016 1:40 PM To: [email protected] Subject: Re: [mssms] Find computers with specific windows updates installed. oh, you know, I assumed you wanted it as a collection query. if all you want is a sql report, then... select s1.netbios_name0, ucs.ci_id, ucs.status, case when ucs.status=3 then 'installed' when ucs.status=2 then 'required/missing' else 'something different' end as 'result', ui.title, ui.articleid ,count(*) 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_FullCollectionMembership fcm on fcm.resourceid=ucs.resourceid where ui.ArticleID='3068708' and fcm.collectionid='ThatcollectionIDYouWantedToLookAt' and ucs.status<>2 --well, you could leave this out I guess order by ucs.status On Wednesday, January 6, 2016 2:35 PM, Sherry Kissinger <[email protected]<mailto:[email protected]>> wrote: Ok... I *WAS* going to save this for MMS May 2016; so if any of you attend, pretend to be surprised if you see this there. Several Steps needed, BUT... you already have the data in state messages from your clients. It's just not simple to get from point a to point c. I'm not a super-fan of enabling QFE if I don't have to. step 1) You need to figure out what ci_id YOUR environment has for that particular ArticleID. AND which one of those Ci_id's for that article actually reflect the ones that are installed. So first thing's first... make a collection (or have one already) where you know boxes in that collection have KB3068708. Take note of the collection ID; and you'll want to insert it in this SQL query, which you run against your CM_xxx database: select ucs.ci_id, ucs.status, case when ucs.status=3 then 'installed' when ucs.status=2 then 'required/missing' else 'something different' end as 'result', ui.title, ui.articleid ,count(*) from v_updateComplianceStatus ucs join v_updateinfo ui on ui.ci_id=ucs.ci_id join v_FullCollectionMembership fcm on fcm.resourceid=ucs.resourceid where ui.ArticleID='3068708' and fcm.collectionid='ThatcollectionIDYouWantedToLookAt' and ucs.status<>2 --well, you could leave this out I guess group by ucs.ci_id, ucs.status, ui.title, ui.articleid order by ucs.status Step 2: Confirm the Title, ArticleID and make sure you know EXACTLY the ci_id you want to make the collection query for. This is unique to your environment, for that update. Step 3: Back in your Console, make a collection query, and you want it to look sorta like this. i.e., if for you, your CI_ID that you care about is 113322, then it would be this: Select SMS_R_System.ResourceID from SMS_R_System Where resourceid in ( Select MachineID from SMS_UpdateComplianceStatus Where CI_ID=113322 and Status=3 ) Which would be machines with status of 3 (installed), for that very specific update. So now you have a collection of machines with that update installed. Tip: because this is based on "state message" type information DO NOT MAKE IT AN INCREMENTAL COLLECTION. You will likely make all collection processing backlog and just tip over and kill your site. Do NOT check that box about incremental. Only let the collection update daily or something even more infrequent. Also, make yourself a note to remind yourself to delete this collection in a month or something. Again, it's kind of "cool" that we "can" do this--but it's really not that awesome to have too many of them out there. Don't be crazy. On Wednesday, January 6, 2016 1:22 PM, the codepoets <[email protected]<mailto:[email protected]>> wrote: Does the report "Compliance 2 - Specific software update" not meet your needs? I use it to hunt down patches. -e On Wed, Jan 6, 2016 at 9:37 AM, Michael Timm <[email protected]<mailto:[email protected]>> wrote: All, I have been tasked with hunting down workstations that have certain unwanted windows KB’s installed. I have tried creating a collection based on a WQL query but it doesn’t return any results. If I recall windows updates aren’t listed in programs and features any longer. So that may be why there aren’t any results. select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3068708%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3022345%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3075249%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3080149%" I’ve also tried a configuration baseline based off of info listed in: https://www.windows-noob.com/forums/topic/8640-sccm-2012-search-for-installed-hotfixes/ I am applying that against a test collection that contains both machine that have them installed, and machines that don’t have them installed. The deployment results show 100% compliant, which aren’t the results I am expecting. Can anyone offer up any advice on what I’m doing wrong, or how I could go about finding them? Thanks! Mike Timm Systems Engineer Overstock.com ________________________________ CONFIDENTIALITY NOTICE: This message is intended only for the use and review of the individual or entity to which it is addressed and may contain information that is privileged and confidential. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message solely to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify sender immediately by telephone or return email. Thank you. ________________________________ CONFIDENTIALITY NOTICE: This message is intended only for the use and review of the individual or entity to which it is addressed and may contain information that is privileged and confidential. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message solely to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify sender immediately by telephone or return email. Thank you.
