Trying to build SWQL alert for value more than 650 from a Modern Dashboard widget

Hey Folks - I want to nest this swql below in the alert swql trigger condition, but the SELECT statement is pre hard-coded within the alert trigger condition. It is basically monitoring a sum of 10 values then checking if the sum value is greater that 650

SELECT value FROM
(SELECT SUM(data.NumericData) as value
FROM Orion.APM.MultipleStatisticData data
WHERE data.Name != 'Offline'
AND data.ComponentID = '12345'
AND data.NumericData is not NULL)
WHERE value > 650;

Many thanks

Find more posts tagged with

trigger

sam

select

trigger_conditions

sql

trigger alert

sum

SWQL

Modern Dashboards

apm

npm

Alerts

Accepted answers

mesverrum

Looking at your earlier query I believe this would work

SELECT Component.Uri, Component.DisplayName
FROM Orion.APM.Component AS Component

JOIN orion.APM.MultipleStatisticData data on data.ComponentID = component.ComponentID
WHERE data.ComponentID = '1234'
AND data.Name != 'Offline'
AND (SELECT SUM(data.NumericData) as value
FROM Orion.APM.MultipleStatisticData data
WHERE data.ComponentID = '1234'
AND data.Name != 'Offline') > 650

All comments

mesverrum

You need to write your custom query in such a way that it can be joined to the pre defined select statement that they give, because the data they are selecting is the stuff the alert engine needs in order to actually create an alert.

Not at my desk today to be able to hammer out the exact sql syntax, but it looks like it would be an easy join to what you have, although in the interest of better sql query writing i'd rewrite it with a group by and a having statement instead of nesting the way you have.

adam.beedell

You could RIGHT JOIN to that using an ID from the subquery.

But this also looks super inefficient as is, maybe cut it down with what the template is you're looking for or something first

lloyd267

Thank you for your reply & advise. I think i have made a start here with my new SWQL below but I'm struggling to progress it

SELECT Component.Uri, Component.DisplayName
FROM Orion.APM.Component AS Component
RIGHT JOIN orion.APM.MultipleStatisticData data on data.ComponentID = component.ComponentID
WHERE data.ComponentID = '1234'
AND data.Name != 'Offline'
AND data.NumericData =
(SELECT SUM(data.NumericData) as value
FROM Orion.APM.MultipleStatisticData data
WHERE data.ComponentID = '1234'
AND data.Name != 'Offline')

mesverrum

Looking at your earlier query I believe this would work