Symantec Management Platform (Notification Server)

 View Only
Back to discussions
Expand all | Collapse all

Encompassed Subnets to Site Association

  • 1.  Encompassed Subnets to Site Association

    Posted Jun 21, 2016 11:26 AM

    Below SQL Query helps to identify the encompassed subnets to site association...

     

     

    /******
    SQL Query to Identify Encompassed Subnets to Site Association
    Prepared By Ludovic Ferre > Symantec
    *******/

    SELECT t.Subnet + t.Mask AS [Subnet],
    i.Name AS [Site],
    ISNULL(t2.Subnet + ' ' + t2.mASk + ' (' + i2.name + ')', '') AS [Encompassed Subnet (Site)]
    FROM vSiteSubnetMap m
    JOIN (
    SELECT s.Guid, Subnet,
    CASE WHEN s.[Subnet Mask]='128.0.0.0'THEN '/1'
    WHEN s.[Subnet Mask]='192.0.0.0'THEN '/2'
    WHEN s.[Subnet Mask]='224.0.0.0'THEN '/3'
    WHEN s.[Subnet Mask]='240.0.0.0'THEN '/4'
    WHEN s.[Subnet Mask]='248.0.0.0'THEN '/5'
    WHEN s.[Subnet Mask]='252.0.0.0'THEN '/6'
    WHEN s.[Subnet Mask]='254.0.0.0'THEN '/7'  
    WHEN s.[Subnet Mask]='255.0.0.0'THEN '/8'
    WHEN s.[Subnet Mask]='255.128.0.0'THEN '/9'  
    WHEN s.[Subnet Mask]='255.192.0.0'THEN '/10'
    WHEN s.[Subnet Mask]='255.224.0.0'THEN '/11'
    WHEN s.[Subnet Mask]='255.240.0.0'THEN '/12'
    WHEN s.[Subnet Mask]='255.248.0.0'THEN '/13'
    WHEN s.[Subnet Mask]='255.252.0.0'THEN '/14'  
    WHEN s.[Subnet Mask]='255.254.0.0'THEN '/15'
    WHEN s.[Subnet Mask]='255.255.0.0'THEN '/16'  
    WHEN s.[Subnet Mask]='255.255.128.0'THEN '/17'
    WHEN s.[Subnet Mask]='255.255.192.0'THEN '/18'
    WHEN s.[Subnet Mask]='255.255.224.0'THEN'/19'
    WHEN s.[Subnet Mask]='255.255.240.0'THEN '/20'
    WHEN s.[Subnet Mask]='255.255.248.0'THEN '/21'
    WHEN s.[Subnet Mask]='255.255.252.0'THEN '/22'
    WHEN s.[Subnet Mask]='255.255.254.0'THEN '/23'
    WHEN s.[Subnet Mask]='255.255.255.0'THEN '/24'
    WHEN s.[Subnet Mask]='255.255.255.128'THEN '/25'
    WHEN s.[Subnet Mask]='255.255.255.192'THEN '/26'
    WHEN s.[Subnet Mask]='255.255.255.224'THEN '/27'
    WHEN s.[Subnet Mask]='255.255.255.240'THEN '/28'
    WHEN s.[Subnet Mask]='255.255.255.248'THEN '/29'
    WHEN s.[Subnet Mask]='255.255.255.252'THEN '/30'
    WHEN s.[Subnet Mask]='255.255.255.254'THEN '/31'
    END AS 'Mask', CAST(LEFT(Subnet, CHARINDEX('.', Subnet, 0) - 1) AS BIGINT)* 256*256*256+ CAST(SUBSTRING(Subnet,
    CHARINDEX('.', Subnet, 0) +1 ,CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, 0) + 1))- (CHARINDEX('.', Subnet, 0) + 1))
    AS BIGINT)*256*256+ CAST(SUBSTRING (Subnet,(CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, 0) + 1)) +1),CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, 0) + 1))) + 1)) - (CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, (CHARINDEX('.', Subnet, 0) + 1))) +1))
    AS BIGINT)*256+ CAST(substring(Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, 0) + 1))) +1 )) + 1, LEN(Subnet)) AS BIGINT) AS 'lbound' ,
    CASE  WHEN s.[Subnet Mask]='128.0.0.0'THEN CAST(256*256 AS BIGINT)*256*128
    WHEN s.[Subnet Mask]='192.0.0.0'THEN 256*256*256*64
    WHEN s.[Subnet Mask]='224.0.0.0'THEN 256*256*256*32
    WHEN s.[Subnet Mask]='240.0.0.0'THEN 256*256*256*16
    WHEN s.[Subnet Mask]='248.0.0.0'THEN 256*256*256*8
    WHEN s.[Subnet Mask]='252.0.0.0'THEN 256*256*256*4
    WHEN s.[Subnet Mask]='254.0.0.0'THEN 256*256*256*2
    WHEN s.[Subnet Mask]='255.0.0.0'THEN 256*256*256  
    WHEN s.[Subnet Mask]='255.128.0.0'THEN 256*256*128
    WHEN s.[Subnet Mask]='255.192.0.0'THEN 256*256*64
    WHEN s.[Subnet Mask]='255.224.0.0'THEN 256*256*32
    WHEN s.[Subnet Mask]='255.240.0.0'THEN 256*256*16
    WHEN s.[Subnet Mask]='255.248.0.0'THEN 256*256*8
    WHEN s.[Subnet Mask]='255.252.0.0'THEN 256*256*4
    WHEN s.[Subnet Mask]='255.254.0.0'THEN 256*256*2
    WHEN s.[Subnet Mask]='255.255.0.0'THEN 256*256   
    WHEN s.[Subnet Mask]='255.255.128.0'THEN 256*128
    WHEN s.[Subnet Mask]='255.255.192.0'THEN 256*64
    WHEN s.[Subnet Mask]='255.255.224.0'THEN 256*32
    WHEN s.[Subnet Mask]='255.255.240.0'THEN 256*16
    WHEN s.[Subnet Mask]='255.255.248.0'THEN 256*8
    WHEN s.[Subnet Mask]='255.255.252.0'THEN 256*4
    WHEN s.[Subnet Mask]='255.255.254.0'THEN 256*2
    WHEN s.[Subnet Mask]='255.255.255.0'THEN 256
    WHEN s.[Subnet Mask]='255.255.255.128'THEN 128
    WHEN s.[Subnet Mask]='255.255.255.192'THEN 64
    WHEN s.[Subnet Mask]='255.255.255.224'THEN 32
    WHEN s.[Subnet Mask]='255.255.255.240'THEN 16
    WHEN s.[Subnet Mask]='255.255.255.248'THEN 8
    WHEN s.[Subnet Mask]='255.255.255.252'THEN 4
    WHEN s.[Subnet Mask]='255.255.255.254'THEN 2
    WHEN s.[Subnet Mask]='255.255.255.255'THEN 1
    END AS 'Hosts' FROM vSubnet s
    ) t
    ON t.Guid = m.SubnetGuid
    LEFT JOIN (
    SELECT s.Guid, Subnet,
    CASE WHEN s.[Subnet Mask]='128.0.0.0'THEN '/1'
    WHEN s.[Subnet Mask]='192.0.0.0'THEN '/2'
    WHEN s.[Subnet Mask]='224.0.0.0'THEN '/3'
    WHEN s.[Subnet Mask]='240.0.0.0'THEN '/4'
    WHEN s.[Subnet Mask]='248.0.0.0'THEN '/5'  
    WHEN s.[Subnet Mask]='252.0.0.0'THEN '/6'  
    WHEN s.[Subnet Mask]='254.0.0.0'THEN '/7'  
    WHEN s.[Subnet Mask]='255.0.0.0'THEN '/8'  
    WHEN s.[Subnet Mask]='255.128.0.0'THEN '/9'
    WHEN s.[Subnet Mask]='255.192.0.0'THEN '/10'  
    WHEN s.[Subnet Mask]='255.224.0.0'THEN '/11'  
    WHEN s.[Subnet Mask]='255.240.0.0'THEN '/12'  
    WHEN s.[Subnet Mask]='255.248.0.0'THEN '/13'  
    WHEN s.[Subnet Mask]='255.252.0.0'THEN '/14'  
    WHEN s.[Subnet Mask]='255.254.0.0'THEN '/15'  
    WHEN s.[Subnet Mask]='255.255.0.0'THEN '/16'  
    WHEN s.[Subnet Mask]='255.255.128.0'THEN '/17'
    WHEN s.[Subnet Mask]='255.255.192.0'THEN '/18'
    WHEN s.[Subnet Mask]='255.255.224.0'THEN'/19'
    WHEN s.[Subnet Mask]='255.255.240.0'THEN '/20'
    WHEN s.[Subnet Mask]='255.255.248.0'THEN '/21'
    WHEN s.[Subnet Mask]='255.255.252.0'THEN '/22'
    WHEN s.[Subnet Mask]='255.255.254.0'THEN '/23'
    WHEN s.[Subnet Mask]='255.255.255.0'THEN '/24'
    WHEN s.[Subnet Mask]='255.255.255.128'THEN '/25'
    WHEN s.[Subnet Mask]='255.255.255.192'THEN '/26'
    WHEN s.[Subnet Mask]='255.255.255.224'THEN '/27'
    WHEN s.[Subnet Mask]='255.255.255.240'THEN '/28'
    WHEN s.[Subnet Mask]='255.255.255.248'THEN '/29'
    WHEN s.[Subnet Mask]='255.255.255.252'THEN '/30'
    WHEN s.[Subnet Mask]='255.255.255.254'THEN '/31'
    END AS 'Mask', CAST(LEFT(Subnet, CHARINDEX('.', Subnet, 0) - 1) AS BIGINT)* 256*256*256+ CAST(SUBSTRING
    (Subnet,CHARINDEX('.', Subnet, 0) +1 ,CHARINDEX('.',
    Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, 0) + 1))- (CHARINDEX('.', Subnet, 0) + 1))
    AS BIGINT)*256*256+ CAST(SUBSTRING (Subnet,(CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, 0) + 1)) +1),CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, 0) + 1))) + 1)) - (CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    (CHARINDEX('.', Subnet, 0) + 1))) +1)) AS BIGINT)*256+ CAST(substring(Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet, CHARINDEX('.', Subnet,
    CHARINDEX('.', Subnet, 0) + 1))) +1 )) + 1, LEN(Subnet)) AS BIGINT) AS 'lbound' ,
    CASE  WHEN s.[Subnet Mask]='128.0.0.0'THEN CAST(256*256 AS BIGINT)*256*128
    WHEN s.[Subnet Mask]='192.0.0.0'THEN 256*256*256*64
    WHEN s.[Subnet Mask]='224.0.0.0'THEN 256*256*256*32
    WHEN s.[Subnet Mask]='240.0.0.0'THEN 256*256*256*16
    WHEN s.[Subnet Mask]='248.0.0.0'THEN 256*256*256*8
    WHEN s.[Subnet Mask]='252.0.0.0'THEN 256*256*256*4
    WHEN s.[Subnet Mask]='254.0.0.0'THEN 256*256*256*2
    WHEN s.[Subnet Mask]='255.0.0.0'THEN 256*256*256   
    WHEN s.[Subnet Mask]='255.128.0.0'THEN 256*256*128
    WHEN s.[Subnet Mask]='255.192.0.0'THEN 256*256*64
    WHEN s.[Subnet Mask]='255.224.0.0'THEN 256*256*32
    WHEN s.[Subnet Mask]='255.240.0.0'THEN 256*256*16
    WHEN s.[Subnet Mask]='255.248.0.0'THEN 256*256*8
    WHEN s.[Subnet Mask]='255.252.0.0'THEN 256*256*4
    WHEN s.[Subnet Mask]='255.254.0.0'THEN 256*256*2
    WHEN s.[Subnet Mask]='255.255.0.0'THEN 256*256   
    WHEN s.[Subnet Mask]='255.255.128.0'THEN 256*128
    WHEN s.[Subnet Mask]='255.255.192.0'THEN 256*64
    WHEN s.[Subnet Mask]='255.255.224.0'THEN 256*32
    WHEN s.[Subnet Mask]='255.255.240.0'THEN 256*16
    WHEN s.[Subnet Mask]='255.255.248.0'THEN 256*8
    WHEN s.[Subnet Mask]='255.255.252.0'THEN 256*4
    WHEN s.[Subnet Mask]='255.255.254.0'THEN 256*2
    WHEN s.[Subnet Mask]='255.255.255.0'THEN 256
    WHEN s.[Subnet Mask]='255.255.255.128'THEN 128
    WHEN s.[Subnet Mask]='255.255.255.192'THEN 64
    WHEN s.[Subnet Mask]='255.255.255.224'THEN 32
    WHEN s.[Subnet Mask]='255.255.255.240'THEN 16
    WHEN s.[Subnet Mask]='255.255.255.248'THEN 8
    WHEN s.[Subnet Mask]='255.255.255.252'THEN 4
    WHEN s.[Subnet Mask]='255.255.255.254'THEN 2
    WHEN s.[Subnet Mask]='255.255.255.255'THEN 1
    END AS 'Hosts' FROM vSubnet s
    ) t2
    ON t.lbound <= t2.lbound
    AND t.lbound + t.hosts >= t2.lbound + t2.hosts
    AND t.Guid != t2.Guid
    LEFT JOIN vSiteSubnetMap m2
    ON t2.Guid = m2.SubnetGuid
    AND m._ResourceGuid != m2._ResourceGuid
    LEFT JOIN vSite i
    ON m._ResourceGuid = i.Guid
    LEFT JOIN vSite i2
    ON m2._ResourceGuid = i2.Guid    
    ORDER BY t.lbound DESC,
    t.lbound + t.hosts DESC