SCCM Patch compliance Report Last 1 month for specific Collection

UpdateSSRS Report for Configuration manager 2012 (all Versions) is available on http://eskonr.com/2014/02/configmgr-ssrs-report-patch-compliance-statistics-last-30-days/

In my previous blogs,I posted couple of reports for Patch compliance status based on the KB/MS ID numbers .

SCCM Patch Compliance Progress report:  http://eskonr.com/2009/10/patch-status-compliancesoftware-updates-report-in-sms-sccm/

Software Update Compliance Status on Specific Collection : http://eskonr.com/2009/09/report-for-software-update-report-for-software-update-compliance/

SCCM monthly Patch statistics report http://eskonr.com/2011/10/sccm-monthly-patch-statistics-reports-to-the-management-in-a-simplified-manner/

This report is going to be interesting .It has 2 SQL queries in one Report .One with count of Active patches for past 1 month and percentage successful

and Other is what are the patches Active on specific collection of machines with Installed,Missing ,required and Percentage of successful.

Report looks like this :

image

SCCM Report :

Select 'Total number of active patches within 30days:', COUNT(distinct Title) AS 'Count'
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30
UNION
select 'Percent sucessfully installed', round(100.0*COUNT( case when LastState=107 or LastState=105 then ResourceID else NULL end)/COUNT(ResourceID),1) as 'Percent successful'
FROM v_GS_PatchStatusEx
WHERE (DATEDIFF(Day, LastStatusTime, GETDATE())) <=30

select ps.ID, ps.QNumbers, ps.Title,
round(100.0*COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end)/COUNT(distinct ps.ResourceID),1) as 'Percent successful' ,
COUNT(distinct case when ps.LastState=107 or ps.LastState=105 then ps.ResourceID else NULL end) as 'Distribution Successful',
COUNT(distinct case when ps.LastState=101 then ps.ResourceID else NULL end) as 'Distribution Failed',
COUNT(distinct case when ps.LastState not in (107,105,101) then ps.ResourceID else NULL end) as 'Distribution Incomplete',
COUNT(distinct ps.ResourceID) as 'In Distribution Scope',
'SMS00001' as 'CollectionID',
'Microsoft Update' as 'Type',
inf.InfoPath
from v_GS_PatchStatusEx ps
join v_FullCollectionMembership fcm on ps.ResourceID=fcm.ResourceID
join v_ApplicableUpdatesSummaryEx inf on
ps.UpdateID=inf.UpdateID
where fcm.CollectionID= @COLLID and
inf.Type = 'Microsoft Update'
AND (DATEDIFF(Day, ps.LastStatusTime, GETDATE())) <=30
group by ps.ID, ps.QNumbers, ps.Title, inf.InfoPath

 

Prompt for COLLID :

select distinct CollectionID,Name from v_FullCollectionMembership

If you need to drill down what are the computers missing specific Path,Create report for Computers missing Particular Patch and link it here.

Do more customizations how you want.

I have exported the report into MOF file ,easy to import without any syntax errors while creating report. Here you go with MOF file 

Until Then!

13 Responses to "SCCM Patch compliance Report Last 1 month for specific Collection"

  1. Hi Friends,

    i need a SQL query it should show list of servers with below mention Enforcement status after the patch deployment i have tried lot but nothing worked kindly help me on this.

    1.Compliant Servers
    2.Server with Install Pending
    3.Server with Pending Restart

    Reply
    1. All this information you can get it if you are on Configmgr current branch 1802 and above .There is pending restart column available for clients .

      Thanks,
      Eswar

      Reply
    1. This report tells you what patches are approved/deployed since 1 month .How is machine related here ? do you want to know what are the patches deployed to specific collection during last 30 days ? if you ,you can edit the query and and replace the collection with name0 from v_R_system .
      you need to replace the inner join collection with v_r_system .

      Reply
    1. Use Report Builder and make use of this SQL Query with prompt,should work.I dont have RDL file now as i have cm12 environment and this query will not work in CM12.

      Reply
  2. Hi Eswar,
    Would you be able to give me the export file ? Cant seem to create the query.
    Keeps showing errors in SQL query. Alternatively, can send me the screenshots ? Thanks

    Reply
  3. Hi Eswar.

    The above report is based on hw ineventory data of update deployment through Itmu..in sccm we may need to use updateinfo,clientupdatecompliance status all view to get real time state message based data..

    Reply

Post Comment