All OSD required deployments status within 30 days 1

All OSD available deployments status within 30 days 1

All Operating systems deployments status 2

All Deployments status for Specific Task Sequence 3

All OSD required deployments status within 30 days

Declare @SoftwareOSDeploymentsReportNeededDays as integer

Set @SoftwareOSDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AdvertisementName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Ds.SoftwareName as ‘TaskSequenceName’,

Ds.ProgramName 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

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

Where Ds.FeatureType = 7 and Ds.DeploymentIntent = 1

and Ds.ModificationTime > GETDATE()-@SoftwareOSDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All OSD available deployments status within 30 days

Declare @SoftwareOSDeploymentsReportNeededDays as integer

Set @SoftwareOSDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AdvertisementName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Ds.SoftwareName as ‘TaskSequenceName’,

Ds.ProgramName 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

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

Where Ds.FeatureType = 7 and Ds.DeploymentIntent = 2

and Ds.ModificationTime > GETDATE()-@SoftwareOSDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Operating systems deployments status

Select

Vaa.AdvertisementName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Ds.SoftwareName as ‘TaskSequenceName’,

Ds.ProgramName ‘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’,

DateDiff(D,Ds.ModificationTime, GetDate()) as ‘CreatedDays’,

Ds.CreationTime as ‘CreationTime’,

Ds.ModificationTime as ‘LastModifiedTime’,

‘Administrator’ as ‘LastModifiedBy’

from v_DeploymentSummary Ds

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

Where Ds.FeatureType = 7

order by Ds.DeploymentTime desc

All Deployments status for Specific Task Sequence

Declare @TaskSequenceName as varchar(255)

Set @TaskSequenceName = ‘Windows 7 x64-CoreImage’ –Specify TaskSequenceName

Select

Vaa.AdvertisementName as ‘DeploymentName’,

Right(Ds.CollectionName,3) as ‘Stage’,

Ds.SoftwareName as ‘TaskSequenceName’,

Ds.ProgramName ‘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’,

DateDiff(D,Ds.ModificationTime, GetDate()) as ‘CreatedDays’,

Ds.CreationTime as ‘CreationTime’,

Ds.ModificationTime as ‘LastModifiedTime’,

‘Administrator’ as ‘LastModifiedBy’

from v_DeploymentSummary Ds

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

Where Ds.FeatureType = 7

and Ds.SoftwareName = @TaskSequenceName

order by Ds.DeploymentTime desc