Close Menu
    Facebook X (Twitter) Instagram
    Sunday, May 18
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr Get count of software updates with its severity (Critical,Important,Moderate and Low)

    SCCM Configmgr Get count of software updates with its severity (Critical,Important,Moderate and Low)

    Eswar KonetiBy Eswar KonetiJuly 18, 1:25 pm3 Mins Read CM2012 5,717 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Quick post on how to get count of list of updates or count of updates in your Configuration Manager with severity categorised as Critical,Important,Moderate and Low.

    In order to get this information count of updates with severity, you first need to identify what the SQL views that store this information about software updates.

    Get the SQL views documentation for all Configmgr versions starting from SCCM 2012 to Current Branch 1702 from https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-R2-SQL-5fefdd3b

    Severity of the software updates is stored in v_UpdateInfo .This SQL view stores lot of other information like title,article ID,bulletin ID,date posted and lot more. Most of the metadata about software update information is stored in this SQL view v_UpdateInfo.

    We will try to use this SQL view to get count of software updates with its severity.

    Listed below are severity and its description:

    Severity=2 –> Low

    Severity=6—>Moderate

    Severity=8—>Important

    Severity=10—>Critical

    Am Listing 2 SQL Queries here for you . 1) without any filters and this will get you what is available in your CM database 2) With custom filters and more of modified version (Thanks to Sherry on myitforum)

    1.

    select CASE(ui.Severity)
    When 2 Then 'Low' When 6 Then 'Moderate' When 8 Then 'Important' When 10 Then 'Critical' Else 'NA' End as 'Severity',
    ui.Severity ,count(ui.ci_id) [Total Updates]
    from v_updateinfo ui
    group by ui.severity
    order by 3 desc

     

    image

    2.

    ;with cte as (   select
       CI_ID,
               BulletinID,
               ArticleID,
               Title,
               DatePosted,
                       DateRevised,
                       isExpired,
                       isSuperseded,
               CI_UniqueID,
             case
            when (ui.severity=0 and ui.CustomSeverity=0) or ui.severity is null then '0 None'
            when ui.CustomSeverity=6 then '6 Moderate'
            when ui.CustomSeverity=8 then '8 Important'
        when ui.CustomSeverity=10 then '10 Critical'
        when ui.CustomSeverity=2 then '2 Low'
        when ui.Severity=2 and ui.CustomSeverity=0 then '2 Low'
            when ui.Severity=6 and ui.CustomSeverity=0  then '6 Moderate'
            when ui.Severity=8 and ui.CustomSeverity=0  then '8 Important'
            when ui.Severity=10 and ui.CustomSeverity=0  then '10 Critical'
            end as 'Severity'
        from v_UpdateInfo ui
    Where
       ui.title not like '%Itanium%'
    )
    select severity, count(*) [Count]
    from cte
    group by Severity
    order by Severity

    image

    You can add  more filters to 2nd query like superseded!=0 and expired=1 etc.

    classification configmgr count of updates with severity custom severity SCCM sccm report SQL SQL query SSRS v_UpdateInfo
    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

    6 Comments

    1. dyzney on July 3, 2020 2:11 PM

      Hi, how can I get report of recently installed computer

      Reply
      • Eswar Koneti on July 8, 2020 12:12 AM

        Can you provide more information on recently installed computer?

        Thanks,
        Eswar

        Reply
    2. Rohit Kolekar on July 23, 2018 11:34 AM

      Hi Eswar,

      Thanks for the post.
      One question, v_updateinfo provides information about software update group or only software update within a group ?

      Thanks Rohit

      Reply
      • Eswar Koneti on July 26, 2018 8:32 PM

        v_updateinfo contains metadata of all software updates .so if you need to know about update groups then can use v_authlistinfo and there are many other SQL views that help you to query for.

        Regards,
        Eswar

        Reply
    3. kothamass nagarjunna on May 7, 2018 10:20 PM

      Hi Eswar,

      I am looking for a report "what software got installed from Software Center on which PC" Could you please help me on it.

      Reply
      • Eswar Koneti on July 8, 2018 8:27 PM

        There are default report to get software installed information but difficult to find if it was installed through software center or manual.

        Regards,
        Eswar

        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.