BGP Flapping alert - Solarwinds

Hi Team,

I am constructed below query to check if I am able to capture the BGP Flapping syslog and alert if the syslog occurs more than 2 time in last 5 mins however when trying to run the query in SWQL Studio. I am getting the Execution timeout error. does anyone tested BGP flapping alert?

Find more posts tagged with

Accepted answers

All comments

stuartd

I don't have an answer for your specific query, but I found the same issue when I was querying a sister table, the OLM.LogEntryFieldValue.

I got around it using this approach:

I added in an extra field to narrow down the query. In my case I did WHERE Name = 'VarBindingValues' AND TextValue Like '%My_Search%'

Looking at the various fields available, then my suggestion would be to maybe narrow it down to only search on specific NodeIDs? The ones you know have BGP sessions.

My final thought, and I'm not a coder but very much a 'hack them together' is that using "TOP 10" and "last 5 minutes" is a very narrow and very limited search. Especially, if like us, you have 1000's of nodes to search across. Your quey (AIUI) will literally only look at the last 10 nodes in your timeframe i.e. 5m - and if none of those have BGP then it will fail anyway. Perhaps try broadening your query out before narrowing it down?

Sriram_1

I have tried to limit the single node also it is giving me the same error.

stuartd

I believe the issue is probably due to the sheer amount of data that it has to filter through.

ronan.tonilon

Disclaimer:
The content posted herein are provided as a suggestion or recommendation to you for your internal use. The information set forth herein may come from third party website or customers. SolarWinds is not liable for any downtime or any issue that may occur if you perform the following suggestions on the link provided. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.
Please try this first in a lab environment, before implementing to your production.

You can open a support ticket to get insights, but please take note of our Working with Support Policy, customizations are not supported especially if this would involved custom scripts or queries, support can assist in a best effort practice since our support team is intended as a break fix support.

Almost there, here are the issues on the original query that you shared:

Typo in GROUP BY: You wrote GROUB BY instead of GROUP BY.
Incorrect function names: SWQL uses DATEADD() and GETUTCDATE() instead of adddate() and getdate().
Using LEFT JOIN without filtering le.NodeID properly leads to row explosion.
Heavy Syslog Tables: Orion.OLM.LogEntry is large; querying it without index-friendly filtering (like on indexed columns such as LogEntryID, DateTime, Message) causes timeouts.

Below is a fixed and optimized query that is cleaner and should be a working version that avoids joins and filters more efficiently:
SELECT
le.NodeID,
n.Uri,
n.Caption AS NodeName,
COUNT(le.LogEntryID) AS BGPDownCount
FROM
Orion.OLM.LogEntry le
JOIN
Orion.Nodes n ON le.NodeID = n.NodeID
WHERE
le.Message LIKE '%BGP%DOWN%'
AND le.DateTime >= DATEADD(MINUTE, -5, GETUTCDATE())
GROUP BY
le.NodeID, n.Uri, n.Caption
HAVING
COUNT(le.LogEntryID) > 2

Defined functions:

Clause	Purpose
`JOIN Orion.Nodes`	Get friendly node caption and URI
`WHERE DateTime >= DATEADD(MINUTE, -5, GETUTCDATE())`	Time-based filtering
`LIKE '%BGP%DOWN%'`	Match relevant BGP syslog events
`GROUP BY NodeID`	Aggregate by device
`HAVING COUNT > 2`	Only show nodes flapping more than twice

To prevent timeouts:

Avoid LEFT JOIN on massive tables like OLM.LogEntry.
Always filter time window early with indexed DateTime.
Consider indexing strategies if using SQL Server backend (advanced).

Optional: Alert-Based SWQL

You can use this logic in an Advanced Alert as a custom SWQL trigger condition, or use it in a custom table widget to display flapping nodes.

Hope this helps.