SolarWinds Orion – Engineering Better Alert Emails

Those who’ve been around me for a while have likely heard a mild SolarWinds “Caleb” rant at some point. Usually, it’s on a Friday afternoon when notifications are going haywire because someone added something new to the network and didn’t add it to SolarWinds, or added it and didn’t change the parent/child relationships correctly in SolarWinds, or didn’t configure SNMP correctly and simply left things in a state of disrepair.

Or, a rant that SolarWinds is still running on .NET Framework code in 2019, their devs won’t re-design it on a more modern coding platform and adapt to the times, and it’ll likely suffer the “Blockbuster” effect in the next five to ten years and be replaced by something like Ansible Tower.

But, the fact of life is that for better or for worse, I’ve been stuck in a love/hate relationship with it for the past few years.

Today, we’re going to take a depth look at doing a couple of useful things with SolarWinds Orion’s alerting, specifically with the email alerting. The default alerts included with the platform are OK, but formatting considerations as well as SQL-side changes that occur in minor version changes of Orion have made them historically somewhat “shaky” out of the box.

This article is divided into two sections. Firstly, we’ll look at cleaning up our emails with little bit of HTML and CSS to get things easier to read. Secondly, we’ll take a look at using SQL to make some custom calls to our database to make multi-node correlations.

Part One – HTML and CSS in Alerts

Pasted below, you’ll find a simple alert template that uses some basic code to provide a layout that’s a bit more pleasing to the eye.

<html>
<head>
<style type="text/css"></style>
</head>
<body>

<table class='alert' style="border:3px solid #EBC800;border-collapse:collapse;font-family:Verdana;font-size:10pt;"><tr><th colspan="2" style="border-bottom:3px solid #EBC800;padding:15px;background-color:#FFF2A6;text-align:center;">

<b>Put_In_Your_Alert_Name_Header_Here</b></th></tr>

<tr><td style="text-align:left;padding:5px">Row_One_Name</td><td>Row_One_Value</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Row_Two_Name</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">Row_Two_Value</td></tr>

<tr><td style="text-align:left;padding:5px"Row_Three_Name</td><td>Row_Three_Value</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Row_Four_Name</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">Row_Four_Value</td></tr>

</table>
<br>

This message was generated by the SolarWinds alert: ${N=Alerting;M=AlertName}

</body>
</html>

The header field as well as table value fields can be populated with whatever values you prefer, including variable calls specific to the item you’re alerting on. This table structure provides four rows in the template listed above, but you can copy the row entry code fields to the bottom of the <table> section for as many times as you need to add additional rows.

As an example, I’ll go ahead and populate this with some values for monitoring an F5 Networks load balancer, and the virtual IPs it’s load balancing on.

The code looks like this:

<html>
<head>
<style type="text/css"></style>
</head>
<body>

<table class='alert' style="border:3px solid #EBC800;border-collapse:collapse;font-family:Verdana;font-size:10pt;"><tr><th colspan="2" style="border-bottom:3px solid #EBC800;padding:15px;background-color:#FFF2A6;text-align:center;">

<b>SolarWinds - F5 VIP Is ${N=SwisEntity;M=Status}</b></th></tr>

<tr><td style="text-align:left;padding:5px">VIP</td><td>${N=SwisEntity;M=Caption}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Location</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=SwisEntity;M=Router.Nodes.Location}</td></tr>

<tr><td style="text-align:left;padding:5px">Packet Loss %</td><td>${N=SwisEntity;M=Stats.PercentLoss}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Timestamp</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=Alerting;M=AlertTriggerTime;F=DateTime}</td></tr>

</table>
<br>

This message was generated by the SolarWinds alert: ${N=Alerting;M=AlertName}

</body>
</html>

For reference, pictured below is a bogus example of what this alert email now looks like when it’s triggered:

Picture

There’s a variety of things you could change on this template, such as altering the color scheme or changing the spacing or alignment of text to suite your needs. Before we proceed any further, however, a couple of points need to be conveyed.

First, understand that SolarWinds’ ability to parse HTML and CSS code is extremely limited, at best. As an example of this, if you were to try doing some more complicated embedding in a <meta> tag for analytics purposes, or using the <script> field to make instructional dropdown lists in the code, you’ll find that attempting to put this in an alert email within SolarWinds will cause your web frontend server to crash. This system is designed to parse basic HTML only; try and keep things as minimal as possible.

Second, SolarWinds and many email clients that render HTML (such as Microsoft Outlook) “disagree” on how to handle a carriage return versus a line break. Because of this, if you were to paste my previous code snippet straight into an alert email field without changing anything, you’d find that the spacing is horridly skewed when viewing the alert in Outlook or Thunderbird.

The way around this is to remove all text editor line breaks, carriage returns, and paragraph breaks from your code before you paste it in. The tool I use for this is made by TextFixer, available here: https://www.textfixer.com/tools/remove-line-breaks.php .

This should provide you with everything you need to get your hands dirty in the world of HTML alerts. For those who’re looking to fully leverage the SQL side of alerting possibilities, we’ll discuss this next.

Part Two – Using SQL Queries in Alerts

​The need for using SQL to get certain information out of your alerts is unfortunately born from the fact that SolarWinds Orion in its stock form doesn’t contain the full featureset that most experienced admins are looking for in terms of monitoring criteria.

To illustrate, let’s suppose you’re in charge of networking for a large enterprise. You’re running BGP at your perimeter, and you have a small pool of providers you’re peered with that are at least one hop away. You’d like to keep track of those routing peers, and alert if of them goes down but the physical link to your next hop is otherwise still up. Pretty understandable.

While SolarWinds will allow you to monitor a routing neighborship, the only query-able field that it will alert on by default is the IP address of the peer. While that’s fine in itself, what if you’d like to also get the peer name as well as other information, such as a custom property or SNMP-defined value? You’d otherwise be out of luck with Orion’s default offerings.

Enter SQL queries, and you can now make this association.

Let’s take a look at a step-by-step example, then briefly discuss what it’s doing and why it’s doing it the way it is.

  1. Gain Windows console access into the SolarWinds Orion server in your environment that you installed the ‘Database Manager’ component on during your initial setup. For most users, this will be the same server that you’re running your web frontend on.
  2. Navigate to Start -> SolarWinds Orion -> Database Manager, and launch it.
  3. Click on the ‘Add Orion Server’ button at the top of the program, and it will open the SQL instance for your primary SolarWinds Orion database.
  4. If you expand the ‘NPM’ dropdown, you’ll be presented with all of the table names for your Orion database.
  5. While you could spend hours digging through table names trying to find the information you’re looking for, most of the Node-based information you’d typically be looking to query off of is under the ‘NodesData’ table. Double-click on NodesData to open it up, and you’ll be presented with the SQL query view.

Let’s stop for a moment to explain what’s going to happen next here. You might be thinking, “Alright, we’ll just write some pretty simple query language such as the following and we’ll be set, right?”

SELECT Caption FROM NodesData WHERE IPAddress='10.1.1.1'

In terms of standard SQL, you’re not wrong. Matter of fact, that query will succeed in the window you’re looking at assuming that you replace ‘10.1.1.1’ with the IP of the Node you’re looking for. But, when you try to paste that query snippet into your alert email using a variable in place of the IP address field, it won’t run at all. Why?

The reason is that SolarWinds’ SQL query parser in their alerts can’t handle an alert variable as part of the query line when you put everything into a single-line single-part query. That’s a small mouthful of words; to explain it more simply, SolarWinds doesn’t like it when you have at least one variable call as part of the same query line that you have a ‘SELECT’ statement in.

To get around this, what you’d have to embed into your email alert to product the Caption value (aka, the name) of the neighbor peer would be the following:

${SQL:SELECT Caption FROM NodesData left join NodeIPAddresses on      NodesData.NodeID=NodeIPAddresses.NodeID where (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')}

So, what is this code actually doing? It’s doing a multi-table query in two different parts. We’re using the ‘left join’ statement to keep our Caption query on one side of the processing pile, then using the other side of the processing pile to search the NodeIPAddresses OR NodesData table for a variable that equates to our peer IP, then merge it back into the other side to get our result.

Using a ‘join’ function like this is the only way for SolarWinds to be able to do a variable-based SQL query as part of an email alert body. It’s a pretty long way around the issue, but it does work.

Putting everything together, below is a finished alert combining everything we’ve learned so far:

<html>
<head>
<style type="text/css"></style>
</head>
<body>

<table class='alert' style="border:3px solid #EBC800;border-collapse:collapse;font-family:Verdana;font-size:10pt;"><tr><th colspan="2" style="border-bottom:3px solid #EBC800;padding:15px;background-color:#FFF2A6;text-align:center;">

<b>SolarWinds - Routing Neighbor Down</b></th></tr>

<tr><td style="text-align:left;padding:5px">Node Name</td><td>${N=SwisEntity;M=Router.Nodes.DisplayName}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Node Location</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=SwisEntity;M=Router.Nodes.Location}</td></tr>

<tr><td style="text-align:left;padding:5px">Node Model</td><td>${N=SwisEntity;M=Router.Nodes.MachineType}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Neighbor IP Address</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=SwisEntity;M=NeighborIP}</td></tr>

<tr><td style="text-align:left;padding:5px">Neighbor Name</td><td>${SQL:SELECT Caption FROM NodesData left join NodeIPAddresses on NodesData.NodeID=NodeIPAddresses.NodeID where (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')}</A></td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Neighbor Location</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${SQL:SELECT Location FROM NodesData left join NodeIPAddresses on NodesData.NodeID=NodeIPAddresses.NodeID where (NodeIPAddresses.IPAddress='${N=SwisEntity;M=NeighborIP}' OR NodesData.IP_Address='${N=SwisEntity;M=NeighborIP}')}</td></tr>

<tr><td style="text-align:left;padding:5px">Full Node Details</td><td>${N=SwisEntity;M=Router.Nodes.DetailsUrl}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Full Alert Details</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=Alerting;M=AlertDetailsUrl}</td></tr>

<tr><td style="text-align:left;padding:5px">Acknowledge This Alert</td><td>${N=Alerting;M=AcknowledgeUrl}</td></tr>

<tr class='alt'><td style="background-color:#FFEC7D;text-align:left;padding:5px">Timestamp</td><td style="background-color:#FFEC7D;text-align:left;padding:3px">${N=Alerting;M=AlertTriggerTime;F=DateTime}</td></tr>

</table>
<br>

This message was generated by the SolarWinds alert: ${N=Alerting;M=AlertName}

</body>
</html>

Seeing things in action, our mock alert would look like this:

Picture

Once again, this could be expanded to include any other variables or database-side criteria you’d want. Now that you’ve seen a little bit of how to use the Database Manager for SolarWinds, you have the ability to find whatever values you want and write your own custom alerts.

As an addendum to this portion of the guide, one of the visitors to this site noted that the ‘Caption’ field that’s referenced in the SQL query is only one of the fields that can contain the full name of the Node you’re going to alert on. If you’re not seeing the full name of the Node, and are instead seeing just the IP address, check within the SolarWinds Database Manager in the NodesData table to see what column contains your human-readable Node name. Thanks for catching this, Pat!

Summary

To give credit to where credit is due, some of the code I’ve used in this article was based both from the SolarWinds alert template packages from their old archives, as well as from various conversations on the Thwack forums of users suggesting options for improving their own alerting. 

It’s my hope that this provides you with some insight into how to get better mileage from your SolarWinds alerting system, and how to find information to provide to your time that the stock installation of Orion might not have otherwise offered.

Caleb
Caleb Huggenberger is a 31 year-old systems engineer, owner of the non-profit animation streaming service 'Otaku Central', and Eastern culture enthusiast. Outside of long work days, he enjoys electronics engineering, cast iron campfire cooking, and homesteading on his acreage in the Indiana countryside.

Leave A Comment (please keep things clean & civil)

Your email address will not be published. Required fields are marked *