Automatic Dependencies Based On Custom Properties (Node Location)

< 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 emoticons_happy.png

Happy SolarWinding

Alex Soul

www.pixace.com

Top Replies

  • As a feature would be nice, but until then...

    In my testing environment, I used Custom_Aux, but if I can do this in live, I might use something more unique, like using my Dept initials (ITSD) to prefix the table.

  • Hi Alex, should this code work on NMP 12?

    I am creating dependencies, 711 of them, but they do not show in he manage decencies global or on the node.

    did you update this for V12?

    Cheers

    Mez

  • its ok think Ive got it working in V12

     

    INSERT (Name, ParentUri, ChildUri, LastUpdateUtc,ParentNetObjectID,ChildNetObjectID,ParentEntityType,ChildEntityType) 

    VALUES (nDependencyName, nParentUri, nChildUri, GetUtcDate(), sEdgeNodeID,nNodeID,'Orion.Nodes','Orion.Nodes') 

    I also dotn use a separate table, I have a view group on site code, and with our naming conventions pick out the router for that site. a lot easier not having to maintain a separate list. will see if I like the 711 dependencies now. thanks alot

  • nice one, good spot. Ye, I didn't get a chance to migrate this one to V12 yet and your input is highly appreciated emoticons_happy.png

  • oh Alex another question, can you explain a little more re logic in a HA scenario?

    rolling out a new wan, all sites will have a single router (no spoke sites anymore emoticons_wink.png) I have set dependencies using this script for all remote sites, so all hardware switches,firewalls etc are dependant on the remote site router - works like a charm now with the above tweaks.

    However all the new wan sites have two paths back to DC1 and DC2. I have currently removed the HA both routers from the data centres (sites) from my view to ensure I get the dependencies correct. Any help greatly appreciated.

    When I took the HA routers out my dependencies row count dropped to 488 ish

    Mez

  • just got back from a short break. Are you able to sketch basic diagram to make it bit more clear about the line-of-sight between SolarWinds poller and nodes from the wan perspective, including those two DCs

  • alexslv

    I just wanted to drop in and let you know about the NPM 12 bug with the script. I just implemented this to get rid of the rampant auto-dependencies that Solarwinds creates when you have it set to calculate automatically.

    Here are the last two lines of the script if you're using Groups for dependencies vs NodeID's:

    THEN 

      INSERT (Name, ParentUri, ChildUri, LastUpdateUtc, ParentNetObjectID, ChildNetObjectID, ParentEntityType, ChildEntityType) 

      VALUES (gDependencyName, nParentUri, nChildUri, GetUtcDate(), sEdgeGroupID, nNodeID, 'Orion.Groups', 'Orion.Nodes') 

    ;

    I hope this helps some others as well. Thanks to mezdem​ for getting me to the point where the info was missing from the script!

  • Can we try this in 2019 version of Orion alexslv

    Please suggest as I am looking for solution where Priority 2 ticket will not get generated for nodes which has redundancy in place.

    We are getting bulk of P2 alerts and looking for solution to reduce it.