SELECT CollectionID, OfferID, PolicyModelID, ModelID, AssignmentID, CollectionName, CollectionType, SoftwareName, DeploymentTime, CreationTime, ModificationTime, FeatureType, SummaryType, DeploymentIntent, EnforcementDeadline,
NumberSuccess, NumberInProgress, NumberUnknown, NumberErrors, NumberOther, NumberTotal, SummarizationTime, ProgramName, PackageID, Enabled
FROM dbo.v_DeploymentSummary
WHERE (CollectionName = N'Users01')
.All Software applications available deployments status within 30 days
Declare @SoftwareAppDeploymentsReportNeededDays as integer
Set @SoftwareAppDeploymentsReportNeededDays = 30 --Specify the Days
Select
Vaa.AssignmentName as 'DeploymentName',
Right(Ds.CollectionName,3) as 'Stage',
Vaa.ApplicationName as 'ApplicationName',
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 'ReqNotMet',
Ds.NumberUnknown as 'Unknown',
case when (Ds.NumberTotal = 0) or (Ds.NumberTotal is null) Then '100' Else (round( (Ds.NumberSuccess +
Ds.NumberOther) / convert (float,Ds.NumberTotal)*100,2)) End as 'Success%',
DateDiff(D,Ds.DeploymentTime, GetDate()) as 'AvailableDays',
DateDiff(D,Ds.EnforcementDeadline, GetDate()) as 'RequiredDays'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 1 and Ds.DeploymentIntent = 2
and Ds.CreationTime > GETDATE()-@SoftwareAppDeploymentsReportNeededDays
order by Ds.DeploymentTime desc