Select Page

Enhance Alert Notifications with Custom Queries

by 24, 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.

Alert Trigger Condition Image 1

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!

Alert Simulate Action Image 2

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’

Configure Action Email Alert Image 3

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}
Select Variable Alert Image 4

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

where m.containerid = ${N=SwisEntity;M=ContainerID}
order by s.InstallDate DESC}

Last Logged in User: ${N=SWQL;M=SELECT i.lastloggedinuser
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
LEFT join Orion.AssetInventory.ServerInformation i on n.NodeID = i.nodeid

where m.containerid = ${N=SwisEntity;M=ContainerID}
order by s.InstallDate DESC}

Operating System: ${N=SWQL;M=SELECT i.operatingsystem
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
LEFT join Orion.AssetInventory.ServerInformation i on n.NodeID = i.nodeid

where m.containerid = ${N=SwisEntity;M=ContainerID}
order by s.InstallDate DESC}

Manufacturer: ${N=SWQL;M=SELECT i.manufacturer
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
LEFT join Orion.AssetInventory.ServerInformation i on n.NodeID = i.nodeid

where m.containerid = ${N=SwisEntity;M=ContainerID}
order by s.InstallDate DESC}

Device Type: ${N=SWQL;M=SELECT i.devicetype
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
LEFT join Orion.AssetInventory.ServerInformation i on n.NodeID = i.nodeid

where m.containerid = ${N=SwisEntity;M=ContainerID}
order by s.InstallDate DESC}

Last Install: ${N=SWQL;M=SELECT s.Name, m.ContainerID, m.MemberPrimaryID, m.Name, m.Status, m.EntityDisplayName, m.DetailsUrl, s.InstallDate
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}

Last Install Date: ${N=SWQL;M=SELECT s.InstallDate,s.Name, m.ContainerID, m.MemberPrimaryID, m.Name, m.Status, m.EntityDisplayName, m.DetailsUrl, s.InstallDate
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}

View full object details here: {N=SwisEntity;M=DetailsUrl}
View full alert details here: {N=Alerting;M=AlertDetailsUrl}
Click here to acknowledge the alert: {N=Alerting;M=AcknowledgeUrl}

To be emailed like the below:

Simulate Action Email Alert Image 5

This makes the alert actionable without having to click multiple links, I can tell who was the last person logged onto the system, what and when the last software was installed if its virtual or physical all great information to find out who or what has caused this member of the group to be in a down state.

This can be used in a variety of ways with different trigger types and shows you are not limited by out of the box restrictions, the possibilities are endless and so you really can make the alert output meaningful and of significant value to those receiving the alert notification.

Hopefully you found this insightful and useful to apply to your own SolarWinds environment. However, if you require further guidance, use the links below to enquire about our Professional Services offering.

Consultancy: SolarWinds Customisation Services

Chris Dodds

Chris Dodds

Senior SolarWinds Engineer

Chris Dodds is a Senior SolarWinds Engineer at Prosperon Networks, and a SolarWinds THWACK MVP. As a SolarWinds Engineer, Chris helps customers meet their IT monitoring requirements with SolarWinds.

Consultancy: SolarWinds Customisation Services

Related Insights From The Prosperon Blog
Not found
Share This