SCCM Configmgr SQL query to find Top X missing updates for specific collection for specific update group

Since few days ,I am working on a customized Software update compliance dashboard report with some Pie charts for management to see how the patch compliance progress for each Business Unit (I say business unit means for each country).

Management are interested to see the overall patch compliance summary for each country (focused on servers ) into a nice pie chart which can be published to them either Via emails (using SSRS subscriptions or put them on Bigger screens especially for server compliance status).

This dashboard uses lot of pre-existing reports that are already published on my blog ,but there is one report (though SQL query is few lines code) which makes me to spend lot time doing lot of changes and check in the console if the results tally or not and the report is:

Top 5 or 10 missing patches for specific collection and specific update group.

The hard part for getting this report work is ,identifying the correct views to join Software update group ,compliance status . I would strongly recommended to use the SQL views documentation to create any custom SCCM reports.

After going through the SQL view documentation ,found below views that will help me to join the software update group (CI_ID) and software updates (CI_ID)

v_BundledConfigurationItems –contains information about each Update CI_ID and software update group ID

v_AuthListInfo –Contains Software update group Name, Update ID(CI_ID) .

For reporting (ONLY) ,we normally have 1 software update group that contains list of all updates (as per the requirement from IT Security team as they are the ones who decide what security patches to deploy ) that are  deployed to clients from so long to until 2 months old from current month  . Technically speaking, you cannot have more than 1000 updates in software update group which you can deploy to collection but ,in this case ,it is only used for reporting ,I can have more than 1000+ updates into 1 software update group and always make sure this SUG group is at good compliance rate for each BU .

As we move on, add the previous months patches to this Software update group and rerun the report to reflect the status for newly added updates against each country collection.

In this blog post, I will share you couple of SQL queries which are used my dashboard report ,help you to create your own dashboards.

P.S: The reason for not posting the dashboard which I created is because ,it has lot of customizations (more into collection ID’s and Software update group) per country basis and they are unique for each organization ,but I can share how the output of the dashboard look like.

Each pie chart has linked report to see the list of clients status like missing or unknown for troubleshooting purpose.

image

Below are couple of SQL queries that I wanted to share with you guys.

1.How to get list of top 5 or 10 missing patches against particular collection for specific software update ?

In SCCM console ,if you go to software updates node ,you can see lot of information for each update with Bulletin ID,Title ID,required,installed etc , but there is no way for you to filter against particular collection and if you want see the list of clients that needed by the patch ,no way in the console.

You either have to use default reports (if there is any such) otherwise ,create custom report.

Use the below Query in your SSRS or SQL management studio to get list of all updates from particular software update group against collection with missing count.

Declare @CollID nvarchar (255),@SUG nvarchar(255);
Set @CollID='PS100254';set @SUG='SUM_2016_July_All';
--CollID=Collection ID and SUG=Software update group Name

Select CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised,
case when ui.IsDeployed='1' then 'Yes' else 'No' end as 'Deployed',
SUM (CASE WHEN ucs.status=3 or ucs.status=1 then 1 ELSE 0 END ) as 'Installed/Not Required',
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required'
From v_UpdateInfo ui
JOIN v_Update_ComplianceStatus ucs on ucs.CI_ID = ui.CI_ID --AND ui.IsExpired = 0 AND ui.IsSuperseded = 0
--If you want display the expired and superdeded patches, remove the -- line in the above query
JOIN v_BundledConfigurationItems bci on ui.CI_ID = bci.BundledCI_ID
JOIN v_FullCollectionMembership fcm on ucs.ResourceID = fcm.ResourceID
join v_R_System sys on sys.ResourceID=ucs.ResourceID
where bci.CI_ID = (SELECT CI_ID FROM v_AuthListInfo where title=@SUG)
and fcm.CollectionID
=@CollID
group by CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2),
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised, ui.IsDeployed
order by sum( case When ucs.status=2 Then 1 ELSE 0 END ) desc

If you compare the result you get from above SQL query ,the required count of clients will vary from what you see in the SCCM console software updates node and this is due the fact that ,in the console ,the software updates do not have any limitation over any collection(They apply to all clients) .But here ,we are trying to limit the software update against particular collection.

You can use this SQL query in multiple ways as you  need.For example ,if someone want to see the list of updates that are still needed by specific collection(BU) ,you can simply comment Software update group and choose only collection ,you can also do the other way.

To get top 5 or 10 missing updates ,simply use TOP 5 or TOP 10 . Full SQL Query is below:

Declare @CollID nvarchar (255),@SUG nvarchar(255);
Set @CollID='PS100254';set @SUG='SUM_2016_July_All';
--CollID=Collection ID and SUG=Software update group Name

Select top 5 CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised,
case when ui.IsDeployed='1' then 'Yes' else 'No' end as 'Deployed',
--SUM (CASE WHEN ucs.status=3 or ucs.status=1 then 1 ELSE 0 END ) as 'Installed/Not Required',
sum( case When ucs.status=2 Then 1 ELSE 0 END ) as 'Required'
From v_UpdateInfo ui
JOIN v_Update_ComplianceStatus ucs on ucs.CI_ID = ui.CI_ID --AND ui.IsExpired = 0 AND ui.IsSuperseded = 0
--If you want display the expired and superdeded patches, remove the -- line in the above query
JOIN v_BundledConfigurationItems bci on ui.CI_ID = bci.BundledCI_ID
JOIN v_FullCollectionMembership fcm on ucs.ResourceID = fcm.ResourceID
join v_R_System sys on sys.ResourceID=ucs.ResourceID
where bci.CI_ID = (SELECT CI_ID FROM v_AuthListInfo where title=@SUG)
and fcm.CollectionID =@CollID
group by CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2),
ui.Title, ui.ArticleID, ui.BulletinID, ui.DateRevised, ui.IsDeployed
order by sum( case When ucs.status=2 Then 1 ELSE 0 END ) desc

Now that, we have count of all updates for specific update group for specific collection with required client count ,but how to get the list of clients needed need specific update ?

This is mainly needed if you want to create linked SSRS report to see the list of clients for specific update for troubleshooting purpose.

SQL Query to list the clients required by specific software update ?

 

Declare @CollID nvarchar (255),@SUG nvarchar(255),@title nvarchar(255);
Set @CollID='PS100254';set @SUG=''SUM_2016_July_All'';
set @title='Security Update for Windows Server 2008 R2 x64 Edition (KB2992611)'
--CollID=Collection ID , SUG=Software update group Name and Title= Name of Software update title

Select sys.Name0,sys.User_Name0,os.Caption0 [OS],ws.LastHWScan,uss.LastScanTime [Last SUScan],os.LastBootUpTime0
From v_UpdateInfo ui
JOIN v_Update_ComplianceStatus ucs on ucs.CI_ID = ui.CI_ID
JOIN v_BundledConfigurationItems bci on ui.CI_ID = bci.BundledCI_ID
JOIN v_FullCollectionMembership fcm on ucs.ResourceID = fcm.ResourceID
join v_R_System sys on sys.ResourceID=ucs.ResourceID
join v_GS_OPERATING_SYSTEM OS on os.ResourceID=ucs.ResourceID
join v_GS_WORKSTATION_STATUS WS on ws.ResourceID=ucs.ResourceID
right join v_UpdateScanStatus uss on uss.ResourceID=ucs.ResourceID
where bci.CI_ID = (SELECT CI_ID FROM v_AuthListInfo where title=@SUG)
and fcm.CollectionID =@CollID
AND UCS.Status='2'
and ui.Title=@title
group by
sys.Name0,sys.User_Name0,os.Caption0,ws.LastHWScan,os.LastBootUpTime0,uss.LastScanTime
order by 1

 

SQL Query used in Pie Chart to get the patch compliance status for specific Collection and for specific update group ?

select CASE WHEN ucs.status=3 or ucs.status=1  then 'success'
When ucs.status=2 Then 'Missing'
When ucs.status=0 Then 'Unknown' end as 'Status',ucs.status [Status ID],coll.CollectionID
From v_Update_ComplianceStatusAll UCS
    left join v_r_system sys on ucs.resourceid=sys.resourceid
    left join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
    left join v_collection coll on coll.CollectionID=fcm.CollectionID
    left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
    left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
    left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
    left join v_AuthListInfo LI on li.ci_id=ucs.ci_id
where li.title='Software update group name' and coll.CollectionID=’CollectionID’
and os.Caption0 not like '%2003%'
order by 1

Hope these SQL queries are helpful to you .

12 Responses to "SCCM Configmgr SQL query to find Top X missing updates for specific collection for specific update group"

  1. Hi Eswar, lot of other reports that has technet links for RDL files are not working and redirecting to somewhere else of Microsoft. I am keen to use few of your SCCM patch reports but not able to do so. Can you verify it pls.

    Reply
    1. Hi NAFJU,
      Thanks for the kind words and appreciate it.
      Do let me know incase you looking for any sort of reports that will be useful.

      Regards,
      Eswar

      Reply
    1. As i said in blog post ,i have used the query that i posted here http://eskonr.com/2016/05/sccm-configmgr-software-update-compliance-report-for-multiple-software-update-groups-per-collection/ and from there i extended it to show the compliance % for each collection (static) for specific software update group (static but keep adding the updates to it when patching goes on every month) .
      My dashboard uses lot of bulitin parameters like collection ID for each country/Business Unit and specific software update group.
      I have posted lot of patch compliance reports ,you can download the RDL file ,have a look at it.

      Thanks,
      Eswar

      Reply
  2. I am trying to add the Software Update Group parameter and it works if I declare it in the SQL Statement but trying to use as parameter it keeps coming back with blank data... thoughts?

    --DECLARE @SUGID VARCHAR(50)
    --SET @SUGID = 'Software Updates | Servers | 2016';
    select CASE WHEN ucs.status=3 or ucs.status=1 then 'success'
    When ucs.status=2 Then 'Missing'
    When ucs.status=0 Then 'Unknown' end as 'Status',ucs.status [Status ID],coll.CollectionID
    From v_Update_ComplianceStatusAll UCS
    left join v_r_system sys on ucs.resourceid=sys.resourceid
    left join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
    left join v_collection coll on coll.CollectionID=fcm.CollectionID
    left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
    left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
    left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
    left join v_AuthListInfo LI on ucs.ci_id=li.ci_id
    where li.title = @SUGID
    and coll.CollectionID='XXXXXXX'
    and os.Caption0 not like '%2003%'
    order by 1

    Reply
    1. Hi,
      os.Caption0 not like '%2003%' is used to filter server 2003 OS . If you do not have server 2003 ,you can either remove it or leave it like that .It doesnt harm anything.

      Thanks,
      Eswar

      Reply
    1. Hi,
      Each pie chart you see in the report has pre-defined collectionID and Software update Group Name which you will have to do edit for each Graph .
      Here is the SQL code i used in all the pie charts to show the patch compliance %:

      select CASE WHEN ucs.status=3 or ucs.status=1 then 'success'
      When ucs.status=2 Then 'Missing'
      When ucs.status=0 Then 'Unknown' end as 'Status',ucs.status [Status ID],coll.CollectionID
      From v_Update_ComplianceStatusAll UCS
      left join v_r_system sys on ucs.resourceid=sys.resourceid
      left join v_FullCollectionMembership fcm on ucs.resourceid=fcm.resourceid
      left join v_collection coll on coll.CollectionID=fcm.CollectionID
      left join v_GS_OPERATING_SYSTEM os on ucs.resourceid=os.resourceid
      left join v_gs_workstation_status ws on ucs.resourceid=ws.resourceid
      left join v_updatescanstatus uss on ucs.ResourceId=uss.ResourceID
      left join v_AuthListInfo LI on li.ci_id=ucs.ci_id
      where li.title='Software update group Name' and coll.CollectionID='CollectionID'
      and os.Caption0 not like '%2003%'
      order by 1

      I have updated the blog post with this query.

      Reply

Post Comment