SCCM Configmgr How to generate patch compliance report that shows all updates for specific collection ?

There are couple of posts on TechNet blog and other forums asking for ‘Is there any report that list all updates with compliance status for specific collection’ ? Read it once again ,A software update compliance report that list all updates whether Targeted, Installed, Missing for specific Collection. A collection may contain X number of clients and each client may produce Y Number of patches for sure from your Configmgr environment with compliance status . So when you generate report with this requirement,you know how many rows do you get ? X (number of clients)* Y (number of patches).

Assume X=200 Clients and Y =100 Patches (till date) ,the report consists of 200* 100=20,000 rows.

So if you are going to generate report for management to show ,what is the compliance status (this is not really compliance status required for Management) with this requirement,Are they going to read it ? How does it matter to them,if particular computer is missing XX number of patches .Management wants to know if the PC is compliant or not .

You should not encourage for such unrealistic requests,if they are going to ask you,tell them ,what they are going to get .Garth has blogged similar kind of requirement How to perform basic Software Audit ,read it here

So ,how do you perform basic software update compliance audit ? There are variety of reports available by default in Configmgr with category  Software Updates - A Compliance.

If your management asked for any patch compliance report, get them overall compliance status from specific collection for specific update group (this will get overall compliance from specific software update group only) or compliance status for each machine from specific collection (this will generate report with all updates in your Configmgr).

There are lot of patch compliance reports on my blog with different requirements ,so have a look at them ,download the RDL file ,edit the RDL file using your favorite Tool(Report builder or BI or Visual studio etc) ,

edit the RDL file,copy the code into SQL server management studio ,do adjustments what is needed for you and get back the query to SSRS Report.

The recommended procedure /best practice is ,try to use the existing default reports or reports posted on my blog for compliance status per collection OR Per OU etc and start looking at computers that are NON-Compliant (if at least one patch is required by Client,it report as Non-Compliant)  and start troubleshooting the non-Compliant PC rather generating unnecessary data.

If you still want to generate such report, here is the SQL query for you Smile .All you need is ,just change the collection ID  and run it from SQL Server management Studio.

vsv.Netbios_Name0 [PC Name],
CAST(DATEPART(yyyy,ui.DatePosted) AS varchar(255)) + '-' + RIGHT('0' + CAST(DATEPART(mm, ui.DatePosted) AS VARCHAR(255)), 2) AS MonthPosted,
Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
Installed=(case when css.Status=3 then '*' else '' end),
IsRequired=(case when css.Status=2 then '*' else '' end),
Deadline=CONVERT ( varchar (26) , cdl.Deadline , 100),
ui.dateposted [Date Posted],
ui.InfoURL as InformationURL
from V_UpdateComplianceStatus  css
join V_UpdateInfo ui on ui.CI_ID=css.CI_ID
join V_CICategoryInfo_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
join V_CICategoryInfo_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
join v_ClientCollectionMembers ccm on ccm.ResourceID=css.ResourceID
JOIN dbo.v_R_System_valid vsv ON vsv.ResourceID = css.ResourceID
left join V_CITargetedMachines   ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
outer apply (
select Deadline=min(a.EnforcementDeadline)
from V_CIAssignment  a
join V_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
) cdl
where ccm.CollectionID='PS100018'
order by ui.Title

Output of the SQL Query:


13 Responses to "SCCM Configmgr How to generate patch compliance report that shows all updates for specific collection ?"

  1. Hi Eswar,

    I ran the query which you mentioned but I got below error. I am running SCCM 2016 and SQL Server 2016.

    Error :- Invalid Object Name ‘V_UpdateCompliaceStatus’


    1. Hi,
      What version of sccm are you on? Surprise to see invalid object name for updatecompliancestatus. Open sql server management studio and run select top 10 from v_updatecompliancestatus if you see any results or not.


    1. Hi Igor,
      No ,I haven't created any SSRS report because it is not genuine report to run so often .If someone run this report against collection of 1000 computers and each computer start generating list of 100 patches then it is going to be 1000*100 rows report which no one is going to monitor. So i just left the SQL query for users if they want to make use of it to create report.
      why do you think this report is important ?


  2. Hi Eswar,

    I need a report which should show below details as output
    1.Business area(since we deploy patches to many areas)
    2.individual installed, pending count of devices for individual patches of that Business area

    ba 1 MSRC1 KB1
    BA1 MSRC2 KB1

    1. Hi,
      Business area is something you need to define in your reports how you want to display it and for KB's installed or not ,that is going to be long list since each month MS release lot of updates.
      What i would suggest is ,create software update group (it contain list of updates) and run the report against this update group for specific collection (i believe this collection will be your business area).



Leave a Reply