December 23, 2010

SQL query to get packages which are advertised without updating DPs

SELECT dbo.v_Advertisement.AdvertisementID, dbo.v_Advertisement.AdvertisementName, dbo.v_Advertisement.CollectionID,

dbo.v_Advertisement.PresentTime, dbo.v_Advertisement.ExpirationTime, dbo.v_Advertisement.SourceSite, dbo.v_Package.PackageID,
dbo.v_Package.Name, dbo.v_Package.Version, dbo.v_Package.SourceVersion, dbo.v_Package.SourceDate, dbo.v_Package.LastRefreshTime,
dbo.v_DistributionPoint.ServerNALPath, dbo.v_DistributionPoint.SiteCode, dbo.v_DistributionPoint.SiteName,
dbo.v_DistributionPoint.LastRefreshTime AS Expr1, dbo.v_DistributionPoint.Status, dbo.v_PackageStatusDistPointsSumm.ServerNALPath AS Expr2,
dbo.v_PackageStatusDistPointsSumm.SourceVersion AS Expr3, dbo.v_PackageStatusDistPointsSumm.SiteCode AS Expr4,
dbo.v_PackageStatusDistPointsSumm.State, dbo.v_PackageStatusDistPointsSumm.LastCopied, dbo.v_PackageStatusDistPointsSumm.SummaryDate,
dbo.v_PackageStatusDistPointsSumm.InstallStatus
FROM dbo.v_Advertisement INNER JOIN
dbo.v_Package ON dbo.v_Advertisement.PackageID = dbo.v_Package.PackageID INNER JOIN
dbo.v_DistributionPoint ON dbo.v_Package.PackageID = dbo.v_DistributionPoint.PackageID INNER JOIN
dbo.v_PackageStatusDistPointsSumm ON dbo.v_DistributionPoint.PackageID = dbo.v_PackageStatusDistPointsSumm.PackageID
WHERE dbo.v_PackageStatusDistPointsSumm.state=1

Monitor your packagers ;)

No comments:

Post a Comment