SCCM SQL Reports With Approvers Email Address And Require Approval Details Of Application Deployments HTMD Blog

Let’s create SCCM SQL Reports with Approvers Email Address and Require Approval Details for all the application deployments. These are the two SCCM custom reports that you will learn from this post.

Recently one requirement came where I was asked to create an SCCM Application Deployment report where the application deployments have the setting “Requires Approval” and the e-mail ID used in the “Approvers Email Address column” at the time of deployment.

There are two SQL custom reports for all the applications deployed as “Require Approval” and “Approvers Email Address” used at the time of the deployment creation process. These details are important to reduce the helpdesk calls.

I have seen many approvers ignore the automated email alerts, which might cause a delay in the deployment of the applications if you are using require approvers and email approvers. This delay could cause helpdesk tickets and frustrated end-users.

The SCCM custom reports explained in this post help the helpdesk team to get the email approver details for end-users. The users can follow up with the approvers and get the SCCM application deployment to happen automatically.

Approve SCCM Application Requests via Email – Prerequisites

There are some prerequisites to get the approval of SCCM Application requests via email working. I will quickly mention three points that you need to take care of to get this SCCM deployment email approval feature working in your SCCM environment.

Turn on Require administrator approval if users request this application feature from \Administration\Overview\Updates and Servicing\Features.
Configure email notifications for alerts from \Monitoring\Overview\Alerts\Subscriptions.
Set up the administration service in SCCM.
Deploy the application to SCCM User Collection as Available deployment.

Approve SCCM Application Requests via Email – **prerequisites**

SCCM Application Deployment Email Approval

Let’s find out what exactly the SCCM Application Deployment Email Approval configuration is and where it is located in the console. It’s part of Deployment Settings in Deploy Software Wizard, and more details are available below.

It isn’t easy to imagine when explained in words, so give the screenshot for better understanding.

Navigate to \Software Library\Overview\Application Management\Applications.
Select the SCCM Application that you want to check manually.
Click on the Deployments tab as shown below.
Right-click on one of the Deployments and go to Properties.

SCCM SQL Reports with Require Approval and Approvers Email Details — SCCM SQL Reports with Require Approval and Approvers Email **Address** Details

On the Deployment properties window – go to the Deployment Settings tab and check whether An Administrator must approve a request for the application on the device option is enabled or not.

You can configure the same setting during the deployment creation process through Deploy Software Wizard, as shown below.

Application Deployment Settings Report

I was trying to find out Application Deployment Settings Reports. I raised it in our HTMD Forum Question. I have already answered the same question and included the SQL Query, but I am trying to explain it via this post.

To make it simple, I have separated the requirement into two parts.

SQL Report for all the applications deployed as “Require Approval.”
SQL Report for “Approvers Email Address” used at the time of deployment.

The first one was really simple because Anoop Nair already wrote one custom query to fetch all application deployments. https://www.anoopcnair.com/sccm-application-deployment-custom-report-sql-query-configmgr/ I just had to add “RequireApproval = 1” in the same query, and I was all set.

Moreover, there is an option in the SCCM console (Monitoring – Deployments) to get this data, but if you want to fetch the details via report, we will have to follow the blog from Anoop Nair.

Application Deployment with the setting "Requires Approval" — Application Deployment with the setting “Requires Approval” – SCCM SQL Reports with Approvers Email 2

The second one is really peculiar because I searched the entire DB to find out the table name and column name where this information is stored.

SQL Report for all the applications deployed as Require Approval

Let’s find out the SQL Report for all the applications deployed as Require Approval. I have used the same method (“RequireApproval = 1” condition) explained in the post linked above to get the details of all the applications deployed with require email approval settings.

Select
Pac.PackageID as 'App_ID',
col.CollectionID as 'AppCollection_ID',
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
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 Vaa.RequireApproval = 1
order by Ds.DeploymentTime desc

How to get all Tables & Columns Details from SCCM DB

Learn How to get all tables & columns Details from SCCM DB.

Select distinct table_name,COLUMN_NAME From Information_Schema.columns
where table_name LIKE ('v_%')
Order by table_name

The output of the SQL query looks like this.

How to get all Tables & Columns Details from SCCM DB

NOTE! – The Table and column names don’t have any information which indicates where the e-mail address will be stored, which was used at the time of deployment.

Application Deployment Action and Task Execution in the Backend

So let’s understand what happens when we select the option “An administrator must approve a request for this application on the device.”

And we have followed by selecting the above option; what happens if we specify the e-mail address under the “Approvers Email Address” box.

SCCM Creates a subscription for the “Approvers Email Address,” so whenever a user requests that particular application, the approver gets notified via “E-mail Alert.”

The person can also approve or reject the request by selecting the hyperlink given in the same e-mail.

The E-mail looks like the below:

Application Deployment Action and Task Execution in the Backend – SCCM SQL Reports with Approvers Email 4

SCCM SQL Report for Approvers Email Address used at the time of App Deployment

Let’s find out the SCCM SQL Report for Approvers Email used at App Deployment. This will be an interesting report because I’m going to join another table in the report shared above.

So after understanding this concept, the report creation for my second requirement, SQL Report for “Approvers Email Address” used at the time of deployment, became easier because I joined the subscription table into the same report we created for the first requirement “Application deployed as Require Approval.”

This is what the query looks like.

Select
Pac.PackageID as 'App_ID',
col.CollectionID as 'AppCollection_ID',
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
vaa.AssignmentName as 'AssignmentName',
EmailRecipients As 'EmailRecipients',
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',
EmailRecipients As 'EmailRecipients'
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
left join subscriptions s on replace(s.name,'Approve application via email - ','') = replace(AssignmentName,'_install','')
Where Ds.FeatureType = 1 and Vaa.RequireApproval = 1
order by Ds.DeploymentTime desc

To get the SCCM SQL Report for Approvers Email Address used at App Deployment, you can follow the steps explained below.

Open the SQL Server Management Studio.
Click on the New Query button.
Select the CM_MEM database from the drop-down menu.
- MEM is the ConfigMgr site code.

Copy the following SQL query to find the report of application deployment with collection details.
Click on the Execute button.

Conclusion – SCCM SQL Reports with Approvers Email and Require Approval Details of Application Deployments

Now we have a SQL report for all the applications deployed as “Requires Approval” and the “Approvers Email” address information to see who is authorized to approve this application request.

Conclusion - SCCM SQL Reports with Require Approval and Approvers Email Settings — Conclusion – SCCM SQL Reports with Require Approval and Approvers Email Settings

Disclaimer – The information provided on the site is for general informational purposes only. All information on the site is provided in good faith. However, we make no representation or warranty of any kind, express or implied, regarding the accuracy, adequacy, validity, reliability, availability, or completeness of any information on the website.

Author

My name is Deepak Rai, and I am a Technical Lead on SCCM and Intune with more than 14 years of experience in IT. My main domain is SCCM (AKA ConfigMgr, CB, MECM, etc.), Intune, and Azure (Runbooks). I have worked on several platforms (Active Directory, Exchange, Veritas NETBACKUP, Symantec Backup Exec, NDMP devices Like Netapp, EMC Data Domain, Quantum using Backup Exec 2010 and 2012, HP storage works 4048 MSL G3, Data Deduplication related troubleshooting.) in these 13 years but at last ended up to the technology from which I started as IT Engineer (SCCM).

SCCM SQL Reports with Approvers Email Address and Require Approval Details of Application Deployments