Network Management

Identifying Syslog and Traps Top Talkers in Orion 2019.4 and newer using OLM/LA

This document describes how to identify Syslog and Traps Top Talkers by querying the Log Analyzer (LA)/Orion Log Viewer (OLV) Database.

First published date

4/7/2020 8:19 PM

Last published date

4/14/2023 10:31 PM

Overview

In past versions, Orion maintained exclusive tables for Syslog and Traps.

To implement Log Analyzer (Licensed)/Orion Log Viewer (Free), SolarWinds has decided to centralize logs of that nature in a new database.

Although carrying some similarities with its previous form, the new database structure is organized differently.
New fields and names were introduced in a different order, making it more efficient but a little harder to understand at first sight.

Product section

Network Performance Monitor

Cause

Database changes to accommodate LA/OLV.

Resolution

  1. Open Web Console
  2. Navigate to <OrionURL>orion/admin/swis.aspx
  3. Paste the desired query into the window (below)
  4. Press Execute Query

A list should be created for all the nodes sending Traps/Syslog into LA and the number of messages stored on the DB order by total descendent.

This can also be used to create a Custom Report in Orion Web Reports to generate the report for Top Talkers in LA/OLV for Syslog and Trap Messages.

Queries

--------- TRAPS ---------

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

-- Traps
SELECT n.caption, e.NodeID, COUNT(e.LogEntryID) as total, n.Vendor, n.MachineType, n.IP_Address
FROM Orion.OLM.LogEntry as e
INNER JOIN Orion.OLM.LogEntryType as t on t.LogEntryTypeID = e.LogEntryTypeID
INNER JOIN Orion.Nodes as n on n.NodeID = e.NodeID
where t.Type = 'Traps'
group by e.NodeID
order by total desc

--------- SYSLOG ---------

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

-- Syslog
SELECT n.caption, e.NodeID, COUNT(e.LogEntryID) as total, n.Vendor, n.MachineType, n.IP_Address
FROM Orion.OLM.LogEntry as e
INNER JOIN Orion.OLM.LogEntryType as t on t.LogEntryTypeID = e.LogEntryTypeID
INNER JOIN Orion.Nodes as n on n.NodeID = e.NodeID

where t.Type = 'Syslog'
group by e.NodeID
order by total desc
---------- Top Talkers by Logging Level ----------

This script should be executed against the OrionLog database. (This is not SWQL query, unlike the other two above). 
-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

-- Top Talkers by Logging Level
SELECT 
      count(e.LogEntryMessageSourceID) as countofsourceID
      ,e.LogEntryMessageSourceID
      ,l.DisplayName
      ,s.MachineType
      ,s.[IPAddress]
      ,s.[Vendor]
      ,s.[Caption]
  FROM [dbo].[OrionLog_LogEntry]E
  Left Join OrionLog_LogEntryMessageSource S on s.LogEntryMessageSourceID = e.LogEntryMessageSourceID
  Left Join OrionLog_LogEntryLevel l on l.LogEntryLevelID = e.LogEntryLevelID
  Group by  e.LogEntryMessageSourceID, s.MachineType,s.IPAddress,s.MachineType,s.Vendor, s.Caption, l.DisplayName
  Order by count(e.LogEntryMessageSourceID) desc

Once the top talkers are identified, consider whether to Create Syslog or Trap discard rule in Orion Log Viewer or Log Analyzer to minimize the amount of data being processed and stored in the database.

If the size of the database has grown significantly and you need to reclaim space, please review this:
Truncate the SolarWindsOrionLog tables in OLV/LA