Hello friends,
I have recently tried to create a Custom SWQL Alert based on the following query with data about Interfaces and traffic.
I have 2 queries in the alert
1. In trigger condition
2. In the Alert Message (trigger action)
The Alerts works fine on some cases and in some cases it is not generated,
instead- I see the query itself , the query No. 2 (Alert Message)
These are the queries:
1. trigger:
SELECT Interfaces.Uri, Interfaces.DisplayName FROM Orion.NPM.Interfaces AS Interfaces
INNER JOIN
(SELECT t1.InterfaceID, IF.FullName
FROM
Orion.NPM.InterfaceTraffic t1
JOIN Orion.NPM.InterfaceTraffic t2
ON t1.InterfaceID= t2.InterfaceID
AND MinuteDiff(ToLocal(t1.DateTime),GETDATE()) >= 0
AND MinuteDiff(ToLocal(t1.DateTime),GETDATE()) < 40
AND MinuteDiff(ToLocal(t2.DateTime),GETDATE()) >= 90
AND MinuteDiff(ToLocal(t2.DateTime),GETDATE()) < 150
JOIN Orion.NPM.InterfaceTraffic t3
ON t1.InterfaceID= t3.InterfaceID
AND MinuteDiff(ToLocal(t3.DateTime),GETDATE()) >= 150
AND MinuteDiff(ToLocal(t3.DateTime),GETDATE()) < 210
JOIN Orion.NPM.InterfaceTraffic t4
ON t1.InterfaceID= t4.InterfaceID
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) >= 210
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) < 270
JOIN Orion.NPM.InterfaceTraffic t8
ON t1.InterfaceID= t4.InterfaceID
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) >= 450
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) < 510
JOIN Orion.NPM.Interfaces AS IF
ON
t1.InterfaceID = IF.InterfaceID
JOIN Orion.NPM.InterfacesCustomProperties ICP
ON t1.InterfaceID = ICP.InterfaceID
AND ( CASE WHEN t8.OutAveragebps !=0 THEN
( ((t8.OutAveragebps- t1.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t2.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t3.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t4.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND (ICP.Monitor_Port_Difference = '1') ) AS S1
ON S1.InterfaceID = Interfaces.InterfaceID
2.Trigger Message(Problematic) query:
${N=SWQL; M=SELECT (CASE WHEN t8.OutAveragebps !=0 THEN
( ((t8.OutAveragebps- t4.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END ) AS Out_BPS_Change
FROM
Orion.NPM.InterfaceTraffic t1
JOIN Orion.NPM.InterfaceTraffic t2
ON t1.InterfaceID= t2.InterfaceID
AND MinuteDiff(ToLocal(t1.DateTime),GETDATE()) >= 0
AND MinuteDiff(ToLocal(t1.DateTime),GETDATE()) < 40
AND MinuteDiff(ToLocal(t2.DateTime),GETDATE()) >= 90
AND MinuteDiff(ToLocal(t2.DateTime),GETDATE()) < 150
JOIN Orion.NPM.InterfaceTraffic t3
ON t1.InterfaceID= t3.InterfaceID
AND MinuteDiff(ToLocal(t3.DateTime),GETDATE()) >= 150
AND MinuteDiff(ToLocal(t3.DateTime),GETDATE()) < 210
JOIN Orion.NPM.InterfaceTraffic t4
ON t1.InterfaceID= t4.InterfaceID
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) >= 210
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) < 270
JOIN Orion.NPM.InterfaceTraffic t8
ON t1.InterfaceID= t4.InterfaceID
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) >= 450
AND MinuteDiff(ToLocal(t4.DateTime),GETDATE()) < 510
JOIN Orion.NPM.Interfaces AS IF
ON
t1.InterfaceID = IF.InterfaceID
AND t1.InterfaceID= ${SwisEntity; M=InterfaceID}
JOIN Orion.NPM.InterfacesCustomProperties ICP
ON t1.InterfaceID = ICP.InterfaceID
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t1.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t2.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t3.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold)
AND ( CASE WHEN t8.OutAveragebps !=0 THEN ( ((t8.OutAveragebps- t4.OutAveragebps)/t8.OutAveragebps) * 100 )
ELSE 0 END )>ICP.Out_BPS_Change_Threshold) } %
Explanation:
I perform a JOIN of the same table to itself 5 times based on a variable
The purpose of the query is basically to compare between 4 time points, and the fifth
In order to find a continuous change in the BPS of the interfaces over time
Each variable Tx indicates a time point x minutes ago
All logical comparisons are made against a reference point which is t8
And I check the value that is calculated against the value of a fixed custom property called Out_BPS_Change_Threshold and therefore there is another JOIN
to the NPM.InterfacesCustomProperties table
The query before was much longer, but I shortened the part of the SELECT and changed the order between the ANDs and currently I am left with this query that works some times and some times it doesn't.
As I wrote, the message query is sometimes showing values and sometimes not:
as I have seen from the logs,
the query may have reached timeout,
but I am not sure if this is what is causing the issue.
I would appreciate any advice on how to fix this\prevent this from happening
Thanks in advance,
Omri
