This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Create a Top XX syslog events by node resource - SWQL

Several folks have mentioned that they are interested in creating a resource for Log Analyzer that summarizes the top "X" nodes by event count. I have created a basic starting point below that could easily be extended to suit the needs of your environment. I am using a "Custom Table" as the basis for this resource and then populating it with some data retrieved via a SWQL query. (Follow this link: SWQL Walkthrough for an excellent primer on SWQL from animelov​) The idea behind this widget is to provide a visual queue for chatty nodes at a fixed severity level... For example a device that is normally very quiet starts climbing the Top 10 with Alert level logs, you can go take a look at that node to identify root cause.

To get started navigate to any summary page where you would like the resource to appear and edit the page and select "Custom Table" as the type of widget you would like to add. After positioning it on the page and exiting the page editor, click the "Edit" link in the upper right hand corner and in the resulting page simply click "Select Datasource". Paste in the query below and then feel free to customize the resource to your liking. Be sure to click into the "Advanced" tab for the "Nodes" column and be sure to enable "Allow HTML tags". A couple quick things to point out about the query... It has embedded HTML that provides a direct link to a given node directly from the table so that it is more efficient to use. Also this is a fairly basic starting point that is specifically selecting "Alert" as the severity level to populate the results. You could easily add multiple widgets for different severity levels or with a little more advanced work in the query you could create a grouping that would show multiple levels of severity in a single table. (I'll try to work on that last part and provide another example later)

SELECT TOP 10 CONCAT('<a href="thwack.solarwinds.com/.../', le.NodeId,'" >', nodes.DisplayName ,'</a>') as [Node],   COUNT(le.NodeId) as [Event Count]

FROM Orion.OLM.LogEntry as le

INNER JOIN Orion.Nodes as nodes on le.NodeID = nodes.NodeID

WHERE le.LogType.Type = 'Syslog' AND le.Level = 'Alert'

AND  [DateTime] > AddDay(-7, GetDate())

GROUP BY (le.NodeId)

ORDER BY [Event Count] DESC

### -- Here is a slightly different version of the query that also filters by a specific vendor -- ###

SELECT TOP 10 CONCAT('<a href="thwack.solarwinds.com/.../', le.NodeId,'" >', nodes.DisplayName ,'</a>') as [Node] , COUNT(le.NodeId) as [Event Count]

FROM Orion.OLM.LogEntry as le

INNER JOIN Orion.Nodes as nodes on le.NodeID = nodes.NodeID

WHERE le.LogType.Type = 'Syslog' and le.Level = 'Emergency' and nodes.Vendor = 'Cisco'

AND  [DateTime] > AddDay(-7, GetDate())

GROUP BY (le.NodeId)

ORDER BY [Event Count] DESC

pastedImage_6.pngpastedImage_5.pngpastedImage_0.pngpastedImage_9.png
  • Hi  

    Thank you for the technical article. I have noticed that API queries to the Orion.OLM.LogEntry are timing out in any high syslog/trap volume environment.

    Regardless of the hardware configuration, API is not able to count those messages via SWQL.

    On the other hand, I was successful in SQL queries. However, Log Viewer/Analyzer often has a dedicated SQL instance and it is tricky to work with SQL queries directly in the Orion Website (workaround such as Linked Database are needed).

    Do you know, if there are plans to optimize API for SWQL queries to Orion.OLM.LogEntry?

    Thanks,

    Marcin.

  •  it would be useful for us to see some logs from the timeouts you are seeing. I will ping you direct for more detail on that.

  • Didn't take me long to convert this into a modern dashboard widget! Love it! 

    I don't suppose you'd know how to make an events list of the last 10 events of a certain severity like this?