nested Select in a SQL variable?

Question

I'm trying to get a variable that indicates if the threshold has been baselined.  I used this post to help, which is awesome (APM Threshold Alert ) but it doesn't appear that nested Select statements are valid in SQL variables, example below:

The below works in SQL management studio really well but when plugged into an alert email as a SQL variable it errors out and just spits back the SQL query in the email...

I used "useBaseline " here to let support teams know its a baselined component.  I was also wanting to use "ComputeBaseline" to indicate it had been turned on as well as "BaselineApplyError" if there were any errors calculating the baseline, but looks like I may be out of luck

SELECT useBaseline FROM [dbo].[APM_Threshold] where ID = ${N=SwisEntity;M=ComponentAlert.ComponentID} and istemplate = 0 and thresholdname = (SELECT CASEWHEN ([PercentCPU] > [Threshold-CPU-Warning]) OR ([PercentCPU] > [Threshold-CPU-Critical]) THEN 'CPU'WHEN ([PercentMemory] > [Threshold-PhysicalMemory-Warning]) OR ([PercentMemory] > [Threshold-PhysicalMemory-Critical]) THEN 'PMem'WHEN ([PercentVirtualMemory] > [Threshold-VirtualMemory-Warning]) OR ([PercentVirtualMemory] > [Threshold-VirtualMemory-Critical]) THEN 'VMem'WHEN ([IOReadOperationsPerSec] > [Threshold-IOReadOperationsPerSec-Warning]) OR ([IOReadOperationsPerSec] > [Threshold-IOReadOperationsPerSec-Critical]) THEN 'IOReadOperationsPerSec'WHEN ([IOWriteOperationsPerSec] > [Threshold-IOWriteOperationsPerSec-Warning]) OR ([IOWriteOperationsPerSec] > [Threshold-IOWriteOperationsPerSec-Critical]) THEN 'IOWriteOperationsPerSec'WHEN ([IOTotalOperationsPerSec] > [Threshold-IOTotalOperationsPerSec-Warning]) OR ([IOTotalOperationsPerSec] > [Threshold-IOTotalOperationsPerSec-Critical]) THEN 'IOTotalOperationsPerSec'WHEN ([ResponseTime] > [Threshold-ResponseTime-Warning]) OR ([ResponseTime] > [Threshold-ResponseTime-Critical]) THEN 'Response'WHEN ([StatisticData] > [Threshold-Statistic-Warning]) OR ([StatisticData] > [Threshold-Statistic-Critical]) THEN 'StatisticData'ELSE 'None' END AS ThresholdTypeFROM APM_AlertsAndReportsDataWHERE ComponentID=${N=SwisEntity;M=ComponentAlert.ComponentID})

njoylif · Answer

That's a nice way to do it...I'll have to chew on that. I've been going down a similar road. Hard to read, but... I've had problems with nesting and using "IS NULL" logic. good times.

Component: ${SQL:Select CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN '' WHEN ${N=SwisEntity;M=Status} in (2,14) THEN '' ELSE '' END}${N=SwisEntity;M=ComponentAlert.ComponentName} App: ${N=SwisEntity;M=Application.ApplicationAlert.ApplicationName}
Stats: ${SQL:Select CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN '' WHEN ${N=SwisEntity;M=Status} in (2,14) THEN '' ELSE '' END}${N=SwisEntity;M=ComponentAlert.StatisticData} Threshold: ${SQL:Select CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN '' WHEN ${N=SwisEntity;M=Status} in (2,14) THEN '' ELSE '' END}${SQL:select warning=REPLACE((CASE WHEN t.warning IS NOT NULL THEN CONVERT(nvarchar(50),t.warning)+' (T.Override)' WHEN t.warning IS NULL THEN CONVERT(nvarchar(50),t2.warning) ELSE '' END),1.79769e+308,'NotSet') from APM_Component c WITH (NOLOCK) left join APM_Threshold t WITH (NOLOCK) on c.id=t.id and t.ThresholdName like 'Stat%' left join APM_Threshold t2 WITH (NOLOCK) on c.TemplateID=t2.id and t2.ThresholdName like 'Stat%' where (c.ID=${N=SwisEntity;M=ComponentAlert.ComponentID} OR c.ID is null)}
${SQL:SELECT CASE WHEN '${N=SwisEntity;M=ComponentAlert.ResponseTime}'='' THEN '' ELSE CONCAT('Response Time: ','${SQL:Select CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN '' WHEN ${N=SwisEntity;M=Status} in (2,14) THEN '' ELSE '' END}','${N=SwisEntity;M=ComponentAlert.ResponseTime}') END} ${SQL:SELECT CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN (CASE WHEN ('${N=SwisEntity;M=ComponentAlertThresholds.ThresholdResponseTimeWarning}'='') THEN 'Threshold: Not Set' ELSE CONCAT('Threshold: ','${N=SwisEntity;M=ComponentAlertThresholds.ThresholdResponseTimeWarning}') END) WHEN ${N=SwisEntity;M=Status} in (2,14) THEN (CASE WHEN ('${N=SwisEntity;M=ComponentAlertThresholds.ThresholdResponseTimeCritical}'='') THEN 'Threshold: Not Set' ELSE CONCAT('Threshold: ','${N=SwisEntity;M=ComponentAlertThresholds.ThresholdResponseTimeCritical}') END) ELSE '' END}
ComponentMsg: ${SQL:SELECT CASE WHEN ${N=SwisEntity;M=Status} in (3) THEN '' WHEN ${N=SwisEntity;M=Status} in (2,14) THEN '' ELSE '' END}${ComponentMessage}

Node: ${Node.Sysname} DNS: ${Node.DNS} IP: ${Node.IP_Address} BCC'd: ${SQL:select CASE WHEN Dept='RHPSAS' and TicketEmail is not null THEN TicketEmail WHEN Dept='DataServices' and TicketEmail is not null THEN TicketEmail ELSE '' END from APM_ApplicationCustomProperties where ApplicationID=${N=SwisEntity;M=Application.ApplicationID}}

ryan.davis26 · Answer

Actually, nevermind!! This DOES work, turns out I had an HTML error that was keeping the SQL variables from working! woohoo!