Enhance Alert Notifications with Custom Queries

by Chris Dodds24, Jan, 2023Alerting & Custom Alerts, Alerting & Incident Management, Blog Posts

In this blog, I wanted to highlight a configuration area of Alert definitions, I see customers do not take full advantage of; putting good quality information in the alert notification output and tuning alert triggers to match required scenarios.

We can use SWQL in many ways to help gather the data we need when the data out of the box is not hitting the spot.

Here we will be working through an alert that is using custom SWQL to find the trigger when a group member’s availability is below 75% then alert the team that needs to resolve. The default out the box alert, the notification is a basic email with some variables brought in about the issue. We would like to provide more detail regarding the members that are down so the alert is actionable immediately, rather than having to capture more details manually to determine next actions. This could be logging in to SolarWinds for the detail, the device/application etc. required to resolve.

Requirements

SWQL Studio
Modules Required to duplicate this blog are below but you can adjust and use other modules where necessary
NPM
SAM or SCM

Process

This alert is using SWQL as the trigger condition to find groups whose availability is below 75%, as it not always appropriate for 1 device out of x to be down or indeed all to be down.

SELECT Groups.Uri, Groups.DisplayName FROM Orion.Groups AS Groups
JOIN
(SELECT c.ContainerID, c.DATETIME, c.Container.Name, c.Status, c.PercentMembersAvailability, c.Container.InstanceType FROM Orion.ContainerStatus c
INNER JOIN
(select max(DateTime) as latestdate, [ContainerID] FROM orion.ContainerStatus
GROUP BY containerID ) SubMax
ON c.DateTime = subMax.latestdate AND c.ContainerID = SubMax.ContainerID
WHERE c.PercentMembersAvailability < 75 AND c.Container.InstanceType = 'Orion.Groups') results ON groups.ContainerID = results.ContainerID

This is currently finding the below groups in this environment

Applications
Application Status
Active Directory Status
IIS Status
Sophos Status
Windows Services

The email action part of this alert is currently the default from SolarWinds which doesn’t give any detail about the issue that was found, just links back to SolarWinds to work out which group is in a reduced state. We want this to be informative, so that when receiving this notification the receiver can immediately determine what the issue is and what a likely path to resolution may be. The current default content does not deliver on this, so lets improve it!

When you edit the email trigger action and go to the ‘Message’ bar, pick where you want to see more information I am currently adding to the top of the body then can adjust to where I want it afterwards. Then select ‘Insert Variable’

When dealing with custom queries in alert notifications each query can only pull back the equivalent of one cell of data. The trick is to create your query with everything you need in the alert notification then just adjust the t field straight after the word select. Best practice would be to remove all the additional headers to reduce the load of each query.

SWQL Studio as always when working with SWQL query generation, can be very useful as you can see the tables and run a live query to confirm the data returned. We use this as the go to for all of our query building, as it is so much easier to create, test and confirm before using, with another example of using SWQL in another of our blogs here.

What I would like to do is pull back information regarding the individual nodes that are causing the group availability to be below 70% and then specific details about those nodes. You will find when specifying the trigger condition object this can limits what preselected variables you can pull back in the trigger action. A benefit of creating a custom query is you’re not limited by the default variables, basically, if the data is in database you can create your SELECT statement and present the returned data in the way you require. The possibilities are indeed endless (slight exaggeration here, but you get my meaning).

So firstly we want the almost duplicate what we can do without using a custom query. We want the Node name from the ‘Container.members’ table, but we only want groups related to nodes so we have to use the where statement to narrow this down.

SELECT m.Name
FROM Orion.ContainerMembers m
where m.MemberEntityType = ‘Orion.Nodes’

The above will work in SWQL Studio and the alert, but we don’t have anything linking the above to the object that will trigger the warning. This is done by using a variable in which the alert will populate for the thing you use to return the filtered data relevant to the alert. In this case, the Alert Variable ‘Group ID’ is the key we add to the WHERE clause to filter the data we wish to include in our alert notification.

${N=SwisEntity;M=ContainerID}

Here is the full query to return the node devices in our affected Group.

SELECT m.Name
FROM Orion.ContainerMembers m
where m.MemberEntityType = ‘Orion.Nodes’ and
m.containerid = ${N=SwisEntity;M=ContainerID}

And back on the message side, we would just add ‘Nodes within Affected Group:’ before the script and then when you test the alert by simulating it.

It will go from this:

Nodes within Affected Group: ${N=SWQL;M= SELECT m.Name
FROM Orion.ContainerMembers m
where m.MemberEntityType = ‘Orion.Nodes’ and
m.containerid = ${N=SwisEntity;M=ContainerID}

To this:

Nodes within Affected Group: PROS-SERVER-Example

We can take this approach to bring back anything relatable from the database, where we just duplicate function for all the fields we want from the tables SWQL Entities attached or link further tables as we see fit.

A further example here utilising the SAM module and looking at the Asset Inventory data set, we have decided to include the last application installed, which in some alert definitions may be a useful piece of information:

Last Software Installed: ${N=SWQL;M=SELECT s.Name
FROM Orion.ContainerMembers m
LEFT JOIN orion.nodes n ON m.memberprimaryid = n.nodeid
LEFT JOIN Orion.AssetInventory.Software s ON n.NodeID = s.nodeid
WHERE m.containerid = ${N=SwisEntity;M=ContainerID}
ORDER BY s.InstallDate DESC}

Will show the last program installed

Last Install: Sophos Endpoint Defense

If you find this useful, look to add the Installation date to your alert notification content as well. I have given you a strong starting point here.

So something that looks very complicated at first glance becomes a simple repetitive process to get your end result from the below:

The group member availability is ${N=SwisEntity;M=ContainerStatus.PercentMembersAvailability} and one of those members is below:

Member Details

Node: ${N=SWQL;M=SELECT n.caption
FROM Orion.ContainerMembers m
left join orion.nodes n on m.memberprimaryid = n.nodeid
LEFT join Orion.AssetInventory.Software s on n.NodeID = s.nodeid