Close Menu
    Facebook X (Twitter) Instagram
    Monday, July 14
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»CM2012»SCCM Configmgr 2012 Report Application Deployment results for specific application for specific OU

    SCCM Configmgr 2012 Report Application Deployment results for specific application for specific OU

    Eswar KonetiBy Eswar KonetiMay 11, 3:19 pm3 Mins Read CM2012 23,117 Views
    Share
    Facebook Twitter LinkedIn Reddit

    Started rolling out MS office 2013 to Users few weeks ago.For this ,created collection to get computers with MS office 2010 ,created application for MS office 2013 upgrade and deploy to collection .This collection consists of computers from different departments,business units etc.

    Colleague of mine asked me if there is way to get report for deployment status of specific application for specific OU. The reason for this requirement is that ,he wanted to get the deployment status per business unit (location)for the deployed application. OUs in AD are named with location names .

    A single collection (MS office 2013 upgrade ) consist of computers from several business units and he wants to see the status per business unit for reporting.

    The default report ‘All Application Deployment (basic)’ from software distribution—Application monitoring category works on specific collection and this collection contains the computers from different business units (Locations  which are nothing but OU’s ) did not work for this requirement. Must create new report to get the deployment status results for specific application for specific business unit.

    So I started looking out for the Configmgr 2012 R2 SQL tables/sql views using the Configmgr 2012 R2 SQL views spread sheet .

    I created report which can be used to find the deployment status of any application (but not packages) for specific OU.

    Note: This report will list the computers on which the deployment ran or atleast the computer received policy about the deployment. For ex: An OU contains 200+ computers but the collection contains only 50 Computers from this specific OU so when you run this report,you may only see results for these 50 computers but not 200+. Also remember that,this report will not give you the error description for the computer when did the deployment is failed . It requires little more work to get the description from dll files in SSRS and will update this post when am done.

    When you run the report ,you will have to select the OU ,you will see list of all OU’s from your AD .It may look ugly  with all OUs and sub OU etc but you can fine tune the SQL query this to get the child OU only.

    Report is based on the Deployment status :

    Success
    In Progress
    Requirements Not Met
    Unknown
    Error

    This report contains 2 sub reports 1)Overall deployment status results with nice pie chart for specific application for specific OU 2) sub report to get the list of computers with specific specific for the selected application .

    Deployment chart summary for specific application :

    image

     

    Sub report to list computers with specific status for application for OU (click on the Pie chart to get list of computers with specific status):

    image

     

    Download the RDL files from TechNet gallery here ,upload the RDL files to your SSRS Folder (keep both these files in same folder as they are linked),change the Data source and run it.

    Application status configmgr Configmgr 2012 configmgr report Deployment results Deployment Status results OU based report SCCM SSRS
    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

    40 Comments

    1. Dan on February 5, 2020 11:11 PM

      So I just downloaded this report, and found that when I click on the pie chart, it changes the OU, so for instance if I choose in the Deployment results for Specific Application Per OU report Domain\Office\Location it shows all the installs for the OU, but when I click on the chart to see the list the OU changes to DOmain\Office\Location\A1\Computers

      is there a way to fix this?

      Reply
      • Eswar Koneti on February 19, 2020 2:53 PM

        Hi,
        The fix is to modify the SQL code for the Pie chart.
        I will update the code when time permits.

        Thanks,
        Eswar

        Reply
    2. Navaneeth on November 1, 2018 6:33 PM

      HI Eswar,
      i need sql query for Patch deployment status report for collection based
      1.Compliant Servers
      2.Server with Install Pending (downloaded update)
      3.Server with Pending Restart,

      with this 3 status kindly help me for this query creation, i have tried lot but i am not able to create the query.

      Reply
      • Eswar Koneti on November 2, 2018 2:08 PM

        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
        • Navaneeth on November 2, 2018 4:35 PM

          Hi Eswar i need to create the report and its publish in dashboard actaully we have many collection and software update group so i have compainded all system in one collection i need the last enforcement report for that collection please help me. i have already created the query for one system but not able to create for collection kindly help me.

          select TOP 1
          vSMS_R_System.Netbios_Name0,
          vSMS_Update_ComplianceStatus.LastEnforcementMessageID,
          vSMS_Update_ComplianceStatus.LastEnforcementMessageName,
          vSMS_Update_ComplianceStatus.LastEnforcementMessageTime
          from vSMS_R_System JOIN vSMS_Update_ComplianceStatus
          on vSMS_Update_ComplianceStatus.MachineID = vSMS_R_System.ItemKey
          where Name0 in ('Hostname') ORDER BY LastEnforcementMessageTime DESC

          Reply
    3. Tariq Khan on September 25, 2018 12:31 AM

      When running the report it says "OU is missing a value"

      Reply
      • Eswar Koneti on September 30, 2018 10:43 PM

        Which report you have issues ? is it linked report or parent report ? if you are running linked report directly ,it wont work as the prompt is hidden and it comes from its parent report.

        Thanks,
        Eswar

        Reply
        • J on October 22, 2018 3:35 AM

          is there a way to add errorcode descriptions? Needed for techsupport

          Reply
    4. Tariq on August 2, 2018 8:49 AM

      Hi Eswar,

      Please pardon my stupidity but, I am kind of new to SCCM. Can you please guide where is SSRS folder located and how to change the data source.

      Thanks in advance.

      Reply
      • Eswar Koneti on August 22, 2018 11:37 PM

        you can try this link on how to change SSRS datasource https://www.enhansoft.com/blog/how-to-change-the-ssrs-datasource

        Thanks,
        Eswar

        Reply
    5. Suneel on September 28, 2017 5:07 PM

      Hi Eswar,

      Do you have any idea on how to get top 25 successful deployment of Apps and packages.

      Reply
      • Eswar Koneti on September 28, 2017 5:20 PM

        You can get but what do you mean by top 25 successful ? is it based on the number of success or success% of the deployment .

        Regards,
        Eswar

        Reply
    6. Meet on February 19, 2017 5:56 PM

      Hi Eswar...I guess there is some issue with above report it show be below error:

      An error has occurred during report processing. (rsProcessingAborted)
      Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
      Incorrect syntax near '='

      Any suggestion !!!

      Reply
      • Eswar Koneti on March 9, 2017 11:14 PM

        I tried it ,it works for me .Can you edit the RDL file for SQL query and try it in SQL server management studio.

        Regards,
        Eswar

        Reply
    7. extenue on January 25, 2017 4:19 PM

      Ola Eswar
      Thanks a lot for your blog , i used to forward internally to others SCCM admins your tricks , very helpful

      there are typo on your query (eg IIf instead of If) , but even after cleaned that i can't get the query to work , i am on SCCM 2012 R2 SP1

      Can you double-check your query please ?

      Thanks,
      XT

      Reply
      • Eswar Koneti on January 25, 2017 4:28 PM

        Hi Extenue,
        I just ran the report ,it works fine for me.What error do you get after running report ? Below is the SQL used in the report which might help you to run in SQL management studio.
        Add Displayname and OU in the SQL query before you run.

        SELECT vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],vru.department0,
        IIf([EnforcementState]=1001,'Already Present',
        IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Success',
        IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress',
        IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ',
        IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown',
        IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status,
        vrs.Last_Logon_Timestamp0,
        vgws.LastHWScan
        FROM dbo.vAppDeploymentResultsPerClient ADRP
        inner JOIN dbo.v_RA_System_SystemOUName SOU ON sou.ResourceID = ADRP.ResourceID
        right JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=ADRP.CI_ID
        inner JOIN dbo.v_R_System vrs ON vrs.ResourceID = ADRP.ResourceID
        INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws ON vgws.ResourceID=vrs.resourceid
        left JOIN dbo.v_R_User AS vru ON vru.User_Name0 = vrs.User_Name0
        INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos ON vgos.ResourceID = vrs.ResourceID
        WHERE lac.DisplayName=''
        AND SOU.System_OU_Name0 LIKE '%%'
        GROUP BY vrs.Name0 ,vgos.Caption0 ,vrs.User_Name0 ,vru.department0,vrs.Last_Logon_Timestamp0,
        vgws.LastHWScan,EnforcementState
        ORDER BY 5

        Regards,
        Eswar

        Reply
    8. Charles on November 24, 2016 2:51 AM

      Can this SQL report be modified to get the specific Collection ID instead of OU ???

      Reply
      • Eswar Koneti on November 28, 2016 1:28 AM

        yes you can modifiy the OU with collection .all you need is resource ID from v_fullcollectionmembership but there is already default reports for collection.

        Regards,
        Eswar

        Reply
        • Charles R Guerra on November 28, 2016 4:46 AM

          Eswar,

          Thanks for all your support and your time you give for your blog's ... I am familiar with the built in report but it is a pain in the butt to get what you want and that is why I am looking for a easier straight forward method to get the info needed. If you can create something simialiarthat would be Awesome ...

          Once again Thank you for all you do !!!

          Reply
          • Eswar Koneti on December 21, 2016 5:07 PM

            Hi,
            Here is the modified SQL query ,you can take this and customize in SSRS.

            SELECT distinct
            vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
            IIf([EnforcementState]=1001,'Already Present',
            IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Success',
            IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress',
            IIf([EnforcementState]>=3000 And [EnforcementState]<4000,'Requirements Not Met ',
            IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown',
            IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status,
            vrs.Last_Logon_Timestamp0,
            vgws.LastHWScan
            FROM dbo.v_R_System AS vrs INNER JOIN (dbo.vAppDeploymentResultsPerClient
            INNER JOIN v_CIAssignment ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID) ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
            INNER JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
            INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws ON vgws.ResourceID=vrs.resourceid
            INNER JOIN v_FullCollectionMembership coll ON coll.ResourceID = vrs.ResourceID
            INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos ON vgos.ResourceID = vrs.ResourceID
            WHERE lac.DisplayName='application name'

            Regards,
            Eswar

            Reply
            • Rajini on January 24, 2017 6:54 AM

              Hello Eswar
              I dont see any default reports in our environment to check the status of an application , We have reports to check the status for packages . Can you tell me how to import them in sccm .Also i need a seperate report to check the status of both applications and packages .

              Reply
              • Eswar Koneti on January 24, 2017 10:50 AM

                Hi Rajni,
                What version of SCCM are you running ? if you are on CM12 and above, there are some default reports with category 'Software Distribution - Application Monitoring' . To import this report, download the RDL file, upload to your SSRS report folder ,change the datasource and run the report.

                Regards,
                Eswar

              • Rajini on January 24, 2017 11:27 PM

                Thanks Eswar for the reply , We are currently running SCCM 2012 R2 , where can we download those reports(RDL files ) . I am pretty new to reporting .

              • Eswar Koneti on January 25, 2017 4:09 PM

                Hi Rajini,
                Run the Configmgr reports URL ,configmgr_sitecode ,look for category 'Software Distribution - Application Monitoring' . If you do not see it ,check with your Configmgr admin for this default reports.

                Regards,
                Eswar

              • Rajini on January 26, 2017 12:28 AM

                I see "All application deployments (advanced) " report under "software distribution - application monitoring " but it does not give any results , I need a report which gives me the status of the application .I am not sure if we have reports which we can import or do we need to write it completely. .

              • Rajini on January 26, 2017 2:11 AM

                I also need a report which will give status of a machine for both applications and packages as we use applications and packages , Default report "All application deployments (advanced)" will not provide any time stamp .

              • Eswar Koneti on February 2, 2017 5:24 PM

                For applications and packages ,they work altogether differently and their SQL tables/views also different .You already have default reports for both packages and application status.

                Regards,
                Eswar

        • Charles on November 28, 2016 4:49 AM

          Eswar,

          Thanks for all your support and your time you give for your blog's ... I am familiar with the built in report but it is a pain in the butt to get what you want and that is why I am looking for a easier straight forward method to get the info needed. If you can create something simialiarthat would be Awesome ...

          Once again Thank you for all you do !!!

          Reply
    9. rastamoses on February 25, 2016 6:56 AM

      Hi Eswar

      can you help

      On the Deployment chart for specific application per OU ...i dont get any application on the select the application

      On the List Computers Deployment results for Specific Application Per OU ..it goes The 'OU' parameter is missing a value

      thanks

      Reply
      • Eswar Koneti on February 25, 2016 10:12 PM

        It works good for me and others as well. Do you have any applications at all and are they deployed to clients ? if you have ,can you check the apps from view called dbo.fn_ListApplicationCIs(1033) ,The prompt used in the report is pulling the information from this view.
        You can open the RDL file using report builder ,take the SQL query used in dataset called App and try the Query in SQL management studio.

        Reply
        • Alvaro P on September 8, 2016 10:29 PM

          Do you known, how to obtein the results, when a deployment was eliminated??

          Reply
          • Eswar Koneti on September 8, 2016 10:33 PM

            what do you mean by eliminated ? Removed ? if so ,you cannot get the status for removed deployment.

            Reply
    10. John on October 9, 2015 12:30 PM

      Is there a report that show when the application was installed? Thanks

      Reply
      • Eswar Koneti on October 10, 2015 10:35 AM

        you can add installeddate0 from v_gs_add_remove_programs? installeddate value stored from this view.

        Reply
        • XT on February 23, 2016 6:48 AM

          Hello,

          have you tested this ?

          for me it cannot work as it does not exist a relationship between the application and the software , how are you linking all together ?

          Thanks
          XT

          Reply
          • Eswar Koneti on February 25, 2016 10:14 PM

            what do you mean by application and software ? This report only works for Applications but not for package(legacy). If you do not see any applications in the prompt, Open the RDL file using report builder or visual studio ,try the SQL Query used in App dataset into SQL Server management studio.
            Take a look at the SQL query to understand the linking and how it works.

            Reply
            • XT on February 26, 2016 1:39 AM

              what i meant was , have you succeed on getting installation date of an application (and not software)
              for me database only tell you date of deployment (so same date for all collection members) but not date of the installation of that application
              my management want a report of apps install made by sccm per month , i am just not able to do that

              Reply
              • Eswar Koneti on February 27, 2016 12:06 AM

                ok,got it. Nope,this report will not tell you when was the application installed since,Application deployment evaluation cycle will run as per the schedule and it always report success if install already .So the best way would be to query it from v_add_remove_programs if install date exist in this view.

    11. Jose Riano on September 29, 2015 12:54 AM

      Any chance you have this for collection based?

      Reply
      • Eswar Koneti on October 2, 2015 8:13 AM

        application deployment status report for collection based is available in default reports. have you looked at report --->All application deployments (basic) under category Software Distribution - Application Monitoring ?

        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.