Can't make Alert based on SWQL

Jabyonok

Hi team,

I have simple SWQL request which returns me list of nodes where Route Flaps happened.

I would like to create the respective alert based on the query to get immediate email notifications.  

However, whatever I choose in the I want to alert on: menu with Custom SWQL alert, it contains already some "SELECT" requests which I can't remove. There is no an empty template. WTF?

It's like I have to invent SQL joins now to match variables.

Is there an easy way?

My request is below and it works perfectly well, I just want to get an Alert if it returns something different from empty.

SELECT
r.NodeID AS NodeID,
N.Caption AS NodeName,
r.RouteDestination AS Destination,
r.CIDR AS CIDR,
COUNT(*) AS FlapCount,
MIN(r.DateTime) AS FirstFlap,
MAX(r.DateTime) AS LastFlap
FROM Orion.Routing.RoutingTableFlap r
JOIN Orion.Nodes N ON r.NodeID = N.NodeID
WHERE r.DateTime >= AddDate('MINUTE', -30, GETDATE())
GROUP BY r.NodeID, N.Caption, r.RouteDestination, r.CIDR
HAVING COUNT(*) >= 3
ORDER BY FlapCount DESC

Any ideas, Gents?

Thanks,

Jack aus Kassel

cnorborg

You can rework things to give you some approximation.    Something like this?

<strong><span class="s5">SELECT</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Uri</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">DisplayName&nbsp;</span><span class="s5">FROM</span><span class="s0"> </span><span class="s11">Orion</span><span class="s10">.</span><span class="s11">Nodes</span><span class="s0"> </span><span class="s5">AS</span><span class="s0"> </span><span class="s11">Nodes</span></strong>

<span class="s5">JOIN</span><span class="s0"> </span><span class="s11">Orion</span><span class="s10">.</span><span class="s11">Routing</span><span class="s10">.</span><span class="s11">RoutingTableFlap</span><span class="s0"> </span><span class="s11">R</span><span class="s0"> </span><span class="s5">ON</span><span class="s0"> </span><span class="s10">(</span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">NodeID</span><span class="s0"> </span><span class="s10">=</span><span class="s0"> </span><span class="s11">R</span><span class="s10">.</span><span class="s11">NodeID</span><span class="s10">)</span><span class="s0"> </span>

<span class="s5">WHERE</span><span class="s0"> </span><span class="s11">r</span><span class="s10">.</span><span class="s19">DateTime</span><span class="s0"> </span><span class="s10">>=</span><span class="s0"> </span><span class="s19">AddDate</span><span class="s10">(</span><span class="s7">&#39;MINUTE&#39;</span><span class="s10">,</span><span class="s0"> </span><span class="s4">-30</span><span class="s10">,</span><span class="s0"> </span><span class="s19">GETDATE</span><span class="s10">())</span><span class="s0"> </span>

<span class="s5">GROUP</span><span class="s0"> </span><span class="s5">BY</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Caption</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Uri</span><span class="s10">,</span><span class="s0"> </span><span class="s11">r</span><span class="s10">.</span><span class="s11">RouteDestination</span><span class="s10">,</span><span class="s0"> </span><span class="s11">r</span><span class="s10">.</span><span class="s11">CIDR</span><span class="s0"> </span>

<span class="s5">HAVING</span><span class="s0"> </span><span class="s20">COUNT</span><span class="s10">(*)</span><span class="s0"> </span><span class="s10">>=</span><span class="s0"> </span><span class="s4">3</span><span class="s0"> </span>

<span class="s5">ORDER</span><span class="s0"> </span><span class="s5">BY</span><span class="s0"> </span><span class="s20">COUNT</span><span class="s10">(*)</span><span class="s0"> </span><span class="s11">DESC</span>

You'll note the original query doesn't change.  But I put basically your join after it, as well as your conditions.  What are you missing?   A bunch of variables that would normally "show up" when you do the query.   Thinking there might be a way to get them back possibly through Macros/Alert Variables, but not sure what you can do with things like the count?   You might have to use the built in relations also, never done that so not sure if they work in this sort of thing.  ie: You can get to the RoutingTableFlap variables by doing a  "Nodes.Router.RoutingTableFlap.DateTime" for example.  Why didn't I do it in the query above?   Probably could have?  You'd end up with this which gets me the same results as both prior queries...

<span class="s5">SELECT</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Uri</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">DisplayName</span><span class="s0"> </span><span class="s5">FROM</span><span class="s0"> </span><span class="s11">Orion</span><span class="s10">.</span><span class="s11">Nodes</span><span class="s0"> </span><span class="s5">AS</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s0"> </span>

<span class="s5">WHERE</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Router</span><span class="s10">.</span><span class="s11">RoutingTableFlap</span><span class="s10">.</span><span class="s19">DateTime</span><span class="s0"> </span><span class="s10">>=</span><span class="s0"> </span><span class="s19">AddDate</span><span class="s10">(</span><span class="s7">&#39;MINUTE&#39;</span><span class="s10">,</span><span class="s0"> </span><span class="s4">-30</span><span class="s10">,</span><span class="s0"> </span><span class="s19">GETDATE</span><span class="s10">())</span><span class="s0"> </span>

<span class="s5">GROUP</span><span class="s0"> </span><span class="s5">BY</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Caption</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Uri</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Router</span><span class="s10">.</span><span class="s11">RoutingTableFlap</span><span class="s10">.</span><span class="s11">RouteDestination</span><span class="s10">,</span><span class="s0"> </span><span class="s11">Nodes</span><span class="s10">.</span><span class="s11">Router</span><span class="s10">.</span><span class="s11">RoutingTableFlap</span><span class="s10">.</span><span class="s11">CIDR</span><span class="s0"> </span>

<span class="s5">HAVING</span><span class="s0"> </span><span class="s20">COUNT</span><span class="s10">(*)</span><span class="s0"> </span><span class="s10">>=</span><span class="s0"> </span><span class="s4">3</span><span class="s0"> </span>

<span class="s5">ORDER</span><span class="s0"> </span><span class="s5">BY</span><span class="s0"> </span><span class="s20">COUNT</span><span class="s10">(*)</span><span class="s0"> </span><span class="s11">DESC</span>

Probably works a little quicker since there is no JOIN.   Still doesn't work around the issue of potentially missing variables?   I don't have the time to go down that rabbit hole, but if you find a solution to that, let us know!

But, what would I do at this point?   I'd probably create a custom page that has a resource that utilizes your original query, and have the Alert text point to that page.   Yea, I know, doesn't give you the info in the alert, but is definitely easier!!   

HTH!