I thought I would share a little SQL report that has come in handy in our organization. We have around 20 package servers and there are occasions, especially in troubleshooting push related issues when it is nice to know which package servers have received a particular package, which are pending, and which are not scheduled to receive it at all. To do this, lets create a new report and create a parameter called "PACKAGE" : Now enter the following as the SQL for the report:
Select Distinct psvr.[Server], isnull(svr.[PackageName],%PACKAGE%)'PackageName', isnull(svr.[Status],'Not Scheduled')'Status', svr.[PackageId] From v_packagestatus psvr Left Join ( Select Distinct s.[Server], ps.[Name]'PackageName', svr.[Status], svr.[PackageId], svr.[PkgSvrId] From v_packagestatus s INNER JOIN item i On i.[Name]=s.[Server] LEFT JOIN SWDPackageServer svr On i.[Guid]=svr.[PkgSvrId] Left Join SWDPackage ps On ps.[PackageId]=svr.[PackageId] Where ps.[Name]like'%'+%PACKAGE%+'%' )svr On psvr.[Server]=svr.[Server] Order By psvr.[Server]
Now when you run the report, you can give it a package name and you get a report of servers and their status:
This small modification will list all packages not "Ready". It has a drop-down parameter (or text box) for the Package Server name named "Package Server" (creative huh). If you set them both to %, then you'll see all packages not ready on all PSes.
SELECT DISTINCT vc.Name [Package Server], p.Name [Package Name], s.Status [Status], p.PackageId [PackageID] FROM SWDPackage p JOIN SWDPackageServer s ON p.PackageId = s.PackageId JOIN vComputer vc on vc.Guid = s.PkgSvrId WHERE vc.Name LIKE '%Package Server%' AND p.Name LIKE '%' + '%PACKAGE%' + '%' AND vc.Guid = s.PkgSvrId AND s.Status <> 'Ready' ORDER BY p.Name, p.PackageId
It seems to me like something general enough to be a canned report, but in my searches I never found one that served this function. Have good one. Mike