Patch Compliance Progression Report

Last updated on July 27th, 2022 at 04:11 pm

This is the Patch Compliance Progression Report for MECM.  This is one of those Software Update (SU) SQL queries that you want. It will grab the MECM software update status compliance of the device. As a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM ) administrator this is something you want! Or should I say need! 

Patch compliance progression report

You can see from the query below. That it will display the computer name, User name, Outstating SU count, Last hardware scan date and time. And finally the collecting id. There is so much more that you can add to this query but it will give you a starting point!

select
      CS.Name0,
      CS.UserName0,
	case
		when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))
		else 'Good Client'
	end as 'Status',
	ws.lasthwscan as 'Last HW scan',
	 FCM.collectionID--,
from
    dbo.v_UpdateComplianceStatus UCS
	left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.ResourceID
	join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID
	join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
	left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid
	left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid
Where
      UCS.Status = '2'
	and FCM.collectionid = 'SMS00001'
Group by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID
Order by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID

This query can be used within a MECM report. MECM Reports are really SQL Server Reporting Services (SSRS). I have even created a Power BI version too of the Patch Compliance Progression Report. When you build your own reports you can even have this report drill down to the MECM built-in report call Compliance 5 – Specific computer. This report is found within the Software Updates – A Compliance folder on your reporting point. The possibilities are endless as to what you can do with it.

The one thing to watch out for is that the RBA version of this query is faster. RBA and Non-RBA Queries: When is Slower Actually Faster? It talks about how Microsoft has optimized the RBA functions to be much faster than the stand SQL views! This might not matter to you but just incase it does, it is some to know about.

What more would you like to do with this SQL query? What other tip Reporting tips would you like to know about?

Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.