also ucs.status=3 for 'not required' along with 'installed' status. Regards, Eswar Koneti www.eskonr.com sent from mobile device,please excuse any typo's as a result.
On Jan 8, 2016, 4:48 AM, at 4:48 AM, David McSpadden <[email protected]> wrote: > >-- Given to me by Sherry Kissinger from MyItForums >-- Shows all missing updates by NetBios Name of Device >-- 01/07/2016 > >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 'Results', > ui.Title, > ui.ArticleID, > fcm.CollectionID > > FROM [CM_SC1].dbo.v_Update_ComplianceStatus ucs > join [CM_SC1].[dbo].[v_R_System] s1 on s1.ResourceID=ucs.ResourceID > join [CM_SC1].dbo.v_UpdateInfo ui on ui.CI_ID=ucs.CI_ID >join [CM_SC1].dbo.v_FullCollectionMembership fcm on >fcm.ResourceID=ucs.ResourceID > > where ucs.status<>3 >--and ui.ArticleID='823718' -- add specific Article for Audit reporting >--and fcm.CollectionID='SC100065' --add specifiv Collection id for >Audit reporting > > order by s1.Netbios_Name0,ui.ArticleID > > >From: [email protected] >[mailto:[email protected]] On Behalf Of David McSpadden >Sent: Thursday, January 7, 2016 3:38 PM >To: [email protected] >Subject: RE: [mssms] Find computers with specific windows updates >installed. > >Corrected the CI_ID join and it is perfect for me. >Nice and shiney! > > >From: [email protected] >[mailto:[email protected]] On Behalf Of Sherry Kissinger >Sent: Thursday, January 7, 2016 3:26 PM >To: [email protected] >Subject: Re: [mssms] Find computers with specific windows updates >installed. > >Because it's really different CI_IDs. > >For example, any particular update by ArticleID (which is in the Where >statement) might have 18+ actual "updates" behind the scenes--usually >for each possible language that might just maybe need to have a unique >update for that language. whether or not you actually HAVE those >languages "enabled" in your Software Update config isn't actually >relevant at this point. > >there are tricks and things; but likely the easiest is to rip out the >.ci_id from the select, and add in a "distinct" after the Select > >Select Distinct s1.netbios_name0, ucs.status, .... > >Remember this was originally all for GETTING the CI_ID you really cared >about for making (I thought) a collection query. This is not the droid >I thought people were looking for. so it's a cr ap query for what I >think you are looking for NOW. > > >On Thursday, January 7, 2016 2:18 PM, David McSpadden ><[email protected]<mailto:[email protected]>> wrote: > >Wonder why I am getting multiple hits with the same netbios name: >(Being dumb but it’s because of each unique CI_ID right?) >[cid:[email protected]] > >From: >[email protected]<mailto:[email protected]> >[mailto:[email protected]] On Behalf Of Sherry Kissinger >Sent: Thursday, January 7, 2016 3:08 PM >To: [email protected]<mailto:[email protected]> >Subject: Re: [mssms] Find computers with specific windows updates >installed. > > >I forgot to take out the >,count(*) > >you don't want to group anymore. Just rip that out, and it'll be fine. > > >On Thursday, January 7, 2016 1:40 PM, David McSpadden ><[email protected]<mailto:[email protected]>> wrote: > >Msg 8120, Level 16, State 1, Line 2 >Column 'CM_SC1.dbo.v_R_System.Netbios_Name0' is invalid in the select >list because it is not contained in either an aggregate function or the >GROUP BY clause. >I am doing it wrong? > > > >From: >[email protected]<mailto:[email protected]> >[mailto:[email protected]] On Behalf Of Sherry Kissinger >Sent: Wednesday, January 6, 2016 3:40 PM >To: [email protected]<mailto:[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. > > > > > > >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. > >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.
