Close Menu
    Facebook X (Twitter) Instagram
    Monday, July 14
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»Patch Reports»SCCM Monthly Patch statistics reports to the management in a simplified manner

    SCCM Monthly Patch statistics reports to the management in a simplified manner

    Eswar KonetiBy Eswar KonetiOctober 25, 6:05 pm1 Min Read Patch Reports 3,932 Views
    Share
    Facebook Twitter LinkedIn Reddit

    This is continuation to the post avilable here on http://eskonr.com/2010/03/monthly-patch-statistics-reports-to-show-up-to-the-management-in-a-simplified-manner/

    Report posted on the above link doesnt give you the required information what it gives in SMS 2003 since some of the columns in have been changed i.e product has blank value from v_GS_patchstausEX view etc in sccm 2007.

    Below is the modified report that works in SCCM environment.

    1) Patch Management summary:

    select summ.ID,summ.QNumbers as 'Q Number',
    COUNT(distinct ps.ResourceID) as 'Requested',
    COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)  as 'Installed',
    ROUND(100.0*COUNT(distinct case when ps.LastState=107 or ps.laststate=102 or ps.laststate=105 then ps.ResourceID else NULL End)
    /count(distinct ps.ResourceID),2) as 'Success %'
    from v_GS_PatchStatusEx ps
    join v_ApplicableUpdatesSummaryEx summ on
       ps.UpdateID=summ.UpdateID
     where (summ.ID='MS11-075') and
             (summ.Type='Microsoft Update')
    group by summ.ID,summ.QNumbers

    order by summ.ID

    PS: Please correct the quotes used in this query ,if you do copy paste,might give you error like "Incorrect Syntax error " so type the quotes again by removing the exisinting ones.

    I will post other quiries in the mean time what is avilable in SMS 2003 environment.

    Attached is MOF file which you can directly import into your configmgr 2007 environment rather copy /paste.Patch_complilance_report.MOF (remove the txt extension,you will get MOF file).

    Compliance reports Configmgr 2007 configmgr report Patch compliance status patch statistics report Patch statistics report for SCCM 2007 SCCM 2007 SCCM 2007 Patch complaince Report SCCM Reports Software updates report SCCM SQL
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Monitoring Endpoint Security Applications with SCCM ConfigMgr SQL

    October 11, 8:48 pm

    Disable windows updates (wufb) on selected devices using Intune

    July 07, 11:04 pm

    9 Comments

    1. Pingback: Patch Management | Process Street

    2. Kevin Pestrue on March 29, 2016 10:24 PM

      This report is exactly what I'm looking for, we have the same report that runs in our SCCM environment but doesn't port over to our CM12 environment. Is there a way to make this report work in CM12 by collection, maybe an RDL for ease of import?

      Reply
      • Eswar Koneti on March 29, 2016 10:39 PM

        I have posted lot many reports on patch compliance status also ,in CM12 ,you can see the status of the patches directly in the console.
        Have a look at the posts on compliance reports http://eskonr.com/?s=patch+compliance+report&submit=

        Reply
        • Kevin Pestrue on March 29, 2016 11:06 PM

          I've looked at your Config Mgr 2012 reports but don't see anything like this one that reports the current status of a deployment, Where you can select the deployment, specifically the different Last State of Software Updates, or any other deployment. I currently have a SCCM 07 report that tells me the following deployment Last State(s):
          Compliant,
          Downloaded Update(s)
          Downloading Update(s)
          Enforcement State Unknown
          Failed to download Update(s)
          Failed to install update(s)
          Installing Update(s)
          Pending system restart
          Successfully installed update(s)
          Waiting for another installation to complete

          The problem I have is that the report won't port into CM12, your 07 report is close to this information which is much more information on what the Compliance 1- Overall Compliance report generates.

          Reply
          • Eswar Koneti on March 29, 2016 11:21 PM

            have you looked at the default reports in CM12 for deployment status based on last state(s) ? While creating reports,i did not look at the last state(s) ,the more interested was, whether the client is compliant or not ,no matter the cause for Non-Compliant and it needs troubleshooting.

            Reply
            • Kevin Pestrue on March 29, 2016 11:27 PM

              Understood; however, knowing the Last State is very helpful in how the non-compliant clients get troubleshot and resolved. I guess I'll have to find another way.

              Reply
              • Eswar Koneti on April 8, 2016 8:31 AM

                it is possible using status messages to amend this into the query, i will look at it later when get time..

              • Eswar Koneti on April 15, 2016 11:53 PM

                Hi Kevin,
                it is possible using state messages , need to have sometime to look at the message ID and its description .

              • DD on October 30, 2019 4:34 PM

                Hello Kevin,

                I am currently stuck to fetch the details of all updates in an SUG deployed to a collection by their deployment state i.e. -
                Last State(s):
                Compliant,
                Downloaded Update(s)
                Downloading Update(s)
                Enforcement State Unknown
                Failed to download Update(s)
                Failed to install update(s)
                Installing Update(s)
                Pending system restart
                Successfully installed update(s)
                Waiting for another installation to complete

                Could you kindly help me with the query please. CB 1906

    Leave a ReplyCancel reply

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

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.