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.
- SCCM Windows 11 Upgrade Readiness Report using SQL Query
- Export List of SCCM Global Conditions using SQL Query
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.
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.
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.
- Amazon Corretto Deployment Using SCCM | Step by Step Guide
- Deploy macOS LOB Apps using Intune MEM
- Windows 11 22H2 Group Policy Settings list Download
- BlueJeans Deployment Using SCCM MSI Deployment Guide
- New SCCM Hotfix KB15498768 NTLM Connection Fallback Update
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.
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.
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:
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.
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.
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).