Package Server Ready Check

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: