Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Query to dump package server name for subnet

Created: 21 Apr 2010 • Updated: 27 May 2010 | 1 comment
KSchroeder's picture
This issue has been solved. See solution.

Hello all, After beating my head against the wall for some time now, I have decided to ask for help! Basically I need a report/query that will give me two columns:

PackageServerName | Subnet
SITE1PS01         | 10.99.90.0
SITE2PS01         | 10.94.49.0

This seems like it shouldn't be all that hard, but I'm just having all sorts of troubles. I'm trying to work with the vSubnet, vSiteSubnetMap, PackageServerServingSite, PackageServerServingSubnet, etc, but I can't get it to all add up. I'm fearing that I need to use a cursor (oh how I hate cursors!) Anyone have a bright idea? This should only return subnets which are mapped to a particular site, which is associated with a Package server. There are a few Stored Procedures which are close as well, but still not getting me what I need. The following almost works...but the PackageServerServingSubnet table seems to only have the explicitly matched results (i.e. the subnet that the PS itself is on):

	select vc.Name, sub.[Subnet]
FROM vSubnet sub
JOIN PackageServerServingSubnet as pss on pss.[SubnetGuid] = sub.Guid
JOIN vComputer vc ON pss.[PackageServerGuid] = vc.Guid
ORDER BY vc.Name

Comments 1 CommentJump to latest comment

KSchroeder's picture

In case anyone else might benefit from this in the future...

SELECT vc.Name, vs.Subnet, vs.[Subnet Mask]
FROM vSiteSubnetMap vssm
JOIN PackageServerServingSite psss
ON vssm._ResourceGuid = psss.SiteGuid
JOIN vSubnet vs
ON vssm.SubnetGuid = vs.Guid
JOIN vComputer vc
ON vc.Guid = PSSS.PackageServerGuid
ORDER BY vc.Name, vs.Subnet ASC

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.

SOLUTION