Hopefully you all know about the dependency feature in Orion where nodes can be depending on another node, not creating alert storms when one central node goes down.
But when you get an alert that a node is down, how do you know if other nodes are also affected by this node? There is no variable for that and Solarwinds support say "it can't be done".... Sounds like a challenge to me :-)
In Orion we can create SQL variables, that is when there is no variable in Orion for what we need we can create our own. Our new SQL variable will dig down through the dependency table to find all nodes in all layers of dependencies. As we don't know how many layers there are we use a SQL tool called "Recursive CTEs", read more here: https://www.essentialsql.com/recursive-ctes-explained/
The result can only be one Cell for orion to accept it, so to get one cell we use "FOR XML PATH" that I explained here: Re: Custom SQL Variable
Now that cell can contain a lot of information if there is many depending nodes. To make it easier to read I solved this by adding html-code to the result passed back to orion, so yes, the alert email has to be in html-format for below to look nice (I have another version without html also if you really want one). Also, the code ended up being a bit long to add into the alert action so I created a SQL Scalar Function of the code, making the code in the alert look much cleaner. The end result looks something like this when alerting on the "101-node:
So, what you have to do is:
* Run attached SQL script against you orion database (If you don't know how to do that you should not, ask your DBA)
* In you alert action add this SQL Variable: ${SQL: SELECT dbo.GetDependentNodes (${N=SwisEntity;M=NodeID})}
Hope this helps someone out there!
