Symantec Management Platform (Notification Server)

 View Only
  • 1.  Query to pull subnets groupings that are associated with a Site

    Posted Jan 24, 2017 06:44 PM

    I have multiple sites setup within the Site Management area of the console and each one has multiple subnets associated with them.  My dilemma is that the majority of the subnets listed under the site are listed in a tree like method under the main subnet for the site.  You can see what I'm referring to in the screenshot below.  10.10.0.0/16 is the main subnet and the rest below are the ones I'm looking to pull from the database.  When pulling the resource associations for the site, I'm able to get the 10.10.0.0/16 subnet but none of the others show up.  Not sure how I can query for the others.

    SubnetCapture.PNG

    I've hit a wall here trying to find this in the database and I'm looking to see if anyone knows how these subnets are associated to eachother within the database.  I've looked under the following tables: RM_ResourceSite, RM_ResourceSubnet, ResourceAssociation, Inv_Subnet, ItemReference, FolderBaseFolder and have come up short.  Anyone run into this?  I know I could remove the /16 subnet and add the other sites individually, but ultimately that's not what I'm looking for as I would like subnets under 10.10.0.0/16 to automatically be added to this site.



  • 2.  RE: Query to pull subnets groupings that are associated with a Site
    Best Answer

    Posted Jan 25, 2017 09:53 AM

    While this is not ultimately what I wanted, I think I may have found a way to achieve my ultimate goal which is to find the computers that are on a subnet within site.  I'm still intrueged to know if someone has a solution for my original question.  If anyone has this goal, here is the SQL I used. 

    declare @SiteName nvarchar(250);
    set @SiteName = 'Name of Site in Site Management';
    
    with computers as (
    	select [guid],[name],[IP Address] from vComputer)
    ,subnets as (
    select Subnet,[Subnet Mask] 
    from vSubnet
    where [Guid] IN (select rsub.[Guid] from RM_ResourceSubnet rsub
    inner join ResourceAssociation ra
    	on ra.ChildResourceGuid = rsub.[Guid]
    	and ra.ResourceAssociationTypeGuid = 'F71ABA8C-8823-44C8-A750-F03DE84DC5F6'
    left outer join RM_ResourceSite rsite
    	on ra.ParentResourceGuid = rsite.[Guid]
    where rsite.[Name] = @SiteName))
    select computers.Guid from subnets,computers
    where (select dbo.fnIsIPInSubnet(computers.[IP Address],subnets.Subnet,subnets.[Subnet Mask])) = 1

     



  • 3.  RE: Query to pull subnets groupings that are associated with a Site

    Posted Jan 25, 2017 03:22 PM

    I updated the SQL query due to problems with sites with multiple subnets.  This one, while not elegant, works around it.



  • 4.  RE: Query to pull subnets groupings that are associated with a Site

    Posted Jan 28, 2017 12:19 PM

    You could try running SQL Server Tracer when you click and expand the tree and see what Symantec queries.