Sccm Deployments Compliance Reports (Sql Query)

Sccm tarafından yapılan software update dağıtımlarına ait compliance raporları, hatırlatma ve takip amacıyla aşağıdaki query ile kendinize bilgilendirme mailleri gönderebilirsiniz.

Mail Örneği;

Declare @PatchDeploymentsReportNeededDays as integer
Set @PatchDeploymentsReportNeededDays = 30 --Specify the Days
Select
Vaa.AssignmentName as 'DeploymentName',
Right(Ds.CollectionName,3) as 'Stage',
'Software Update' as 'PackageName',
CASE when Vaa.DesiredConfigType = 1 Then 'Install' when vaa.DesiredConfigType = 2 Then 'Uninstall' Else
'Others' End as 'DepType',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as
'Purpose',
Ds.DeploymentTime as 'AvailableTime',
Ds.EnforcementDeadline as 'RequiredTime',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'Others',
Ds.NumberUnknown as 'Unknown',
case when (Ds.NumberSuccess = 0) or (Ds.NumberSuccess is null) Then '0' Else (round(Ds.NumberSuccess/
convert (float,Ds.NumberTotal)*100,2)) End as 'Success%',
DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays',
DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays',
DateDiff(D,Ds.CreationTime, GetDate()) as 'CreatedDays',
Vaa.CreationTime as 'CreationTime',
Vaa.LastModificationTime as 'LastModifiedTime',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 5
and Vaa.LastModificationTime > GETDATE()-@PatchDeploymentsReportNeededDays
and Vaa.CreationTime > '2022-01-01 00:00:00.000'
order by Ds.DeploymentTime desc