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.
Hi, how can I get report of recently installed computer
Can you provide more information on recently installed computer?
Thanks for the post.
One question, v_updateinfo provides information about software update group or only software update within a group ?
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.
I am looking for a report "what software got installed from Software Center on which PC" Could you please help me on it.
There are default report to get software installed information but difficult to find if it was installed through software center or manual.