Package Server Ready Check

Updated: 20 May 2009 | 7 comments

+7 7 Votes

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" :

imagebrowser image

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:

imagebrowser image

article Filed Under:

Endpoint Management, Symantec Management Platform (Notification Server), Basics, Reporting, Tip/How to

Group Ownership:

Denver Endpoint Management User Group

Login or register to post comments
Comments RSS Feed

Comments

May

2009

1 Vote +1

lotsill

Great query. This report can

Great query. This report can come in handy so the software team ensures the package is distributed across the enterprise.

On a side note, I modified the Global Parameter so it queried the packages and provided a drop down.
Here is the image and the query if anyone wants it.

Select Distinct
ps.[Name]
From SWDPackage ps

Steve Petrasek

May

2009

1 Vote +1

Mike.Langford

Great Idea!

Hey, That is actually a really good idea. I have made that change in our environment as well. It has always been a pain going back and forth between the tasks tab and the reports tab to make sure I get the name right. Now it is automatic.

Thanks again!

Nov

2009

0 Votes 0

Sid Markolov

Very Helpful!

I have been trying to come up with something like this for some time now.

Nov

2009

0 Votes 0

Mike.Langford

Glad it worked for you

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

Nov

2009

0 Votes 0

KSchroeder

Trusted Advisor

Nice job Mike!

Nice job Mike, very useful report!

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

Dec

2009

0 Votes 0

cnimmer

A+

Running it here...indispensible!

Dec

2009

0 Votes 0

KSchroeder

Trusted Advisor

This small modification will

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

Thanks,
Kyle
Symantec Trusted Advisor

For Forum threads, please click "Mark as Solution" if answered.
For all content, please give a thumbs up if you agree with or support the post.

Package Server Ready Check

Comments

Great query. This report can

Great Idea!

Very Helpful!

Glad it worked for you

Nice job Mike!

A+

This small modification will

Would you like to reply?

Links

Technical Support

Symantec.com

Store

Community Stats