Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr SQL Query How to get list of patches required by specific Computer

    SCCM Configmgr SQL Query How to get list of patches required by specific Computer

    Eswar KonetiBy Eswar KonetiOctober 27, 10:11 pm2 Mins Read CM2012 19,684 Views
    Share
    Facebook Twitter LinkedIn Reddit

     

    There are instances (you might want to get list of required patches by server ,so can download and install in DMZ who do not have internet or for patch compliance checking for specific computer and many) , where you might be searching for list patches of required by specific computer/computers and generally ,we look at default reports if there is any report which can give me the required information.

    If you look at default reports,there is no exact report give you the list of patches required by specific computer with targeted,required,when was it released and other information.

    You can use below SQL Query to run  from SQL SERVER MANAGEMENT STUDIO or use this in your SSRS Reports with parameter for computer name.

    This query has been customized to present the Month posted,Title of the patch,Targeted to the client,Required ,KB URL,Date posted and deadline if there is any.

    Note: This Query is filtered with Severity (Security and critical)  from view v_UpdateInfo.

    SQL Code:

    select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
    ui.bulletinid [BulletinID],ui.articleid [ArticleID], ui.Title,
        Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
        IsRequired=(case when css.Status=2 then '*' else '' end),
        ui.InfoURL as InformationURL,
        ui.dateposted [Date Posted] ,
        Deadline=cdl.Deadline
        from V_UpdateComplianceStatus  css
        join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
        left join v_CITargetedMachines  ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
        INNER join v_CICategories_All catall2 on catall2.CI_ID=css.CI_ID
        INNER  join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID
        and catinfo2.CategoryTypeName='UpdateClassification'
        JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
           outer apply (
           select Deadline=min(a.EnforcementDeadline)
           from v_CIAssignment  a
           join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
           ) cdl
       WHERE vrs.Name0='Win7X64-001' and
       ui.Severity IN (8, 10) --this is for security and critical updates
      AND css.Status=2  --for required
    ORDER BY 1

    I posted the same query on TechNet forums ,more info https://social.technet.microsoft.com/Forums/en-US/8fe96532-d9ef-48f2-a356-6e1195d9273e/report-of-what-updates-are-needed-by-server?forum=configmanagergeneral#7185a84e-afab-47d2-82bd-7418e02dd75f

    Collection Compliance configmgr patch compliance report Patch Report patches required by client Reports SCCM Software update compliance Software update group SQL SSRS SUP report Update report
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Identifying devices managed by Intune but not reporting to WUfB using KQL

    November 09, 10:28 am

    14 Comments

    1. Pingback: SCCM Configmgr SSRS Report Get list of missing updates for PC from specific Software update group | All about Microsoft Endpoint Manager

    2. Nick on January 4, 2019 8:56 PM

      Hi Eswar, this looks very useful, is there a way of just producing the count of patches that are missing for devices in a Collection. We are just starting to use SCCM to do patching on our server estate and have a requirement to see how far out of date each server is so that we can estimate the time required to update them.

      Reply
      • Eswar Koneti on January 27, 2019 10:14 PM

        Hi Nick,
        This is possible and you can make use of these software update reports: http://eskonr.com/2016/02/sccm-configmgr-ssrs-report-how-do-i-quickly-tell-if-clients-are-compliant-or-not-for-all-the-approved-patches/
        http://eskonr.com/2015/12/sccm-configmgr-2012-updated-patch-compliance-reports-for-software-update-group-and-collection-with-patch-progression/

        Thanks,
        Eswar

        Reply
    3. Eray Kaya on July 12, 2018 8:30 PM

      here is an sql query that direcly queries the hotfixes and related hostnames from SCCM database
      https://bestitsm.wordpress.com/2018/07/12/how-to-get-list-of-installed-hotfixes-from-sccm-database/

      Reply
    4. David on June 27, 2016 9:10 AM

      Eswar
      I think i got it wrong. (Management 2 - Updates required but not deployed) report might be the one i needed after all.
      I think the NOT DEPLOYED means the updates that is not deployed to the collection that i am running the report against. Is that correct? Thanks, Dave.

      Reply
      • Eswar Koneti on June 28, 2016 10:34 PM

        I think that's true, it tells about whether the patch deployed to the collection or not.

        Reply
    5. David on June 27, 2016 8:45 AM

      Hi Eswar
      Another request for you.
      Is it possible to modify this and have a report that list all required updates (whether deployed or not, or downloaded or not), for a collection? Very similar to the built in report (Management 2 - Updates required but not deployed). But, not limiting it to "Not deployed".
      Thanks again, DM.

      Reply
    6. David on December 4, 2015 7:37 PM

      Mate, that will be great if you could create a computer report based on Software Update Group.
      Keep up the good work.

      Reply
      • Eswar Koneti on December 6, 2015 10:22 PM

        sure,this is in my To-Do list,will blog it soon.

        Reply
        • Dave on December 9, 2015 5:38 PM

          Hi Eswar,

          Have you managed to create a computer report based on Software Update Group?

          This would be very useful.

          Thanks

          Reply
          • Eswar Koneti on December 13, 2015 11:33 PM

            not yet Dave but will do it ,lab is still down..

            Reply
          • Eswar Koneti on December 21, 2015 4:47 PM

            Hi Dave,
            yes,i posted blog on this http://eskonr.com/2015/12/sccm-configmgr-ssrs-report-get-list-of-missing-updates-for-pc-from-specific-software-update-group/ .have a check.

            Reply
    7. Latif Yahya on November 20, 2015 9:10 PM

      Very good.
      But is it possible to get such a report based on 'custom' software update groups?

      Reply
      • Eswar Koneti on November 23, 2015 11:32 AM

        Yes It is possible. I will look into that later but here are the list of Configmgr 2012 R2 SQL views which might help you to customize reports https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b

        Reply

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.