none
Collection based on Windows Update (KB) installed status RRS feed

  • Question

  • I have a Windows Update that went out to my users and I now need to uninstall it on a select number of machines based on department. I am using the SCCM report Compliance 2 - Specific software update to identify the systems that received the patch.

    Is it possible to build a collection query that identifies computers with a particular KB installed? if not, does anyone have any recommendations on how to dynamically group computers with this patch installed?

    Tuesday, January 20, 2015 5:13 PM

Answers

All replies

  • Search the forums for Win32_QuickFixEngineering. It will tell you a way to extend hardware inventory in order to grab that information.

    Torsten Meringer | http://www.mssccmfaq.de

    Tuesday, January 20, 2015 5:23 PM
    Moderator
  • Here is your collection query. just paste it into the collection query designer and adjust for the KB article #

    select *  from  SMS_R_System inner join SMS_G_System_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceId = SMS_R_System.ResourceId where SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID = "KB2552343"

    Tuesday, January 20, 2015 7:41 PM
  • good discussion here (avoid inventorying QFE if possible)https://social.technet.microsoft.com/Forums/systemcenter/en-US/1c35cd02-d903-4e94-b1e6-1ea42f2f70ef/deploying-kb-2775511-win7-hotfix-rollup-as-a-regular-sccm-package?forum=configmgrswdist

    Don
    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer", where applicable.
    This helps the community, keeps the forums tidy, and recognises useful contributions. Thanks!)

    Tuesday, January 20, 2015 9:13 PM
  • Thank you, this worked perfectly for me
    Wednesday, March 29, 2017 6:34 PM
  • Or... if you want to get your full geek on:

    Step 1: first figure out what the ci_id is for THAT specific update in YOUR specific environment. 

    --#This is where you put in the specific articleID you want to get the unique ci_id for
    Declare @ArticleID varchar(10) = '3139923'
    --------------------------------------------
    select ucs.ci_id, ucs.status,
    case when ucs.status=3 then 'installed'
    when ucs.status=2 then 'required/missing'
    else 'other'
    end as 'result',
    ui.title, ui.articleid
    ,count(distinct fcm.resourceid) [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=@ArticleID
    --and fcm.collectionid='ThatSpecificcollectionIDYouWantedToLookAt'
    --and ucs.status=3 --well, you could leave this out; to get all status'
    group by ucs.ci_id, ucs.status, ui.title, ui.articleid
    order by ucs.status

    Got the exact, absolutely positively positive correct ci_id for YOUR environment?  Then...

    Step 2:

    Create a Collection, and you want to copy and paste this in to the collection query logic, manually.  Make sure you replace the ci_id below with the one you got from the above SQL.  the below example is for machines which are MISSING that ci_id.  If you want machines that say INSTALLED for that CI_ID, then Status=3, not Status=2

    Select SMS_R_System.ResourceID
      from SMS_R_System
    Where resourceid in (
      Select MachineID from SMS_UpdateComplianceStatus
      Where CI_ID=16783188 and Status=2
    )

    Personally, I would use this extremely sparingly.


    Standardize. Simplify. Automate.

    Wednesday, March 29, 2017 6:54 PM
  • I know I'm necroposting here but this was a one of the top search results.

    Since there will be multiple updates and corresponding CI_IDs for a given KB article you can take Sherry's solution one step further.  This will return clients that needs any update included in a particular KB article.  Query took just shy of 5 seconds in my ~17k client environment.

    Select SMS_R_System.ResourceID
      from SMS_R_System
    Where resourceid in (
      Select MachineID from SMS_UpdateComplianceStatus
      Where CI_ID In (Select CI_ID From SMS_SoftwareUpdate Where ArticleID='<INSERT YOUR KB HERE>') and Status=2
    )

    • Edited by Bryan Dam Monday, April 2, 2018 3:09 PM
    Monday, April 2, 2018 3:05 PM
  • or, (as I should have thought to mention originally..)

    create relevant CI's for the SU's (this is builtin functionality in Compliance Settings) and add to a baseline and deploy that. You can then right-click that baseline and create [compliant=TRUE] and [compliant=FALSE] collections in two clicks.

    This method, it's all wizard-driven and simple to do, no sticky SQL/WQL required :)

    And, you don't need to collect the QFE class either...

    (just in case you're not ready to embrace all that goo just yet ;)


    Don [doesn't work for MSFT, and they're probably glad about that ;]


    • Edited by DonPick Tuesday, April 3, 2018 9:18 PM
    Tuesday, April 3, 2018 9:17 PM
  • Is there a way to add several KB's as a requirement for a collection query?

    So for example, can the following work, to only select machines with the following 3 updates installed?

    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_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceId = SMS_R_System.ResourceId where SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID = "KB1234567","KB1234568","KB1234569"

    *Note* I know this is a dead post, but I can't find the answer anywhere else

    Wednesday, October 9, 2019 2:41 PM
  • Use In operator instead of  '='

    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_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceId = SMS_R_System.ResourceId where SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID in( 'KB1234567','KB1234568','KB1234569');

    Tuesday, November 19, 2019 10:56 AM