Alert depending on downstream host UP

I've had an alerting issue thrown my way that I'm not sure how to solve.

SQL server running jobs - let's call the server SQL1. Running a regular SQL job that I'll call Job1.

A downstream server named Server1 consumes the results of Job1.

I've got an application watching the Sql jobs that goes critical if Job1 fails.

I want an alert if my SQL Job monitor shows Job1 fails - that's the easy part.

Now the hard part. If Server1 goes offline then Job1 will always fail. I don't want the SQL Job alert to fire if Server1 is offline.

Wondering if we have a way to set up a trigger condition that fires if the SQL Job1 monitor goes Critical, BUT ONLY IF Server1 is UP. If Server1 is not up then the Sql job alert will not fire.

I've been looking at Advanced Alerting Conditions and Secondary Trigger Actions, however not sure if I understand how to set that up.

Thanks in advance for any inputs from the community.

Find more posts tagged with

advanced_alerts

secondary

Alerts

Accepted answers

All comments

monitoringlife

If you have an eye for SWIS/SQL I would configure the alert close to how you want it, then drop it to SWQL via the menu option then look at it there (or modify as needed, then just make the alert a SWQL alert).

Far right side of the trigger conditions builder, click the chevron, 'show SWQL', copy/paste into SWQL Studio

Also it can help to toggle between the alert context (I want to alert on) between node and the table your interested in (application / app-insight for SQL), as some variables are exposed in one and not the other

robert777

Thank you! Looking at my alert SWQL now.

Just wondering, would I include my 'extra' server reference as a subquery, something like

[existing query] AND

(Select n.Caption, n.Status from Orion.Nodes n WHERE n.Caption LIKE 'server1' AND n.Status LIKE 'Up')

[careful about parenthesis]

If I follow correctly the subquery would have to evaluate to something other than NULL for the SWQL to evaluate to TRUE. If the alert SWQL does not evaluate to TRUE then the alert will not fire.

Let me know if I am on the right track. Thanks for your help.

robert777

After some research and brainshare with teammates I believe we have an answer:

Manage alerts >> trigger condition >> click the box for 'Advanced options - enable complex conditions'. If I follow correctly this allows a secondary trigger condition with logic essentially separate from the main trigger condition.

something like, alert when...

MyApplication running on MyDbServer goes Critical condition

AND

MyDependentAppServer is NOT down and NOT unreachable

Developing and testing now but I think this will work.

Thank you for the input from the community.

robert777

Greetings all.

With the help of my teammates at work and input from the Thwack community I believe we have developed a working solution.

The problem can be solved using Alert Trigger Complex Conditions. The solution does not require SWQL coding (although for some modifying background SWQL is easier/faster).

Business problem: need to alert if a specific SQL job fails. However, the SQL app has a number of servers running dependent services. If a dependent server is down then the SQL job will always fail. Do not alert if the SQL job fails due to dependent servers being down.

Solution:

Create a Solarwinds group. Add the dependent servers to the group (sample name 'My New Group'.
Create an alert to check for SQL job completion (details not covered here).
Alert Trigger Conditions tab - check the box near the bottom to "Enable complex conditions..." (screenshot 1).
Add a new section to trigger condition ("secondary condition").
Set the secondary condition to key on the Group object, with your group name as the scope, trigger conditions look at Group Member status 'is not' various down or unavailable conditions (screenshot 2).

Note that in my shop we are still testing this alert configuration. However, so far it looks like the secondary condition as configured will suppress a failed SQL job alert when any group member server is in a down/unavailable/unmanaged/unreachable state.

Any input from the community is welcomed. Please post if you see anything that looks like a bug in the solution. Cheers.

SteveK

You maybe also able to get away with setting an a dependcy on the application (All Settings -> Manage Dependencies). In this scenario, the parent would be Node - Server1 and the child would be Application - SQL1.

I haven't built out dependencies on applications like that, but in theory it could work. I know for our nodes, if a parent goes down the child nodes go into an unreachable state. Our alerts are looking specifically for the Down state, so unreachable does not trip.

robert777

Thank you!

Actually we found a solution that does not require testing the "group member" object. As follows:

add the dependent servers to a new Solarwinds group
set the group rollup status to 'worst case', meaning that the poorest condition of any group member will be reflected in the group condition (i.e. if one group member shows Critical and other group members show green, group condition is Critical).
test group status in the secondary alert condition; if group status is Down or Critical expression tests False and the alert does not fire.

Testing this configuration appears to work as expected. In fact a couple of actual alerts have fired for these servers and the alert criteria looks valid.

Thanks again for all the inputs from the community.