SCCM Report

Question

Hello All,

Need SQL expert advice please. Trying to create a report. Details Below:

SCCM Version MECM 2111 with Latest Hotfix
Primary Site

4 MP, 2 SUP, 40 DP, 1 Dataware House, CMG, Co-Management Enabled, SQL With Always ON

I am trying to fetch all the application deployments which are marked as “Require Approval” and the e-mail address defined under “Approver’s E-mail” at the time of application deployment. Screenshot of the application deployment page where we see all these information’s to select and define Approver.
SQL Deploy - HTMD Forum - Welcome to the world of Device Management! This is community build by Device Management Admins for Device Management Admins❤️ Ask your questions!! We are here to help you! - SCCM Report
If the screenshot not clear then use link https://snipboard.io/7t6IxY.jpg

solved 0
Deepak Rai 2022-03-14T04:30:36+05:30 3 Answers 36 views Admin 0

Answers ( 3 )

  1. Hi, I think the only option is to look into SQL tables or views to understand whether there is any been collected and stored in DB such as email-ID.

    I don’t remember anything on top of my head.

      0
      2022-03-15T03:53:09+05:30

      Ok Sir. Thanks for the guidance. Will come back with results.

        0
        2022-04-04T07:12:39+05:30

        I got it Sir. I broke it into 2 parts.

        Application deployed with the option “Requires Approval”

        You already wrote this one so i just added “RequireApproval = 1”

        Link for that query below:
        https://www.anoopcnair.com/sccm-application-deployment-custom-report-sql-query-configmgr/

        Select
        Pac.PackageID as ‘App_ID’,
        col.CollectionID as ‘AppCollection_ID’,
        Vaa.ApplicationName as ‘ApplicationName’,
        Ds.CollectionName as ‘CollectionName’,
        AssignmentName as’AssignmentName’,
        CASE when col.CollectionType = 1 Then ‘User’ when col.CollectionType = 2 Then ‘Device’ Else ‘Others’ End as ‘CollType’,
        CASE when Vaa.DesiredConfigType = 1 Then ‘Install’ when vaa.DesiredConfigType = 2 Then ‘Uninstall’’ Else ‘Others’ End as ‘DeploymentType’,
        CASE when Ds.DeploymentIntent = 1 Then ‘Required’ when Ds.DeploymentIntent = 2
        Then ‘Available’ when Ds.DeploymentIntent = 3 Then ‘Simulate’ End as ‘Purpose’,
        Ds.DeploymentTime as ‘AvailableTime’,
        Ds.EnforcementDeadline as ‘RequiredTime’,
        Vaa.CreationTime as ‘CreatedOn’,
        Vaa.LastModificationTime as ‘LastModifiedOn’,
        Vaa.LastModifiedBy as ‘LastModifiedBy’
        from v_DeploymentSummary Ds
        left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
        left join v_Package Pac on Vaa.ApplicationName = Pac.Name
        left join v_collection col on Ds.CollectionName = col.Name
        Where Ds.FeatureType = 1 and RequireApproval = 1
        order by Ds.DeploymentTime desc

        The second requirement “Approver’s E-mail” used at the time of app deployment is little complicated. The only place SCCM DB storing this information is under “Subscription” because as soon as we select the option “An administrator must approve this request” then it creates one subscription so if any users selects the app then approver gets notified from subscription option so i wrote this one. It’s not fancy but get’s the job done.

        Select

        Name,

        EmailRecipients,

        CreatedBy,

        CreationTime,

        ModifiedBy

        From Subscriptions where Name like’%Approve Application%’

        Best answer
        Cancel the best answer

Leave an answer

Sorry, you do not have a permission to answer to this question .