Query to show Package Servers per site across hierarchy
Created: 01 May 2013 • Updated: 02 May 2013 | 3 comments
This issue has been solved. See solution.
I'm trying to write a sql query that will show me all active site servers and their associated site across hierarchy.
I came up with this:
comp2site.ParentResourceGuid as SiteServerGuid
FROM ResourceAssociation comp2site
JOIN vSite s ON s.Guid= comp2site.ChildResourceGuid
JOIN vComputer vc ON vc.Guid = comp2site.ParentResourceGuid
WHERE comp2site.ResourceAssociationTypeGuid='5E8E3C61-A80C-4b0a-A228-DBF97607CEE4'--Computer To Site
The trouble with this is that while it returns a list of site server, the site associated is only what the parent NS thinks it site is, not what site the children NS thinks they are in.
We have a single parent NS with two Child NS's.