< 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
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
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}"
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
(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:
(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
- 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
- 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
Here is an example of populated records in table:
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
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. 
