Alert on difference in value of custom poller between 2 nodes

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

not sure i understand how to define the conditions in the CASE.  I need to create alert when the difference in the 2 values below in Display name is greater x.

So if you're using a custom SWQL alert, you may have noticed the SELECT portion gets predefined for you so a CASE statement would only be helpful if using it in a subquery or join etc. The conditions you need are fairly simple it seems but I have one question.

Do you need an alert when the top row records value (current value of 879 I see) is perhaps greater than X in difference from that of the bottoms rows value (I see a value of 857 currently)..?

If thats the case then I think some subqueries would need to be formulated for this to be able to appropriately compare the two values.

Yes, i am using the predefined select portion plus a few joins to get the table.  

Yes,  I need the difference between the 2 values, so "greater of the two values" minus "the lesser of the two values" (order doesn't matter as long as I get an absolute value)

Here is the query used to produce the table.

SELECT CustomPollerStatusOnNodeTabular.Uri, CustomPollerStatusOnNodeTabular.DisplayName,Name.CustomPollerName,adminState.[Status] AS [Admin Status],ObjStat.[Status] AS [Object Status] FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS CustomPollerStatusOnNodeTabular

INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS ObjStat ON ObjStat.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID

INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS adminState ON adminState.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID

INNER JOIN Orion.NPM.CustomPollerAssignment AS Name ON Name.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID

WHERE

Name.CustomPollerName = 'POLLER_NAME_HERE'

So you want to subtract 857 from 879 (= 22) and then alert if the result is greater than lets say 15?

This will be a little tricky as  sum_giais  explained, you can't change the predefined initial Select statement in the alert.

Typically you would do the subtraction within the Select statement. Anyone have any ideas?

So I can explore how I might approach this. To be honest though -- more work to be done here and I am left with more questions but I need to shift my focus.

Question 1: Are the 2 pollers you show in your screenshot the only 2 pollers you need to alert on this for? I ask because that would change the approach.

• Because the query for the custom pollers will return essentially 2 Uri's to alert on... you would possibly get 2 alerts for the same condition (one on each.) There's some crafty ways around this by limiting the alert to the specific poller Uri, or RowID etc. That would have its problems perhaps.

Question 2: Are these custom pollers assigned to the same node?

• With question 1 in mind and assuming these 2 pollers are assigned to the same node, I'd adjust my approach and actually create a custom SWQL alert on the node, join in the data relative to the node and then you only have 1 Uri (the node) that would alert.

Further thoughts... that SWQL query you provided seems to be using way to many joins than needed. Take a look at this query, and familiarize yourself with the data (it may be a lot). But you should be able to find the CustomPollerAssignmentID's, and the RowID's for your pollers. This first query would just be to "Get the data you need from SWQL"... not to use in an alert in anyway yet.

-- Update the CustomPollerAssignmentID, and RowID below
SELECT 
     p.Uri
    ,p.DisplayName
    ,p.AssignmentName
    ,p.CustomPollerAssignmentID
    ,p.CompressedRowID
    ,p.RowID
    ,p.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p
--WHERE p.CustomPollerAssignmentID = 'yourpollerassignment'
--AND RowID = rowidhere

Here's what I have so far in an attempt to put some subqueries together for your purpose, but then realized I had some glaring questions (above)... that would change this approach.

Note: this query is not 100% complete and would absolutely need to be tailored to your environment with your CustomPollerAssignmentID, and RowID's.etc. so don't expect this to work but  wanted to post it if it can help.

-- Update the CustomPollerAssignmentID, and RowID below
SELECT 
     p.Uri
    ,s1.Status
    ,s2.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p
INNER JOIN ( SELECT 
     p1.Uri
    ,p1.CustomPollerAssignmentID
    ,p1.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p1
WHERE ( p1.CustomPollerAssignmentID = 'yourpollerassignment'
AND p1.RowID = rowidhere ) ) AS s1 ON s1.Uri = p.Uri
LEFT JOIN ( SELECT 
     p2.Uri
    ,p2.CustomPollerAssignmentID
    ,p2.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p2
WHERE ( p2.CustomPollerAssignmentID = 'yourpollerassignment'
AND p2.RowID = rowidhere ) ) AS s2 ON s2.Uri = p.Uri

Let us know!

So I can explore how I might approach this. To be honest though -- more work to be done here and I am left with more questions but I need to shift my focus.

Question 1: Are the 2 pollers you show in your screenshot the only 2 pollers you need to alert on this for? I ask because that would change the approach.

• Because the query for the custom pollers will return essentially 2 Uri's to alert on... you would possibly get 2 alerts for the same condition (one on each.) There's some crafty ways around this by limiting the alert to the specific poller Uri, or RowID etc. That would have its problems perhaps.

Question 2: Are these custom pollers assigned to the same node?

• With question 1 in mind and assuming these 2 pollers are assigned to the same node, I'd adjust my approach and actually create a custom SWQL alert on the node, join in the data relative to the node and then you only have 1 Uri (the node) that would alert.

Further thoughts... that SWQL query you provided seems to be using way to many joins than needed. Take a look at this query, and familiarize yourself with the data (it may be a lot). But you should be able to find the CustomPollerAssignmentID's, and the RowID's for your pollers. This first query would just be to "Get the data you need from SWQL"... not to use in an alert in anyway yet.

-- Update the CustomPollerAssignmentID, and RowID below
SELECT 
     p.Uri
    ,p.DisplayName
    ,p.AssignmentName
    ,p.CustomPollerAssignmentID
    ,p.CompressedRowID
    ,p.RowID
    ,p.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p
--WHERE p.CustomPollerAssignmentID = 'yourpollerassignment'
--AND RowID = rowidhere

Here's what I have so far in an attempt to put some subqueries together for your purpose, but then realized I had some glaring questions (above)... that would change this approach.

Note: this query is not 100% complete and would absolutely need to be tailored to your environment with your CustomPollerAssignmentID, and RowID's.etc. so don't expect this to work but  wanted to post it if it can help.

-- Update the CustomPollerAssignmentID, and RowID below
SELECT 
     p.Uri
    ,s1.Status
    ,s2.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p
INNER JOIN ( SELECT 
     p1.Uri
    ,p1.CustomPollerAssignmentID
    ,p1.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p1
WHERE ( p1.CustomPollerAssignmentID = 'yourpollerassignment'
AND p1.RowID = rowidhere ) ) AS s1 ON s1.Uri = p.Uri
LEFT JOIN ( SELECT 
     p2.Uri
    ,p2.CustomPollerAssignmentID
    ,p2.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p2
WHERE ( p2.CustomPollerAssignmentID = 'yourpollerassignment'
AND p2.RowID = rowidhere ) ) AS s2 ON s2.Uri = p.Uri

Let us know!

sum_giais , thank you for your attention to this. to answer your questions:

1. The screenshot shows results for 2 nodes for one poller.  This poller will only be assigned to these 2 nodes

2. No, there is only one custom poller that we are interested in.  this poller is assigned to 2 nodes.

As bobmarley mentioned, i have to start my query with the predefined query:

"SELECT CustomPollerStatusOnNodeTabular.Uri, CustomPollerStatusOnNodeTabular.DisplayName,Name.CustomPollerName,adminState.[Status] AS [Admin Status],ObjStat.[Status] AS [Object Status] FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS CustomPollerStatusOnNodeTabular"

Ok, I think I have something that you could work with.

So this query is something that will need to be tailored to your environment so I'll provide you some steps to do so...

1. Use the 1st query to find the 2 unique CustomPollerAssignmentID's for each poller
2. Use the 1st query to find the RowID for the poller (since this is a tabular poller, RowID's refer to the individual value monitored on the table data in SNMP, this is probably going to be the same on each.
3. Update the text for 2nd Query inside the single quotes for each appropriate CustomPollerAssignmentID, each one should be referenced twice, lines 21, 23, 34 and 36
4. Update the text for the RowID in the 2nd Query, lines 20, 22, 35 and 37
5. Test that the query works manually in SWQL studio without the very bottom WHERE clause

1st Query:

SELECT 
     p.Uri
    ,p.DisplayName
    ,p.AssignmentName
    ,p.CustomPollerAssignmentID
    ,p.CompressedRowID
    ,p.RowID
    ,p.Status
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS p

2nd Query:

SELECT CustomPollerStatusOnNodeTabular.Uri, CustomPollerStatusOnNodeTabular.DisplayName FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS CustomPollerStatusOnNodeTabular
--
-- The alert condition only will require lines 8 and beyond, however
-- make sure to follow the steps provided to update this query to work
-- for your environment with your custom pollers, and test that it in
-- SWQL Studio prior to throwing in an alert
--
LEFT JOIN ( SELECT TOP 1
     stat1.Uri
    ,stat1.CustomPollerAssignmentID
    ,CASE WHEN stat1.RawStatus > stat2.RawStatus THEN (stat1.RawStatus - stat2.RawStatus)
          ELSE (stat2.RawStatus - stat1.RawStatus)
     END AS [Difference1]
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS stat1
INNER JOIN ( SELECT
     substat2.CustomPollerAssignmentID
    ,substat2.RowID
    ,substat2.RawStatus
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS substat2 ) AS stat2 ON ( stat2.CustomPollerAssignmentID = '<CustomPollerAssignmentID-#1>'
AND stat2.RowID = <ROWID-ForStatus> )
WHERE ( stat1.CustomPollerAssignmentID = '<CustomPollerAssignmentID-#2>' 
AND stat1.RowID = <ROWID-ForStatus> ) ) AS diff1 ON diff1.Uri = CustomPollerStatusOnNodeTabular.Uri
LEFT JOIN ( SELECT TOP 1
     stat3.Uri
    ,stat3.CustomPollerAssignmentID
    ,CASE WHEN stat3.RawStatus > stat4.RawStatus THEN (stat3.RawStatus - stat4.RawStatus)
          ELSE (stat4.RawStatus - stat3.RawStatus)
     END AS [Difference2]
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS stat3
INNER JOIN ( SELECT
     substat4.CustomPollerAssignmentID
    ,substat4.RowID
    ,substat4.RawStatus
FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS substat4 ) AS stat4 ON ( stat4.CustomPollerAssignmentID = '<CustomPollerAssignmentID-#2>'
AND stat4.RowID = <ROWID-ForStatus> )
WHERE ( stat3.CustomPollerAssignmentID = '<CustomPollerAssignmentID-#1>'
AND stat3.RowID = <ROWID-ForStatus> ) ) AS diff2 ON diff2.Uri = CustomPollerStatusOnNodeTabular.Uri
WHERE ( diff1.Difference1 > 0 OR diff2.Difference2 > 0 )

End Result if you add in the diff1.Difference1 and diff2.Difference2 fields into the select

Nice work! @sum_giais 

Thank you for your attention to this, amazing work!.  I have not been able to test this out yet.  i will respond when i do

cool sounds good... thank me when it works LOL 

I think the only catch with this is, unless you limit the alert to a specific node or poller you are going to end up with an alert on both (2x alerts) because they both will have a difference > 0 no matter what way you look at it. Is that the desired result?

Thats more a limitation on the alerting engine. It'd be great if we had the ability to put a true custom swql or sql alert by defining the exact select statement. Feature request!

You are awesome!  this works great.  Thank you!!!

to resolve the 2X alerts, we removed one of the diffs at the end since we only have one poller, 2 nodes and absolute value in the diff is all we need