Close Menu
    Facebook X (Twitter) Instagram
    Sunday, July 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr SSRS SQL report Status of All Packages with Targeted Installed Compliance %

    SCCM Configmgr SSRS SQL report Status of All Packages with Targeted Installed Compliance %

    Eswar KonetiBy Eswar KonetiSeptember 10, 9:32 pm3 Mins Read CM2012 3,060 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Reporting in Configuration Manager 2012 and later, is Replaced with SSRS with more features and little complicated if you have not used to it.

    In this blog post, we will see how to check the status of the packages with their distribution status.

    To know the Status of content (Packages) ,you can look at monitoring Node—>Distribution Status—>Content But somehow i did not like view to go every time (I feel it takes much time to load all the packages) to know the distribution status of all applications such as apps, packages, software update packages etc created in configuration manager.

    I then created simple SQL Query which you can use to create SSRS report in nice format or Run it from SQL Server management Studio.

    What you get from this : Software Name,PackageID,Package Type ,Targeted,Installed,Not Installed,Compliance,Manufacturer,Source Size in MB,Source Version.

    The following is the SQL query to get the status of all apps that are distributed to atleast 1 distribution point.

    SELECT  distinct v_Package.Name [Software], PSD.PackageID,
    Case v_Package.PackageType
    When 0 Then 'Package'
    When 3 Then 'Driver'
    When 4 Then 'Task Sequence'
    When 5 Then 'software Update'
    When 7 Then 'Virtual'
    When 8 Then 'Application'
    When 257 Then 'Image'
    When 258 Then 'Boot Image'
    When 259 Then 'OS'
    Else ' '
    END AS 'Type',
    PSR.Targeted ,PSR.Installed,(psr.Failed+psr.Retrying) as [Not Installed],
    ROUND((100 * psr.installed/PSR.Targeted),2) as 'Compliance',
    v_Package.Manufacturer, SMSPackages.SourceSize / 1000 AS [Size (MB)]
    ,v_Package.SourceVersion
    FROM         v_PackageStatusDistPointsSumm PSD
    INNER JOIN v_Package ON PSD.PackageID = v_Package.PackageID
    INNER JOIN SMSPackages ON v_Package.PackageID = SMSPackages.PkgID
    inner JOIN v_PackageStatusRootSummarizer PSR ON PSR.PackageID=PSD.PackageID
    group by v_Package.Name,psd.PackageID,v_Package.PackageType,psr.Targeted,PSR.Installed,v_Package.Manufacturer,SMSPackages.SourceSize,v_Package.SourceVersion,
    PSR.Failed,PSR.Retrying
    ORDER BY 7

    The following is the updated query to list the status of all applications irrespective of their distribution status (if they are  distributed or not).

    SELECT distinct v_Package.Name [Software], case when PSD.PackageID is null then v_Package.PackageID else PSD.PackageID end as PackageId ,
    Case v_Package.PackageType
    When 0 Then 'Package'
    When 3 Then 'Driver'
    When 4 Then 'Task Sequence'
    When 5 Then 'software Update'
    When 7 Then 'Virtual'
    When 8 Then 'Application'
    When 257 Then 'Image'
    When 258 Then 'Boot Image'
    When 259 Then 'OS'
    Else ' '
    END AS 'Type',
    PSR.Targeted ,PSR.Installed,(psr.Failed+psr.Retrying) as [Not Installed],
    --@Product1 / NULLIF(@Product2,0)
    ROUND((100 * psr.installed/nullif(PSR.Targeted,0)),2) as 'Compliance',
    v_Package.Manufacturer, SMSPackages.SourceSize / 1000 AS [Size (MB)]
    ,v_Package.SourceVersion
    from v_Package
    left join v_PackageStatusDistPointsSumm PSD on v_Package.PackageID=PSD.PackageID
    left JOIN SMSPackages ON v_Package.PackageID = SMSPackages.PkgID
    left JOIN v_PackageStatusRootSummarizer PSR ON v_Package.PackageID=PSR.PackageID
    group by v_Package.Name,psd.PackageID,v_Package.PackageType,psr.Targeted,PSR.Installed,v_Package.Manufacturer,SMSPackages.SourceSize,v_Package.SourceVersion,
    PSR.Failed,PSR.Retrying,v_Package.PackageID
    ORDER BY 7

    More fields, you can customize it.

    Next post, it will be SSRS SQL report :Status of Failed Packages on what Distribution points .This can be linked to above report nice way for Troubleshooting. http://eskonr.com/2013/09/sccm-configmgr-2012-ssrs-report-package-compliance-status-for-all-distribution-points/

    Hope this helps!

    configmgr Distribution Point Failed Packages SCCM SQL Status of Packages
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    SCCM SQL Report – Compare Installed Apps on TWO Different Computers

    July 13, 10:35 am

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    6 Comments

    1. Carl Radcliff on July 9, 2021 7:59 PM

      Thank you for sharing the query, but I am only getting packages with targeted to 1 or more dp's. However, its not showing anything with do DP targeted even though in the console it shows those. Any idea how to reflect that?

      Reply
      • Eswar Koneti on July 25, 2021 3:12 AM

        Hi Carl,
        thank you for the query.
        The following is the modified query for your request. I have also updated the blog post with this query too!

        SELECT distinct v_Package.Name [Software], case when PSD.PackageID is null then v_Package.PackageID else PSD.PackageID end as PackageId ,
        Case v_Package.PackageType
        When 0 Then 'Package'
        When 3 Then 'Driver'
        When 4 Then 'Task Sequence'
        When 5 Then 'software Update'
        When 7 Then 'Virtual'
        When 8 Then 'Application'
        When 257 Then 'Image'
        When 258 Then 'Boot Image'
        When 259 Then 'OS'
        Else ' '
        END AS 'Type',
        PSR.Targeted ,PSR.Installed,(psr.Failed+psr.Retrying) as [Not Installed],
        --@Product1 / NULLIF(@Product2,0)
        ROUND((100 * psr.installed/nullif(PSR.Targeted,0)),2) as 'Compliance',
        v_Package.Manufacturer, SMSPackages.SourceSize / 1000 AS [Size (MB)]
        ,v_Package.SourceVersion
        from v_Package
        left join v_PackageStatusDistPointsSumm PSD on v_Package.PackageID=PSD.PackageID
        left JOIN SMSPackages ON v_Package.PackageID = SMSPackages.PkgID
        left JOIN v_PackageStatusRootSummarizer PSR ON v_Package.PackageID=PSR.PackageID
        group by v_Package.Name,psd.PackageID,v_Package.PackageType,psr.Targeted,PSR.Installed,v_Package.Manufacturer,SMSPackages.SourceSize,v_Package.SourceVersion,
        PSR.Failed,PSR.Retrying,v_Package.PackageID
        ORDER BY 7

        Thanks,
        Eswar

        Reply
    2. Greg C Gilbert on July 21, 2014 5:32 PM

      Perfect timing, I have a meeting this morning to discuss eliminating about 95 DPs. This will help make my case that most of them really aren't being used for much today since we have Nomad.

      One addition that may be required. If you have any packages that are not currently targeted at any DPs, you will need this Where statement to avoid a divide by zero error from the "Compliance" field calculation.

      WHERE psr.Targeted 0

      Reply
      • Eswar Koneti on July 22, 2014 7:52 AM

        the query what i posted to get status for all dps what you see in console but your condition refers to the DP's in which,no packages targeted. you can use this condition to know none of the packages targeted to DP

        Reply
    3. Johan Pol on September 10, 2013 11:54 PM

      Hi,

      I do get the following when executed from SQL Management Studio

      Msg 229, Level 14, State 5, Line 1
      The SELECT permission was denied on the object 'v_PackageStatusRootSummarizer', database 'CM_T01', schema 'dbo'.

      Any idea what goes wrong ?

      Reply
      • Eswar Koneti on September 16, 2013 9:20 AM

        You need to have read permissions on the database.Watch the blog on this

        Reply

    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.