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

Parents
  • By the way, it has always been on my mind to request a feature similar to Custom Properties, but only for Custom Tables emoticons_happy.png I never published it though as I could not work out based way of explaining it to community. I have so many custom tables that I use and it has became pretty much my standard setup now. Would be great to see this as a feature one day so that you can create your own custom tables for holding various stuff to refer to, based on your requirements.

    Examples can be:

    - Custom table for holding Sites info

    - Custom table for holding additional custom variables and global variables you can use when setting up alerts, etc

    - Custom table to hold information about Latency offset to a particular site so that you can adjust your alert to account for thos nodes that are remote to poller and still keep your response alerts valid. This can be extra field in Sites table as well

    - Custom table to hold info about your customers

    - Custom table to hold info about your services you provide to customers, including description and other relevant data. Both customers and services info can then be displayed as a dynamic resource on your node page. All you do - set CustomerID in Custom Proeprty and then it it linked to record in custom table and all info about this customer/service is being displayed on Node's landing page.

    ... the possibilities are endless emoticons_happy.png

Reply
  • By the way, it has always been on my mind to request a feature similar to Custom Properties, but only for Custom Tables emoticons_happy.png I never published it though as I could not work out based way of explaining it to community. I have so many custom tables that I use and it has became pretty much my standard setup now. Would be great to see this as a feature one day so that you can create your own custom tables for holding various stuff to refer to, based on your requirements.

    Examples can be:

    - Custom table for holding Sites info

    - Custom table for holding additional custom variables and global variables you can use when setting up alerts, etc

    - Custom table to hold information about Latency offset to a particular site so that you can adjust your alert to account for thos nodes that are remote to poller and still keep your response alerts valid. This can be extra field in Sites table as well

    - Custom table to hold info about your customers

    - Custom table to hold info about your services you provide to customers, including description and other relevant data. Both customers and services info can then be displayed as a dynamic resource on your node page. All you do - set CustomerID in Custom Proeprty and then it it linked to record in custom table and all info about this customer/service is being displayed on Node's landing page.

    ... the possibilities are endless emoticons_happy.png

Children
No Data