Video Screencast Help

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.
Operating Systems:

Comments 3 CommentsJump to latest comment

AltirisJunkie's picture

This may be of use for you.

I use a portion of this as a automation policy that runs every hour on all SMP's in my hierarchy to email me if any of my site servers through out the infrastructure miss their checkin by 2 hours.

I have tweaked it some to run a report on which sites they all belong to.

Can be run from your parent to show all downstream task servers.

Please tweak for your usage.

select Distinct v.Guid,,
ISNULL(CAST(c.[LastConfig] AS varchar(64)), 'Last Config Request Missing for Conversion') AS 'Hours Since Last Check In', 
ISNULL(CAST(c.[LastConfigDate] AS varchar(64)), 'May Belong to Child or Have Issues') AS 'Last Configuration Request Date',
vs.Name AS 'Belongs to This Defined Site'
From vComputer v
INNER JOIN Inv_AeX_AC_Client_Agent ac on ac._ResourceGuid = v.Guid
LEFT OUTER JOIN(SELECT ResourceGuid, LastConfig = MIN(datediff(hh,[StartTime],getdate())), LastConfigDate = MAX([StartTime]) 
        FROM Evt_NS_Client_Config_Request with (NOLOCK) 
        GROUP BY ResourceGuid) 
        AS c ON c.[ResourceGuid] = v.[Guid]
INNER JOIN [Inv_AeX_AC_TCPIP] AS [tcp] ON [tcp].[_ResourceGuid] = [v].[Guid]
INNER JOIN [vSite] AS [vs] ON [vs].Name LIKE '%'
INNER JOIN [vSiteSubnetMap] AS [ss] ON [ss]._ResourceGuid = [vs].[Guid]
Left Outer JOIN [Inv_Subnet] AS [sn] ON [sn].[_ResourceGuid] = [ss].[SubnetGuid]
Where ac.[Agent Name] = 'altiris client task server agent'
AND v.IsManaged = 1
AND ac.[Product Version] > '7.%'
AND dbo.[fnIsIPInSubnet2]([tcp].[IP Address],[sn].[Subnet],[sn].[Subnet Mask]) = 1
order by v.Name asc
5-2-2013 10-45-15 AM.jpg
This is ran from my parent
Hope this helps,
ArthurV's picture

Quick question about the Automation Policy.  When you create the Automation Policy on the Parent, replication creates it on the Child.  Do you just turn it off on the child so you don't get two emails?

charlespaul279's picture

Thanks!  That gets me pretty close to what I need.

So the lesson here for me is that to relate a computer to it's site you have to di it via site to subnet calculations.