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 http://www.enhansoft.com/blog/how-to-perform-a-basic-software-audit

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.

select
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,
ui.BulletinID,
ui.ArticleID,
ui.Title,
UpdateClassification=cls.CategoryInstanceName,
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:

image

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

  1. Hi Eswar, I realize this post is a bit dated but is this reporting based on updates pushed via sccm or in my case, our company has a separate wsus system that handles updates independently of sccm. Will it still work?

    Reply
    1. Hi Jon,
      If you dont have SCCM, you cannot use this report. This is purely for SCCM and WSUS is integrated wit it.
      If you have different patching solution, the SQL tables/Views might be different hence this report wont work.
      You need to see what data is available in your system and create a report.

      Thanks,
      Eswar

      Reply
  2. very helpful.
    But one thing I just can't understand. how to understand or create a list of clients that do not work updates. example: these updates have not been successful on these PCs. Incredible that there is no way.

    Reply
    1. Hi Marco,
      you can try the default software update compliance reports to know the list of clients or you can use the SQL views to create custom report.

      Thanks,
      Eswar

      Reply
  3. very helpful.
    But one thing I just can't understand. how to understand or create a list of clients that do not work updates. example: these updates have not been successful on these PCs. Incredible that there is no way.

    Reply
  4. Hi Eswar - I tried running this sql query after changing the Collection ID. But it is stuck on executing. Any suggestions?

    Reply
    1. Hi,
      how many clients are managing using configmgr? it may take time depends on the number of clients that the collection have else it will be quick.

      Thanks,
      Eswar

      Reply
  5. Hi Eswar
    Quries
    Will SCCM 1806 or 1906 capable to track daily patching deployments ?
    Will I get to know where is the patching delays while in deployments ??
    How delays can be track or monitor ?
    Please advice.
    reg
    DJ

    Reply
    1. Hi,
      Yes they are. Have you looked at the default software update compliance reports?
      also the monitoring node for the compliance status?

      Thanks,
      Eswar

      Reply
  6. Hi Eswar

    Quries

    Will SCCM 1806 or 1906 capable to track daily patching deployments ?
    Will I get to know where is the patching delays while in deployments ??
    How delays can be track or monitor ?
    Please advice.

    reg
    DJ

    Reply
    1. Hi,
      Yes they are. Have you looked at the default software update compliance reports?
      also the monitoring node for the compliance status?

      Thanks,
      Eswar

      Reply
  7. 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’

    Regards,
    Sam

    Reply
    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.

      Regards
      Eswar

      Reply
    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 ?

      Thanks,
      Eswar

      Reply
  8. 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

    Business area MSRC KB INSTALLED PENDING
    ba 1 MSRC1 KB1
    KB2
    KB3
    BA1 MSRC2 KB1
    KB2
    KB3

    Reply
    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).

      Thanks,
      Eswar

      Reply

Post Comment