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
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)
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
;with cte as ( select
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
ui.title not like '%Itanium%'
select severity, count(*) [Count]
group by Severity
order by Severity
You can add more filters to 2nd query like superseded!=0 and expired=1 etc.