All Packages available in SCCM 2

Package Deployment Detailed status for specific Advertisement ID 2

All Packages Total Targeted DP Counts 2

All Packages Compare targeted Packages on Two DPS 4

Compare Packages with two DP’S 4

All Software Distribution Packages without Advertisements 5

All Packages which are waiting to distribute content to DPs 5

All Packages Content Distribution Status 6

All Software packages required deployments status within 30 days 6

All Software packages available deployments status within 30 days 7

All Software packages required deployments status within 5 days 8

All Software packages deployments status within 30 days 9

All Deployments status for Specific Package 10

Deployment Detailed status for specific package with specific collection 10

Deployment Detailed status for specific package 11

Deployments status for specific packages 11

Compare Two DP’s Packages Status 12

Compare packages with Another DP 12

All Software packages deployments status 12

All Packages available in SCCM

SELECT DISTINCT p.PackageID, p.Name,p.Version,p.Language,p.Manufacturer,

PackageType = Case Packagetype

When 0 Then ‘Software Distribution Package’

When 3 Then ‘Driver Package’

When 4 Then ‘Task Sequence Package’

When 5 Then ‘Software Update Package’

When 6 Then ‘Device Settings Package’

When 7 Then ‘Virtual Package’

When 8 Then ‘Software Distribution Application’

When 257 Then ‘Image Package’

When 258 Then ‘Boot Image Package’

When 259 Then ‘OS Install Package’

End,

p.PkgSourcePath,p.SourceDate,p.LastRefreshTime ,p.SourceVersion,

p.SourceSite,n.Targeted,n.Installed,n.Retrying,n.Failed,(n.SourceSize/1024)as ‘SourceSize(MB)’,

(n.SourceCompressedSize/1024) as ‘SourceCompressedSize(MB)’

FROM v_Package p LEFT JOIN v_PackageStatusRootSummarizer n

ON p.PackageID = n.PackageID

WHERE p.PackageType <> ‘4’ and p.PackageType <> ‘258’

order by 1 desc

Package Deployment Detailed status for specific Advertisement ID

Declare @AdvID varchar (255)

set @AdvID = ‘00020001’ –Advertisement ID

Select

vca.AdvertisementID,

vc.Name as ‘CollectionName’,

vrs.Name0,

vrs.User_Name0,

vrs.AD_Site_Name0,

vca.LastAcceptanceStateName,

vca.LastAcceptanceStatusTime,

vca.LastStateName,

vca.LastStatusMessageIDName,

vca.LastExecutionResult

from v_Advertisement as Va

inner join v_ClientAdvertisementStatus as Vca on va.AdvertisementID=vca.AdvertisementID

inner join v_R_System as Vrs on vca.ResourceID=vrs.ResourceID

inner join v_Collection as Vc on va.CollectionID=vc.CollectionID

Where va.AdvertisementID = @AdvID

order by vca.LastStateName

All Packages Total Targeted DP Counts

Select

vp.PackageID,

vp.Name,

Case vp.PackageType

When 0 Then ‘Package’

When 3 Then ‘Driver’

When 4 Then ‘Task Sequence’

When 5 Then ‘software Update’

When 7 Then ‘Virtual’

When 8 Then ‘Application’

When 257 Then ‘Image’

When 258 Then ‘Boot Image’

When 259 Then ‘OS’

Else ‘ ‘

END AS ‘PackageType’,

vp.Manufacturer,

vp.Version,

vp.Language,

(pkgs.SourceSize/1024) as ‘Package Size (MB)’,

vp.PkgSourcePath,

vp.SourceVersion,

vp.SourceDate,

Pkgs.Targeted,

pkgs.Installed,

pkgs.Pending,

pkgs.Retrying,

pkgs.Failed,

pkgs.Unknown

from

vPkgStatusSummary as Pkgs

Inner Join v_Package as Vp on pkgs.PkgID=vp.PackageID

order by 3

All Packages Compare targeted Packages on Two DPS

select s.SiteCode,s.PackageID,p.Name,(p.sourcesize)/1024 as ‘Size(MB)’,

s.sourceversion as ‘DPVersion’,p.storedpkgversion as ‘LastVersion’,s.Installstatus as ‘Package Status’,

Case v_Package.PackageType

When 0 Then ‘Package’

When 3 Then ‘Driver’

When 4 Then ‘Task Sequence’

When 5 Then ‘software Update’

When 7 Then ‘Virtual’

When 8 Then ‘Application’

When 257 Then ‘Image’

When 258 Then ‘Boot Image’

When 259 Then ‘OS’

Else ‘ ‘

END AS ‘Type’

from v_PackageStatusDistPointsSumm s

inner join smspackages p on s.packageid = p.pkgid

inner join v_Package on v_Package.PackageID=p.[PkgID]

where s.PackageID not in

(select PackageID from v_DistributionPoint where ServerNALPath like ‘%DPServerName1%’) and ServerNALPath like ‘%DPServerName1%’

order by 8

Compare Packages with two DP’S

select

Pkg.PackageID,

Pkg.Name,

Case Pkg.PackageType

When 0 Then ‘Package’

When 3 Then ‘Driver’

When 4 Then ‘TaskSequence’

When 5 Then ‘softwareUpdate’

When 7 Then ‘Virtual’

When 8 Then ‘Application’

When 257 Then ‘Image’

When 258 Then ‘BootImage’

When 259 Then ‘OS’

Else ‘ ‘

END AS ‘Type’

from v_Package Pkg

where Pkg.PackageID in (

select PackageID from v_DistributionPoint where ServerNALPath like ‘%Master DP Name%’

and PackageID not in

(select PackageID from v_DistributionPoint where ServerNALPath like ‘%Compare DP Name%’) )

order by 3

All Software Distribution Packages without Advertisements

select v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate

from dbo.v_package

Where

packageID not in (select PackageID from dbo.v_Advertisement)and

PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and

v_Package.name not like ‘%osd%’ and V_package.PackageType =’0′

group by v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate

order by v_Package.PackageID

All Packages which are waiting to distribute content to DPs

Select

SubString(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath)+2,(CHARINDEX(‘ »]’, dp.ServerNALPath) – CHARINDEX(‘\\’, dp.ServerNALPath))-3) as ServerName,

dp.SiteCode as ‘SiteCode’,

dp.PackageID as ‘PackageID’,

p.Name as ‘PackageName’,

P.SourceVersion as ‘SourceVersion’,

P.LastRefreshTime as ‘LastRefreshTime’,

stat.InstallStatus as ‘InstallStatus’

from v_DistributionPoint dp left join v_PackageStatusDistPointsSumm stat on

dp.ServerNALPath=stat.ServerNALPath and dp.PackageID=stat.PackageID

left join v_PackageStatus pstat on dp.ServerNALPath=pstat.PkgServer and

dp.PackageID=pstat.PackageID left outer join v_Package p on dp.packageid = p.packageid

where stat.InstallStatus not in (‘Package Installation complete’)

ORDER BY 1

All Packages Content Distribution Status

select v_SystemResourceList.ServerName as ‘ServerName’,

v_SystemResourceList.SiteCode,

(select count(*)from v_PackageStatusDistPointsSumm where servernalpath = nalpath) as ‘Targetted’,

(select count(*)from v_PackageStatusDistPointsSumm where installstatus =’Package Installation complete’and servernalpath = nalpath) as ‘Installed’,

(select count(*)from v_PackageStatusDistPointsSumm where (installstatus =’Content updating’ or installstatus =’Waiting to install package’ or installstatus =’Content monitoring’) and servernalpath = nalpath) as ‘Waiting’,

(select count(*)from v_PackageStatusDistPointsSumm where installstatus like’%Retry%’and servernalpath = nalpath) as ‘Retrying’,

(select count(*)from v_PackageStatusDistPointsSumm where installstatus =’Waiting to remove package’ and servernalpath = nalpath) as ‘Removing’,

(select count(*)from v_PackageStatusDistPointsSumm where installstatus like’%Fail%’ and servernalpath = nalpath) as ‘Failed’,

(select ROUND((100 * (select count(*)from v_PackageStatusDistPointsSumm where installstatus =’Package Installation complete’ and servernalpath = nalpath)/(select count(*)from v_PackageStatusDistPointsSumm where servernalpath = nalpath)),2)) as ‘Compliance %’

from v_SystemResourceList join v_PackageStatusDistPointsSumm

on v_SystemResourceList.nalpath = v_PackageStatusDistPointsSumm.servernalpath

where v_SystemResourceList.RoleName = ‘SMS Distribution Point’

group by v_SystemResourceList.SiteCode, v_SystemResourceList.servername, v_SystemResourceList.nalpath order by 1

All Software packages required deployments status within 30 days

Declare @SoftwarePkgDeploymentsReportNeededDays as integer

Set @SoftwarePkgDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AdvertisementName as ‘DeploymentName’,

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1)as ‘ApplicationName’,

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’

from v_DeploymentSummary Ds

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

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

and Ds.ModificationTime > GETDATE()-@SoftwarePkgDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software packages available deployments status within 30 days

Declare @SoftwarePkgDeploymentsReportNeededDays as integer

Set @SoftwarePkgDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AdvertisementName as ‘DeploymentName’,

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1)as ‘ApplicationName’,

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’

from v_DeploymentSummary Ds

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

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

and Ds.ModificationTime > GETDATE()-@SoftwarePkgDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Software packages required deployments status within 5 days

Declare @CurrentDeploymentsReportNeededDays as integer

Set @CurrentDeploymentsReportNeededDays = 5 –Specify the Days

Select

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

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1)as ‘ApplicationName’,

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 ‘AvailDays’

from v_DeploymentSummary Ds

join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID

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

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

and Ds.NumberTotal > 0

order by Ds.DeploymentTime desc

All Software packages deployments status within 30 days

Declare @PKGDeploymentsReportNeededDays as integer

Set @PKGDeploymentsReportNeededDays = 30 –Specify the Days

Select

Vaa.AdvertisementName as ‘DeploymentName’,

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1)as ‘ApplicationName’,

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 = 2

and Ds.ModificationTime > GETDATE()-@PKGDeploymentsReportNeededDays

order by Ds.DeploymentTime desc

All Deployments status for Specific Package

Declare @PackageName as Varchar(255)

Set @PackageName = ‘SAP Client GUI 7.30’ –Specify Package Name

Select

Vaa.AdvertisementName as ‘DeploymentName’,

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1) as ‘PackageName’,

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 = 2

and Ds.SoftwareName like @PackageName + ‘%’

order by Ds.DeploymentTime desc

Deployment Detailed status for specific package with specific collection

Declare @PackageName as Varchar(255)

Declare @CollectionName as Varchar(255)

Set @PackageName = ‘Lync 2013’ — Specify Application name

Set @CollectionName = ‘%Lync%’ — Specify Collection name

select distinct

pack.Name as ‘Package Name’,

COLL.CollectionName as ‘Target Collection’,

adv.ProgramName as ‘DeploymentTypeName’,

Vrs.Name0 as ‘ComputerName’,

Vrs.AD_Site_Name0 as ‘ADSiteName’,

ADV.AdvertisementID as ‘AdvertisementID’,

ADVS.LastStateName as ‘Status’,

ADVS.LastStatusTime as ‘Status Time’

from v_R_System Vrs

INNER JOIN vSMS_ClientAdvertisementStatus ADVS ON Vrs.ResourceID = ADVS.ResourceID

INNER JOIN v_Advertisement ADV ON ADV.AdvertisementID = ADVS.AdvertisementID

INNER JOIN v_FullCollectionMembership CM ON Vrs.ResourceID = CM.ResourceID

LEFT JOIN v_Package Pack on adv.PackageID = pack.PackageID

LEFT JOIN v_Collections COLL ON ADV.CollectionID = COLL.SiteID

where pack.Name = @PackageName

and COLL.CollectionName like @CollectionName

Order by Vrs.Name0

Deployment Detailed status for specific package

Declare @PackageName as Varchar(255)

Set @PackageName = ‘Lync 2013’ — Specify Application name

select distinct

pack.Name as ‘Package Name’,

COLL.CollectionName as ‘Target Collection’,

adv.ProgramName as ‘DeploymentTypeName’,

Vrs.Name0 as ‘ComputerName’,

Vrs.AD_Site_Name0 as ‘ADSiteName’,

ADV.AdvertisementID as ‘AdvertisementID’,

ADVS.LastStateName as ‘Status’,

ADVS.LastStatusTime as ‘Status Time’

from v_R_System Vrs

INNER JOIN vSMS_ClientAdvertisementStatus ADVS ON Vrs.ResourceID = ADVS.ResourceID

INNER JOIN v_Advertisement ADV ON ADV.AdvertisementID = ADVS.AdvertisementID

INNER JOIN v_FullCollectionMembership CM ON Vrs.ResourceID = CM.ResourceID

LEFT JOIN v_Package Pack on adv.PackageID = pack.PackageID

LEFT JOIN v_Collections COLL ON ADV.CollectionID = COLL.SiteID

where pack.Name = @PackageName

Order by Vrs.Name0

Deployments status for specific packages

select distinct

pack.Name as ‘Package Name’,

COLL.CollectionName as ‘Target Collection’,

adv.ProgramName as ‘DeploymentTypeName’,

Vrs.Name0 as ‘ComputerName’,

Vrs.AD_Site_Name0 as ‘ADSiteName’,

ADV.AdvertisementID as ‘AdvertisementID’,

ADVS.LastStateName as ‘Status’,

ADVS.LastStatusTime as ‘Status Time’

from v_R_System Vrs

INNER JOIN vSMS_ClientAdvertisementStatus ADVS ON Vrs.ResourceID = ADVS.ResourceID

INNER JOIN v_Advertisement ADV ON ADV.AdvertisementID = ADVS.AdvertisementID

INNER JOIN v_FullCollectionMembership CM ON Vrs.ResourceID = CM.ResourceID

LEFT JOIN v_Package Pack on adv.PackageID = pack.PackageID

LEFT JOIN v_Collections COLL ON ADV.CollectionID = COLL.SiteID

where pack.Name in (‘PackageName1’, ‘PackageName2’, ‘PackageName3’)

Order by Vrs.Name0

Compare Two DP’s Packages Status

Select

Pkg.PackageID,Pkg.Name

from v_Package Pkg

where Pkg.PackageID in (

select PackageID from v_DistributionPoint where ServerNALPath like ‘%DPName1%’

and PackageID not in (select PackageID from v_DistributionPoint where ServerNALPath like ‘% DPName2%’) )

Compare packages with Another DP

select

Pkg.PackageID,

Pkg.Name,

Case Pkg.PackageType

When 0 Then ‘Package’

When 3 Then ‘Driver’

When 4 Then ‘TaskSequence’

When 5 Then ‘softwareUpdate’

When 7 Then ‘Virtual’

When 8 Then ‘Application’

When 257 Then ‘Image’

When 258 Then ‘BootImage’

When 259 Then ‘OS’

Else ‘ ‘

END AS ‘Type’

from v_Package Pkg

where Pkg.PackageID in (

select PackageID from v_DistributionPoint where ServerNALPath like ‘%Master DP%’

and PackageID not in

(select PackageID from v_DistributionPoint where ServerNALPath like ‘%Compare DP%’) )

order by 3

All Software packages deployments status

Select

Vaa.AdvertisementName as ‘DeploymentName’,

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

Left(Ds.SoftwareName, CharIndex(‘(‘,(Ds.SoftwareName))-1)as ‘ApplicationName’,

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 = 2

order by Ds.DeploymentTime desc