Video Screencast Help

Encompassed subnets to site association...

Created: 04 Jul 2013 | 2 comments

Looking for a SQL query to identify the encompassed subnets to site association.

Below query will give the detailed site mapping information, however it does not hold encompassed subnets.

 

SELECT DISTINCT vSite.[Name] [Site Name],s.Subnet, s.[Subnet Mask]
,     c.[Name] [Package Server Name]
,     c.[Domain]
,     c.[OS Name]
,     c.[IP Address]
,     t1.[Assignment Type]
FROM vSite
LEFT OUTER JOIN vSiteSubnetMap sm
     ON sm._ResourceGuid = vSite.[Guid]
LEFT OUTER JOIN vSubnet s
     ON s.Guid = sm.SubnetGuid
INNER JOIN (
     SELECT ra1.ParentResourceGuid [ComputerGuid]
     ,     ra1.ChildResourceGuid [SiteGuid]
     ,     NULL [IP Address]
     ,     'Manually Assigned' [Assignment Type]
     FROM ResourceAssociation ra1
     WHERE ra1.ResourceAssociationTypeGuid = '5E8E3C61-A80C-4b0a-A228-DBF97607CEE4' -- Manual Computer to Site Service association
     UNION
     SELECT ra2.ChildResourceGuid [ComputerGuid]
     ,     NULL [SiteGuid]
     ,     tcpip.[Ip Address]
     ,     'Package Server in Site Subnet' [Assignment Type]
     FROM vRM_PackageService ps
     INNER JOIN ResourceAssociation ra2
          ON ps.[Guid] = ra2.ParentResourceGuid
          AND ra2.ResourceAssociationTypeGuid='5F00E96B-93F3-41f0-94A7-7DBBB8AEF841' -- Site Service To Computer
     INNER JOIN Inv_AeX_AC_TCPIP tcpip
          ON tcpip._ResourceGuid = ra2.ChildResourceGuid
     WHERE tcpip.[Subnet Mask] != '255.255.255.255'
          AND tcpip.[IP Address] != ''
     ) t1
     ON t1.SiteGuid = vSite.[Guid]
     OR (dbo.fnIsIPInSubnet(t1.[IP Address], s.Subnet, s.[Subnet Mask]) = 1 -- If Package Server IP is in site's subnet then join
          AND t1.[IP Address] IS NOT NULL)
INNER JOIN vComputer c
     ON c.[Guid] = t1.ComputerGuid
WHERE c.IsLocal = 1
ORDER BY vSite.[Name]
 

Subnet.png

Comments 2 CommentsJump to latest comment

SK's picture

If you run SQL Profiler and then load that page, the query or stored procedure used to populate the encompassed column should be captured, which will then allow you to add the appropriate snytax to your existing query.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

Kada's picture

Emil,

did you find such query which will identify the encompassed subnets?