All Software updates required deployments status within 30 days 2

All Software updates available deployments status within 30 days 2

All SCCM Server Software Update Sync status 3

All Software updates required deployments status within 5 days 3

All Software updates deployments status within 30 days 4

All Software updates deployments status within 30 days 4

All Deployments status for Specific Software Update Group 5

Deployment Detailed status for specific software Update deployment 6

Specific Software Update Deployment Failed Errors with Description 7

Deployment status for specific software Update deployments 8

All Software Updates Last Month deployments SLA and KPI Data status 8

Software Update Group Created, Modified or Deleted Properties 10

Software Update last month patch compliance report using Compliance Settings 11

All Software updates deployments status 11

All Software updates required deployments status within 30 days

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 Ds.DeploymentIntent = 1

and Vaa.LastModificationTime > GETDATE()-@PatchDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software updates available deployments status within 30 days

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 Ds.DeploymentIntent = 2

and Vaa.LastModificationTime > GETDATE()-@PatchDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All SCCM Server Software Update Sync status

SELECT US.SiteCode, S.ServerName, S.SiteName, US.ContentVersion, US.SyncTime, US.LastSyncState, US.LastSyncStateTime, US.LastErrorCode

FROM update_syncstatus US, v_Site S where US.SiteCode=S.SiteCode

ORDER BY SyncTime

All Software updates required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer

Set @CurrentDeploymentsReportNeededDays = 500 –Specify the Days

Select

CONVERT(VARCHAR(11),GETDATE(),106) as ‘Date’,

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

Vaa.AssignmentName as ‘DeploymentName’,

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.EnforcementDeadline, GetDate()) as ‘ReqDays’

from v_DeploymentSummary Ds

left join v_CIAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID

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

and DateDiff(D,Ds.EnforcementDeadline, GetDate()) between 0 and @CurrentDeploymentsReportNeededDays

and Ds.NumberTotal > 0

order by Ds.EnforcementDeadline desc

All Software updates deployments status within 30 days

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

order by Ds.DeploymentTime desc

All Software updates deployments status within 30 days

Declare @PatchDeploymentsReportNeededDays as integer

Set @PatchDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AssignmentName as ‘DeploymentName’,

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

order by Ds.DeploymentTime desc

All Deployments status for Specific Software Update Group

Declare @SoftwareUpdateGroupName as varchar(255)

Set @SoftwareUpdateGroupName = ‘All Updates SRWP2’ –Specify Software Update Group Name

Select

Vaa.AssignmentName as ‘DeploymentName’,

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

Li.Title as ‘SUGroupName’,

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

left join v_AuthListInfo LI on LI.ModelID = Ds.ModelID

Where Ds.FeatureType = 5

and Li.Title like @SoftwareUpdateGroupName

order by Ds.DeploymentTime desc

Deployment Detailed status for specific software Update deployment

Declare @DeploymentName as Varchar(255)

Set @DeploymentName = ‘SU WKS-All Production Computers’ — Specify Software update deployment name

select

vrs.Name0,

vrs.Active0,

vrs.AD_Site_Name0,

vrs.User_Name0,

vrs.Operating_System_Name_and0,

a.Assignment_UniqueID as DeploymentID,

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

wsus.LastErrorCode as ‘LasErrorCode’,

wsus.LastScanTime as ‘LastWSUSScan’,

DateDiff(D, wsus.LastScanTime, GetDate()) as ‘LastWSUSScan Age’,

wks.LastHWScan,

DateDiff(D, wks.LastHwScan, GetDate()) as ‘LastHWScan Age’

from v_CIAssignment a

join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID

join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

join v_R_System vrs on vrs.ResourceID = assc.ResourceID

join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID

join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID

where a.AssignmentName = @DeploymentName

and assc.StateType in (300,301)

order by 11 desc

Specific Software Update Deployment Failed Errors with Description

Declare @DeploymentName as Varchar(255)

Set @DeploymentName = ‘SU WKS-All Production Computers’

Select

Vrs.Name0 as ‘MachineName’,

Vrs.User_Name0 as ‘UserName’,

assc.LastEnforcementMessageTime as ‘LastEnforcementTime’,

assc.LastEnforcementErrorID&0x0000FFFF as ‘ErrorStatusID’,

isnull(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(8),CONVERT(int,assc.LastEnforcementErrorCode))), 0) as ‘ErrorCode’,

assc.LastEnforcementErrorCode as ‘ErrorCodeInt’,

Asi.MessageName as ‘MessageName’

from V_CIAssignment cia

join V_UpdateAssignmentStatus_Live assc on assc.AssignmentID = cia.AssignmentID

inner join v_AssignmentState_Combined Ac on Ac.ResourceID = assc.ResourceID

inner join V_AdvertisementStatusInformation Asi on Asi.MessageID = Ac.LastStatusMessageID

and isnull(assc.IsCompliant, 0) = 0

and assc.LastEnforcementMessageID in (6,9)

and assc.LastEnforcementErrorCode Not in (0)

join v_R_System Vrs on assc.ResourceID = Vrs.ResourceID and isnull (Vrs.Obsolete0,0) = 0

where cia.AssignmentName = @DeploymentName

Deployment status for specific software Update deployments

select

vrs.Name0,

vrs.Active0,

vrs.AD_Site_Name0,

vrs.User_Name0,

vrs.Operating_System_Name_and0,

a.Assignment_UniqueID as DeploymentID,

a.AssignmentName as DeploymentName,

a.StartTime as Available,

a.EnforcementDeadline as Deadline,

sn.StateName as LastEnforcementState,

wsus.LastErrorCode as ‘LasErrorCode’,

wsus.LastScanTime as ‘LastWSUSScan’,

DateDiff(D, wsus.LastScanTime, GetDate()) as ‘LastWSUSScan Age’,

wks.LastHWScan,

DateDiff(D, wks.LastHwScan, GetDate()) as ‘LastHWScan Age’

from v_CIAssignment a

join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID

join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)

join v_R_System vrs on vrs.ResourceID = assc.ResourceID

join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID

join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID

where a.AssignmentName in (‘UpdateGroupDeploymentName1’, ‘ UpdateGroupDeploymentName 2’,

‘ UpdateGroupDeploymentName 3’)

and assc.StateType in (300,301)

order by 11 desc

All Software Updates Last Month deployments SLA and KPI Data status

Declare @PatchDeploymentsReportNeededMonths as integer

Set @PatchDeploymentsReportNeededMonths = 1 –Specify the no of Months

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 (DATEDIFF(m,Vaa.CreationTime, GETDATE()) = @PatchDeploymentsReportNeededMonths)

order by Ds.DeploymentTime desc

Software Update Group Created, Modified or Deleted Properties

Select

rsm.Severity,

rsm.MessageTypeString as ‘Type’,

rsm.SiteCode,

rsm.Timestamp as ‘Date/Time’,

rsm.System,

rsm.Component,

rsm.MessageID,

‘User « ‘ + rsm.InsStrValue1 + ‘ »‘ +

CASE

when rsm.MessageID = 30196 Then ‘ created updates assignment ‘

when rsm.MessageID = 30197 Then ‘ modified updates assignment ‘

when rsm.MessageID = 30198 Then ‘ deleted updates assignment ‘

when rsm.MessageID = 30219 Then ‘ created authorization list ‘

when rsm.MessageID = 30220 Then ‘ modified authorization list ‘

when rsm.MessageID = 30221 Then ‘ deleted authorization list ‘

End

+ rsm.InsStrValue2 + ‘ ‘ + rsm.InsStrValue3 + ‘ ‘ + rsm.InsStrValue4 as ‘Description’,

cia.CollectionID,

cia.CollectionName

from v_Report_StatusMessageDetail rsm

left join v_CIAssignment cia on rsm.InsStrValue2 = cia.AssignmentID

where rsm.MessageID >= 30196 and rsm.MessageID <= 30198

or rsm.MessageID >= 30218 and rsm.MessageID <= 30221

order by 4 desc

Software Update last month patch compliance report using Compliance Settings

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0)

SET @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))

SELECT

dbo.v_R_System.ResourceID,

dbo.v_R_System.Name0 AS [Name],

dbo.v_CICurrentComplianceStatus.ComplianceState,

v_LocalizedCIProperties_SiteLoc.DisplayName AS [BaselineName],

CAST(dbo.v_LocalizedCIProperties_SiteLoc.Description AS DATETIME) AS [BaselineDate],

SUMMARY.LastActiveTime,

OP.LastBootUpTime0 AS [LastBootUpTime],

DATEDIFF(Day, OP.LastBootUpTime0, GETDATE()) as [DaysSinceReboot]

FROM

dbo.v_BaselineTargetedComputers

INNER JOIN dbo.v_R_System ON v_R_System.ResourceID = dbo.v_BaselineTargetedComputers.ResourceID

INNER JOIN dbo.v_ConfigurationItems ON dbo.v_ConfigurationItems.CI_ID = v_BaselineTargetedComputers.CI_ID

INNER JOIN dbo.v_CICurrentComplianceStatus ON dbo.v_CICurrentComplianceStatus.CI_ID = dbo.v_ConfigurationItems.CI_ID AND

dbo.v_CICurrentComplianceStatus.ResourceID = dbo.v_BaselineTargetedComputers.ResourceID

INNER JOIN dbo.v_LocalizedCIProperties_SiteLoc ON dbo.v_LocalizedCIProperties_SiteLoc.CI_ID = dbo.v_ConfigurationItems.CI_ID

INNER JOIN dbo.v_CH_ClientSummary SUMMARY ON dbo.v_R_System.ResourceID = SUMMARY.ResourceID

LEFT JOIN dbo.v_GS_OPERATING_SYSTEM OP ON dbo.v_R_System.ResourceID = OP.ResourceID

WHERE

dbo.v_LocalizedCIProperties_SiteLoc.DisplayName LIKE ‘Windows Updates %’

and dbo.v_LocalizedCIProperties_SiteLoc.DisplayName LIKE ‘Windows Updates April%’

AND CAST(dbo.v_LocalizedCIProperties_SiteLoc.Description AS DATETIME) BETWEEN @StartDate AND @EndDate

ORDER BY dbo.v_R_System.Name0

All Software updates deployments status

Select

Vaa.AssignmentName as ‘DeploymentName’,

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

order by Ds.DeploymentTime desc