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.
If the screenshot not clear then use link https://snipboard.io/7t6IxY.jpg
Answers ( 3 )
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.
Ok Sir. Thanks for the guidance. Will come back with results.
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%’