Video Screencast Help

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