APM Threshold Alert

muckman

I want to build an alert variable that will state the threshold name when an APM component has triggered a warning or critical status such as CPU, Memory, Statistic etc. Shockingly there is no built in alert variable from SAM 6.2.4 for this purpose either. However the problem I'm running into is the limitation of the SQL macro. Apparently you can't use IF/ELSE logic or even multiple Selects. I wish StatusOrError condition would list threshold condition but it doesn't so I must build my own variable.

This is a snip of my Component Alert Email to give you context

Component: ${N=SwisEntity;M=ComponentAlert.ComponentName}

Status: ${N=SwisEntity;M=ComponentAlert.ComponentAvailability}

Details: ${N=SwisEntity;M=ComponentAlert.StatusOrErrorDescription}

Cause: {SQL: Select statement where the threshold name will be selected. If none is present this variable will be blank which is fine.}  "CPU Threshold"

1.) Where are the rules documented for the SQL macro?

2.) Is there a table in the SolarWindsOrion database that states which APM component threshold has been breached?

     2a.) If not I can calculate this from the APM_AlertsAndReportsData view but I'm limited by the single select where if the component has multiple threshold breaches only the first will be listed.

Sample SQL Variable

${SQL: SELECT Top 10 (SELECT 'Physical Memory Threshold' From APM_AlertsAndReportsData Where APM_AlertsAndReportsData.PercentMemory > APM_AlertsAndReportsData.[Threshold-PhysicalMemory-Warning] AND ComponentID=${N=SwisEntity;M=ComponentAlert.ComponentID}); (SELECT 'Virtual Memory Threshold' From APM_AlertsAndReportsData Where APM_AlertsAndReportsData.PercentVirtualMemory > APM_AlertsAndReportsData.[Threshold-VirtualMemory-Warning] AND ComponentID=${N=SwisEntity;M=ComponentAlert.ComponentID})} Repeat nested select statements for each type of threshold. When I build this in SSMS the query works but due to the limitations of the SQL macro in the Alert Action it only returns the first nested select. The expected output would be the name of the threshold(s) if breached.  "Physical Memory, Virtual Memory, CPU" etc

Any SQL help would be appreciated.

muckman

As usual I'm answering my own questions. I wrote this Select Case expression to build my own alert variable to state which component threshold has been breached. This is useful for when you have a component such as a Windows Service that can be in a warning, critical or down state. Instead of having to get specific with your alerts you can use a single alert definition to trigger on warning, critical or down and this SQL macro will tell you WHICH threshold has triggered the warning or critical status. Warning or Critical status comes from when you define a threshold on the component such as CPU, Memory, IO, Statistic or Response. This SQL macro will cover all of those thresholds.

You can even go one step further to add the current, warning, and critical values to the expression in addition to the threshold type.

Which Component Threshold has been Breached

SELECT CASE WHEN ([PercentCPU] > [Threshold-CPU-Warning]) OR ([PercentCPU] > [Threshold-CPU-Critical]) THEN 'CPU' WHEN ([PercentMemory] > [Threshold-PhysicalMemory-Warning]) OR ([PercentMemory] > [Threshold-PhysicalMemory-Critical]) THEN 'Physical Memory' WHEN ([PercentVirtualMemory] > [Threshold-VirtualMemory-Warning]) OR ([PercentVirtualMemory] > [Threshold-VirtualMemory-Critical]) THEN 'Virtual Memory' WHEN ([IOReadOperationsPerSec] > [Threshold-IOReadOperationsPerSec-Warning]) OR ([IOReadOperationsPerSec] > [Threshold-IOReadOperationsPerSec-Critical]) THEN 'IO Reads' WHEN ([IOWriteOperationsPerSec] > [Threshold-IOWriteOperationsPerSec-Warning]) OR ([IOWriteOperationsPerSec] > [Threshold-IOWriteOperationsPerSec-Critical]) THEN 'IO Writes' WHEN ([IOTotalOperationsPerSec] > [Threshold-IOTotalOperationsPerSec-Warning]) OR ([IOTotalOperationsPerSec] > [Threshold-IOTotalOperationsPerSec-Critical]) THEN 'IO Total' WHEN ([ResponseTime] > [Threshold-ResponseTime-Warning]) OR ([ResponseTime] > [Threshold-ResponseTime-Critical]) THEN 'Response Time' WHEN ([StatisticData] > [Threshold-Statistic-Warning]) OR ([StatisticData] > [Threshold-Statistic-Critical]) THEN 'Statistic' ELSE 'None' END AS ThresholdType FROM APM_AlertsAndReportsData WHERE ComponentID=${N=SwisEntity;M=ComponentAlert.ComponentID}

I still would like to see a document for the rules of SQL statements used in alerts.