Video Screencast Help
New Company Name and Logo Announced. Learn More.

Collection of PS's with a specific Invalid Package Status.

Created: 16 Jul 2012 | 1 comment

Good afternoon, (This question pertains to CMS 6.x)

Looking for a gifted SQL resource to help me tie this together. 

I want to create a collection that is going to just give me a list of PS’s that are going to give me an Invalid Package status for a given package. This will be used with Task Server to target machines for a Package Refresh job we have. Anyway the basic query below is where I am starting from. I will add parameters to it so we can use it inside the console for various packages. Here is my question. In the SWDPackageServer table is the PkgSvrId column with corresponding GUID. What I am confused about is how I would convert that PkgSvrId to a ResourceGuid that can be recognized in a collection. Does anyone know what I would join onto to link and have it return a list of ResourceGuids that will build my collection?

Here is the basic query I am starting from.

Select DISTINCT PkgSvrId

FROM dbo.SWDPackageServer

WHERE PackageId like '7B2E9293-7688-4C18-80C3-0001E33C69D7' and Status = 'Invalid Package'

I have to think this is pretty straight forward but I am stuck. Any input woul be appreciated.

Thank you


Comments 1 CommentJump to latest comment

mclemson's picture

PkgSvrId should be a valid GUID for building filters or use in automation policies.  The following query would join it to vComputer to provide the server name, proving that the GUID is valid. (Or not, since I don't have access to a test DB at the moment.)

SELECT vc.Name,ps.PkgSvrId
FROM vComputer vc
JOIN SWDPackageServer ps ON ps.PkgSvrId=vc.Guid
WHERE ps.PackageId like '7B2E9293-7688-4C18-80C3-0001E33C69D7' and ps.Status = 'Invalid Package'

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner