Close Menu
    Friday, November 22

    SCCM Patch compliance Report Last 1 month for specific Collection

    Eswar KonetiBy 2 Mins Read CM2012 9,356 Views

    Update: SSRS 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!

    Share.

    Related Posts

    13 Comments

    1. Navaneeth on

      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
      • Eswar Koneti on

        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
    2. sundeep on

      Hi Eswar,

      How can i include the machine name on this query. Can you help me on this

      Thanks in Advance
      Sundeep

      Reply
      • Eswar Koneti on

        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
    3. georges on

      Hi

      it's possible to send me the RDL file for sccm 2007 ?
      I would liket to import on SSRS Report folder and Run 🙂

      thanks in advance

      Reply
      • Eswar Koneti on

        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
    4. Paul Murray on

      Hi Eswar,

      Since v_GS_PatchStatusEx is not in SCCM 2012 do you have an updated report for 2012 to replace this one?

      Thanks
      -Paul

      Reply
    6. jengo on

      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
    7. Jijo on

      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

    Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.