Automatic Dependencies Based On Custom Properties (Node Location)

Version 3

    < BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

     

    Hi Guys,

     

    I am very excited to present you with this solution that will allow you to create automatic dependencies based on custom properties. Again, this solution fits perfectly well with my previously discussed automation, such as Automatically Update World Map Points Based On Custom Property Value and Manage Node's Response Time Thresholds Better By Accounting For WAN Latencies. I suggest you skim read them first to get an idea of the structure and then carry on with this setup as I am going to be building this solution on top of existing config

     

    So, let's begin...

     

    How it will work:

     

    You will maintain location information for your nodes in custom property ("n_site_id" for example).

    You will have table, which will hold all information about your sites + information about edge devices (or group of edge devices if you have HA routers/firewall on the edge) allocated to every site.

    You we will then have magic happening at the background which will keep creating and managing all site dependencies for you the moment you change site for the node or update edge devices for any site

     

    Note: I do this just for the 1st level of PARENT-CHILD relationships. It will possibly work with more deeper levels, but requires further testing to ensure logic will work as expected. For our global architecture with 20+ sites (as below) this works perfectly fine

    arch.JPG

     

    The result is pretty awesome. In my case 153 dependencies have been automatically created in a split of a second and now are being dynamically managed just by me maintaining node's site id custom property. Besides I really feel I am in full control over them all

    006.JPG

     

    Notice naming convention:

    Names start with "auto_" for all dependencies created this way. In case you create some of your own manual dependencies they will not be overwritten nor will be deleted as long as you do not give your manually created dependencies "auto_" at the beginning of a name

     

    Here is how it will look like on the Node's page when you will add resource "All Dependencies including node ${Caption}"

    SnipImage.JPG

     

    Step-by-step:

     

    (1)

    First, you need Aux database

     

    (2)

    Second, you need to have Sites table within this database as described here in step (2) + add additional two columns to define Edge device IDs

    001.PNG

     

    (3)

    Third - you need "n_site_id" custom property which you will use to specify the location for the node. As described in my previous articles I do this with "n_site_id" custom property:

    002.JPG

     

    (4)

    And finally, with all the above you are now ready to Rock'n Roll with scripted dependencies creation

     

    Here is a magical two SQL scripts for you to do so.

     

    This one is to create dependencies when parent is a Node:

     

    MERGE SolarWinds.dbo.Dependencies AS Target
    USING
      (
        SELECT
          n.NodeID
         ,n.Caption
         ,n.n_site_id
         ,s.EdgeNodeID
         ,'auto_n'+CONVERT(NVARCHAR(250),s.EdgeNodeID)+'--n'+CONVERT(NVARCHAR(250),n.NodeID) AS 'DependencyName'
         ,'swis://XXXXXXXXXXXXXXXXXXXXX/Orion/Orion.Nodes/NodeID='+CONVERT(NVARCHAR(250),s.EdgeNodeID) AS 'ParentUri'
         ,'swis://XXXXXXXXXXXXXXXXXXXXX/Orion/Orion.Nodes/NodeID='+CONVERT(NVARCHAR(250),n.NodeID) AS 'ChildUri'
        FROM SolarWinds.dbo.Nodes n
        LEFT JOIN SolarWindsAux.dbo.Sites s ON s.SiteID=n.n_site_id
        LEFT JOIN Solarwinds.dbo.Dependencies d ON (d.ChildUri LIKE '%='+CONVERT(NVARCHAR(250),n.NodeID) AND d.Name LIKE 'auto_n%')
      ) AS Source (nNodeID, nCaption, nSiteID, sEdgeNodeID, nDependencyName, nParentUri, nChildUri) ON (Target.ChildUri = Source.nChildUri AND Target.Name = Source.nDependencyName)
    -- Delete from DEPENDENCIES...
    WHEN NOT MATCHED BY source --if node no longer exist in Nodes table...
      AND Target.Name LIKE 'auto_n%' --and providing that record has been created by this automation
    THEN DELETE
    -- Delete from DEPENDENCIES...
    WHEN MATCHED --if record already exist...
      AND Target.Name LIKE 'auto_n%' --and providing that record has been created by this automation
      AND (
      Source.nSiteID IS NULL --and either site id was not specified
      OR Source.sEdgeNodeID IS NULL --or edge device was not specified for this site
      OR Source.nNodeID = Source.sEdgeNodeID --or device is an edge device itself
      )
    THEN DELETE
    -- Update record if...
    WHEN MATCHED --record exist...
      AND Target.Name LIKE 'auto_n%' --and providing that record has been created by this automation
      AND Source.nParentUri <> Target.ParentUri --and if edge device has been changed (this can be due to site for the node has been changed or edge itself has been changed for the entire site)
    THEN UPDATE
      SET
       Name = nDependencyName,
       ParentUri = nParentUri,
       LastUpdateUtc = GetUtcDate()
    -- Synch new dependencies
    WHEN NOT MATCHED BY target
      AND Source.nSiteID IS NOT NULL --Site has been specified
      AND Source.sEdgeNodeID IS NOT NULL --Edge device has been specified
      AND Source.nNodeID <> Source.sEdgeNodeID --Node is not edge device itself
    THEN
      INSERT (Name, ParentUri, ChildUri, LastUpdateUtc)
      VALUES (nDependencyName, nParentUri, nChildUri, GetUtcDate())
    ;
    
    
    
    
    
    
    
    
    
    

     

     

    This one is to create dependencies when parent is a Group:

     

    MERGE SolarWinds.dbo.Dependencies AS Target
    USING
      (
        SELECT
          n.NodeID
         ,n.Caption
         ,n.n_site_id
         ,s.EdgeGroupID
         ,'auto_g'+CONVERT(NVARCHAR(250),s.EdgeGroupID)+'--n'+CONVERT(NVARCHAR(250),n.NodeID) AS 'DependencyName'
         ,'swis://XXXXXXXXXXXXXXXXXXXXXX/Orion/Orion.Groups/ContainerID='+CONVERT(NVARCHAR(250),s.EdgeGroupID) AS 'ParentUri'
         ,'swis://XXXXXXXXXXXXXXXXXXXXXX/Orion/Orion.Nodes/NodeID='+CONVERT(NVARCHAR(250),n.NodeID) AS 'ChildUri'
        FROM SolarWinds.dbo.Nodes n
        LEFT JOIN SolarWindsAux.dbo.Sites s ON s.SiteID=n.n_site_id
        LEFT JOIN Solarwinds.dbo.Dependencies d ON (d.ChildUri LIKE '%='+CONVERT(NVARCHAR(250),n.NodeID) AND d.Name LIKE 'auto_g%')
      ) AS Source (nNodeID, nCaption, nSiteID, sEdgeGroupID, gDependencyName, nParentUri, nChildUri) ON (Target.ChildUri = Source.nChildUri AND Target.Name = Source.gDependencyName)
    -- DELETE RECORD...
    WHEN NOT MATCHED BY source --if node no longer exist in Nodes table...
      AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation
    THEN DELETE
    -- DELETE RECORD...
    WHEN MATCHED --if node exist...
      AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation
      AND (
      Source.nSiteID IS NULL --and either site was not specified
      OR Source.sEdgeGroupID IS NULL --or edge group was not specified for this site
      OR Source.nNodeID IN --or node is a member of the edge group itself
        (SELECT n_excl.EntityID
         FROM Solarwinds.dbo.ContainerMemberSnapshots n_excl
         WHERE
           n_excl.EntityType = 'Orion.Nodes' AND
           n_excl.ContainerID = Source.sEdgeGroupID))
    THEN DELETE
    -- UPDATE RECORD...
    WHEN MATCHED --when it is already there...
      AND Target.Name LIKE 'auto_g%' --and providing that record has been created by this automation...
      AND Source.nParentUri <> Target.ParentUri --and edge group has been changed (this can be due to site for the node has been changed or edge group has been changed for the entire site)
    THEN UPDATE
      SET
       Name = gDependencyName,
       ParentUri = nParentUri,
       LastUpdateUtc = GetUtcDate()
    -- INSERT NEW RECORD...
    WHEN NOT MATCHED BY target --when it does not already exist...
      AND Source.nSiteID IS NOT NULL --and site has been specified...
      AND Source.sEdgeGroupID IS NOT NULL --and edge group has been specified...
      AND Source.nNodeID NOT IN --and node is not a member of the Edge group itself
        (SELECT n_excl.EntityID
         FROM Solarwinds.dbo.ContainerMemberSnapshots n_excl
         WHERE
           n_excl.EntityType = 'Orion.Nodes' AND
           n_excl.ContainerID = Source.sEdgegroupID)
    THEN
      INSERT (Name, ParentUri, ChildUri, LastUpdateUtc)
      VALUES (gDependencyName, nParentUri, nChildUri, GetUtcDate())
    ;
    
    
    
    
    
    
    
    
    
    

     

    IMPORTANT:

    4.1 You first need to go though this script and change database name as per your SQL setup

    4.2 You also need to replace 'XXXXXXXXXXXX' with correct FQDN for your environment. The best way to do so is to create dependency manually and then check ParentUri and ChildUri columns in [Dependencies] table to find out what it should be set to

     

    (5)

    Final step is to configure SQL job to do it automatically (unless you are ok to run the above scripts manually every time somebody changes site id for your nodes).

     

    Please refer to the following step-by-step guide from MSDN on how to create SQL scheduled job: Schedule a Job

     

     

    Maintenance:

     

    You will need to populate your Sites table with data first.

    • SiteID column should have values corresponding with "n_site_id" custom properties drop down values.
    • EdgeNodeID is an ID of a node. When you land on the node page you can get this ID from the URL

    004.JPG

    • EdgeGroupID is an ID of a group. Group your edge devices together and then from the group summary page note down Group ID from the URL

    005.JPG

      • Remember to set your group settings to roll over to down state only when all devices are down (screenshot below) - this is key when setting up dependencies for the group

    003.JPG

     

    Here is an example of populated records in table:

    008.PNG

     

    Please note that you would normally specify either EdgeNodeID or EdgeGroupID for a specific site, not both of them at the same time

     

     

    That's it.

     

    From now on all you need to do is to allocate correct site ID for your nodes (or to change site ID for exisitng one) for the magic to happen

     

    Happy SolarWinding

     

    Alex Soul

    www.pixace.com