SCCM CB Monitoring | Deployment Reporting using SQL

I have been look for a way to report deployment status, without using Monitoring | Deployment in the console.

The trick was to monitor SMSPROV.LOG while I activated the monitoring  | deployment in the console. After some digging around in the log, I found the select statement below.

I have modified it a bit and added some comments.

select  all SMS_DeploymentSummary.ApplicationName,

SMS_DeploymentSummary.AssignmentID,

SMS_DeploymentSummary.CI_ID,

SMS_DeploymentSummary.CollectionID,

SMS_DeploymentSummary.CollectionName,

SMS_DeploymentSummary.CreationTime,

SMS_DeploymentSummary.DeploymentID,

SMS_DeploymentSummary.DeploymentIntent,

SMS_DeploymentSummary.DeploymentTime,

SMS_DeploymentSummary.DesiredConfigType,

SMS_DeploymentSummary.EnforcementDeadline,

SMS_DeploymentSummary.FeatureType,

SMS_DeploymentSummary.ModelName,

SMS_DeploymentSummary.ModificationTime,

SMS_DeploymentSummary.NumberErrors,

SMS_DeploymentSummary.NumberInProgress,

SMS_DeploymentSummary.NumberOther,

SMS_DeploymentSummary.NumberSuccess,

SMS_DeploymentSummary.NumberTargeted,

SMS_DeploymentSummary.NumberUnknown,

SMS_DeploymentSummary.ObjectTypeID,

SMS_DeploymentSummary.PackageID,

SMS_DeploymentSummary.PolicyModelID,

SMS_DeploymentSummary.ProgramName,

SMS_DeploymentSummary.SecuredObjectId,

SMS_DeploymentSummary.SoftwareName,

SMS_DeploymentSummary.SummarizationTime,

SMS_DeploymentSummary.SummaryType

from fn_DeploymentSummary(1033) AS SMS_DeploymentSummary

-- Added featuretype 5 to include softwareupdate.

-- see the full list below.

where (SMS_DeploymentSummary.FeatureType in (1,2,3,5)

AND SMS_DeploymentSummary.CollectionName <> N'')

-- only report on deployments where target is bigger than 0

AND NumberTargeted NOT LIKE 0

-- get the deployments from the last 30 days.

-- change 30 to 60 to report the status for the last 60 days.

AND DATEDIFF(day,DeploymentTime,getdate()) between 0 and 30

  

/*

link to the SMS_DeploymentSummary class info page

https://docs.microsoft.com/en-us/sccm/develop/reference/apps/sms_deploymentsummary-server-wmi-class

SMS_DeploymentSummary.FeatureType options:

1           Application

2           Program

3           MobileProgram

4           Script

5           SoftwareUpdate

6           Baseline

7           TaskSequence

8           ContentDistribution

9           DistributionPointGroup

10          DistributionPointHealth

11          ConfigurationPolicy

28          AbstractConfigurationItem

*/

Next fun part could be:

• Calculate the deployment success rates and color them green, yellow or red depending on some KPI agreements.
• Get data from several SCCM databases and consolidate a deployment report.

Will return with an update, when I have some interesting to show..