Is it possible to query alert configurations by application monitor name?

In the days before Orion 11.5 and SAM 6.2.2 we used to do a DB query of the AlertDefinitions table to pull a list of alerts for a specific application or component monitor by checking for the name in the TriggerQuery column. Since upgrading, not only is the table different (AlertConfigurations) but the Trigger column (the equivalent of the aforementioned TriggerQuery column) now contains XML instead of T-SQL, but decoding that XML into something that looks like SQL or even SWQL is blowing my mind.

Here's my simple requirement:

Given the name of a application monitor (I'll even forego the component level query!), is there a query (SQL or SWQL) that I can run that will return all of the alerts that are scoped with that application monitor.

Help me Thwacksters, you are my only hope.

Find more posts tagged with

query

sam

sql

SWQL

Alerts

Accepted answers

lukas.belza

Hello,

If you have trigger condition like this

Then you can use following query to get what you need:

SELECT * FROM AlertConfigurations WHERE [Trigger] LIKE '%ApplicationName%Windows Server 2003-2012 Services and Counters%ComponentName%Disk Queue Length%'

Basically you can use only

SELECT * FROM AlertConfigurations WHERE [Trigger] LIKE '%Disk Queue Length%'

but example above is useful if you have more alerts with similar trigger conditions.

Lukas

All comments

jbiggley

Alright, I guess it is time to start baiting the big guns to weigh in here. Maybe adatole or aLTeReGo know someone who can answer this question?

jamison.jennings

Well, I'm no big gun, but I'm not afraid to take a stab.

Select AlertName

from dbo.AlertDefinitionsView

where Triggerquery like '%Application Name%'

lukas.belza

Hello,

If you have trigger condition like this

Then you can use following query to get what you need:

SELECT * FROM AlertConfigurations WHERE [Trigger] LIKE '%ApplicationName%Windows Server 2003-2012 Services and Counters%ComponentName%Disk Queue Length%'

Basically you can use only

SELECT * FROM AlertConfigurations WHERE [Trigger] LIKE '%Disk Queue Length%'

but example above is useful if you have more alerts with similar trigger conditions.

Lukas

jbiggley

Unfortunately, the AlertDefinitionsView uses the data from the AlertDefinitions table and that table was deprecated in SAM 6.2.2 and Orion 2015.1.2. To validate that data, perform the following query against your SolarWinds Orion DB:

SELECT TOP 10 AlertName, LastExecuteTime

FROM AlertDefinitionsViews

ORDER BY LastExecuteTime DESC

You should see all of the times on, or near, the time you upgraded from Orion 2014.2.1 to Orion 2015.1.2 (done when you upgraded NPM, SAM or both)

jbiggley

Oh man! That's it. I was intimidated by the XML and didn't even see the names of the application monitors when I pasted it into Notepad++

Sweet!

jamison.jennings

-1 for me for getting it wrong.

-10 for SolarWinds for not cleaning up the database when they deprecate tables.