SWQL query to extract Varbind data from SolarWinds Traps Message.

I'm trying to parse data from a Trap that is received in Solarwinds from a switch. I only want to see certain data/values from the trap. In its raw form it send to much data.

Does anyone have a SWQL query I can use to perform that function to create a Report?

Can anyone help?

I would really appreciate it, I have no experience writing SWQL/SQL statements.

Thanks

Example: I only want to see all the 'sysUptime' data, the Monitor_RTT data and RTT METRIC:RTT: 1 ms value.

Find more posts tagged with

Accepted answers

All comments

brscott

It's not pretty because all the varbind names are one csv string and all the varbind values are one csv string.

SELECT 
    le.LogEntryID, 
    le.LogMessageSource.Caption, 
    le.DateTime, 
    le.Message, 
    ( select top 1 lefv.textvalue from Orion.OLM.LogEntryFieldValue lefv where lefv.LogEntryID = le.LogEntryID and lefv.Name = 'VarBindingNames'  ) as VarBindNames, 
    ( select top 1 lefv.textvalue from Orion.OLM.LogEntryFieldValue lefv where lefv.LogEntryID = le.LogEntryID and lefv.Name = 'VarBindingValues'  ) as VarBindValues
FROM Orion.OLM.LogEntry le
Where 
    le.logentrytypeid = 2 -- traps

vader_1

Thank you for your response, I cut and paste your SWQL query into my report and it says query invalid, it looks great to me but not sure what part of the code to edit to make it valid.

brscott

Hmmm, I'm not sure which part is not working. I'm running Orion platform version 2024.1.0 with Log Analyzer. Not sure if any of this has changed in the Observability product line..

vader_1

I’m running v2024.2, the code should work on my system...

adam.beedell

${N=OLM.AlertingMacros;M=OLMAlertMessage.VbName1}${vbName1}

Trap variable binding name

You can specify up to 10 varbinding names/values as macros. To do so, change the last digit to the requested value.

For example, to get the name of the fifth varbinding from the incoming trap, use this:

"${N=OLM.AlertingMacros;M=OLMAlertMessage.VbName5}"

${N=OLM.AlertingMacros;M=OLMAlertMessage.VbData1}${vbData1}

Trap variable binding value

You can specify up to 10 varbinding names/values as macros. To do so, change the last digit to the requested value.

For example, to get the fifth varbinding value from the incoming trap, use this:

"${N=OLM.AlertingMacros;M=OLMAlertMessage.VbData5}"

brscott

Since this is not simple, I really suggest you download the SDK which includes SWQL studio.

https://github.com/solarwinds/OrionSDK

axg4437

Try the below query i gave MachineType = 'Zebra Technologies Corporation' you can customize the same for last 24 hours

SELECT
le.LogEntryID,
le.DateTime,
le.Message,
lel.Level AS SeverityLevel,
lel.LevelKey,
ms.Caption AS NodeName,
ms.IPAddress,
ms.MachineType,
ms.Vendor,
ms.Status,
n.Location AS NodeLocation -- Adding Node Location from Orion.Nodes
FROM Orion.OLM.LogEntry le
LEFT JOIN Orion.OLM.LogEntryLevel lel
ON le.LogEntryLevelID = lel.LogEntryLevelID
LEFT JOIN Orion.OLM.MessageSources ms
ON le.MessageSourceID = ms.MessageSourceID
LEFT JOIN Orion.Nodes n -- Join Orion.Nodes to get Node Location
ON ms.NodeID = n.NodeID
WHERE le.DateTime >= ADDDAY(-1, GETDATE()) -- Last 24 hours
AND ms.MachineType = 'Zebra Technologies Corporation' -- Filter by MachineType
ORDER BY le.DateTime DESC

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.

To extract VarBind data from SNMP traps in SolarWinds using SWQL, you need to query from the Orion.Traps and Orion.TrapVarBinds tables. Here’s how to structure a query:

Basic SWQL Query to Get VarBinds from Traps

<span><span class="hljs-keyword">SELECT</span><br />&nbsp; T.TrapID,<br />&nbsp; T.NodeID,<br />&nbsp; N.Caption <span class="hljs-keyword">AS</span> NodeName,<br />&nbsp; T.DateTime <span class="hljs-keyword">AS</span> TrapReceivedTime,<br />&nbsp; VB.OID,    VB.Value,<br />&nbsp; VB.DisplayName<br /><span class="hljs-keyword">FROM</span><br />&nbsp; Orion.Traps <span class="hljs-keyword">AS</span> T<br /><span class="hljs-keyword">JOIN</span><br />&nbsp; Orion.TrapVarBinds <span class="hljs-keyword">AS</span> VB <span class="hljs-keyword">ON</span> T.TrapID <span class="hljs-operator">=</span> VB.TrapID<br /><span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span><br />&nbsp; Orion.Nodes <span class="hljs-keyword">AS</span> N <span class="hljs-keyword">ON</span> T.NodeID <span class="hljs-operator">=</span> N.NodeID<br /><span class="hljs-keyword">WHERE</span><br />&nbsp; T.DateTime <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">DAY</span>, <span class="hljs-number">-7</span>, GETUTCDATE()) <span class="hljs-comment">-- Last 7 days</span><br /><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span><br />&nbsp; T.DateTime <span class="hljs-keyword">DESC</span></span>

Field descriptions:

Field	Description
`T.TrapID`	Unique ID for each SNMP trap
`T.NodeID`	Associated node
`N.Caption`	Node name
`T.DateTime`	Time trap was received
`VB.OID`	Object Identifier from the VarBind
`VB.Value`	Actual value received
`VB.DisplayName`	Description for the OID if known

Filter by Specific OID or Node (Optional)

You can add filters like:

<span><span class="hljs-keyword">AND</span> VB.OID <span class="hljs-operator">=</span> <span class="hljs-string">&#39;1.3.6.1.4.1.9.9.41.2.0.1&#39;</span>  <span class="hljs-comment">-- Example: Cisco syslog OID</span><br /><span class="hljs-keyword">AND</span> N.Caption <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%router%&#39;</span></span>

NOTE:

The TrapVarBinds.Value field is text, even for numeric OIDs.
If traps are not associated with a node (i.e., NodeID is NULL), they might be unmatched and need to be linked by IP.

Below are tailored SWQL queries to extract VarBind data from SNMP traps in SolarWinds, with filtering options for trap name, specific VarBind keyword, and device group or node criteria.

1. Filter by Specific VarBind OID and Value Keyword

This example filters for traps that include a specific OID and contain a keyword in the Value field:

<span><span class="hljs-keyword">SELECT</span><br />&nbsp; T.TrapID,<br />&nbsp; T.IPAddress,<br />&nbsp; N.Caption <span class="hljs-keyword">AS</span> NodeName,</span>

  T.DateTime AS TrapReceivedTime,

  VB.OID, VB.DisplayName,

  VB.Value

FROM

  Orion.Traps AS T

JOIN

<span>&nbsp; Orion.TrapVarBinds <span class="hljs-keyword">AS</span> VB <span class="hljs-keyword">ON</span> T.TrapID <span class="hljs-operator">=</span> VB.TrapID</span>

LEFT JOIN

<span>&nbsp; Orion.Nodes <span class="hljs-keyword">AS</span> N <span class="hljs-keyword">ON</span> T.NodeID <span class="hljs-operator">=</span> N.NodeID</span>

WHERE

<span>&nbsp; T.DateTime <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">DAY</span>, <span class="hljs-number">-7</span>, GETUTCDATE())</span>

<span><span class="hljs-keyword">&nbsp; AND</span> VB.OID <span class="hljs-operator">=</span> <span class="hljs-string">&#39;1.3.6.1.4.1.9.9.41.2.0.1&#39;</span>  <span class="hljs-comment">-- Replace with the OID you want</span></span>

<span><span class="hljs-keyword">&nbsp; AND</span> VB.Value <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%critical%&#39;</span>          <span class="hljs-comment">-- Replace with your keyword</span></span>

ORDER BY

  T.DateTime DESC

2. Filter by Node Group (Custom Property or Group Name)

Assuming you have a custom property called DeviceGroup, you can filter by that:

SELECT

  T.TrapID,

  N.Caption AS NodeName,

  N.CustomProperties.DeviceGroup,

  T.DateTime AS TrapReceivedTime,

  VB.OID, VB.DisplayName,

  VB.Value

FROM

  Orion.Traps AS T

JOIN

<span>&nbsp; Orion.TrapVarBinds <span class="hljs-keyword">AS</span> VB <span class="hljs-keyword">ON</span> T.TrapID <span class="hljs-operator">=</span> VB.TrapID</span>

JOIN

<span>&nbsp; Orion.Nodes <span class="hljs-keyword">AS</span> N <span class="hljs-keyword">ON</span> T.NodeID <span class="hljs-operator">=</span> N.NodeID</span>

WHERE

<span>&nbsp; T.DateTime <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">DAY</span>, <span class="hljs-number">-7</span>, GETUTCDATE())</span>

<span><span class="hljs-keyword">&nbsp; AND</span> N.CustomProperties.DeviceGroup <span class="hljs-operator">=</span> <span class="hljs-string">&#39;CoreRouters&#39;</span>  <span class="hljs-comment">-- Your group/tag name</span></span>

ORDER BY

  T.DateTime DESC

NOTE: Replace DeviceGroup with your actual custom property name.

3. Filter by Trap Name (MIB or Syslog Event ID Match)

Traps with known SNMP event types may include specific enterprise OIDs or message strings. You can search trap messages like:

SELECT

  T.TrapID,

  T.Message,

  VB.OID, VB.Value,

  T.DateTime, N.Caption AS NodeName

FROM

  Orion.Traps AS T

JOIN

<span>&nbsp; Orion.TrapVarBinds <span class="hljs-keyword">AS</span> VB <span class="hljs-keyword">ON</span> T.TrapID <span class="hljs-operator">=</span> VB.TrapID</span>

LEFT JOIN

<span>&nbsp; Orion.Nodes <span class="hljs-keyword">AS</span> N <span class="hljs-keyword">ON</span> T.NodeID <span class="hljs-operator">=</span> N.NodeID</span>

WHERE

<span>&nbsp; T.DateTime <span class="hljs-operator">>=</span> DATEADD(<span class="hljs-keyword">DAY</span>, <span class="hljs-number">-7</span>, GETUTCDATE())</span>

<span><span class="hljs-keyword">&nbsp; AND</span> T.Message <span class="hljs-keyword">LIKE</span> <span class="hljs-string">&#39;%Authentication Failure%&#39;</span>  <span class="hljs-comment">-- Trap type / text</span></span>

ORDER BY

  T.DateTime DESC

Optional: Include Only Traps Without NodeID (Unmatched Traps)

<span><span class="hljs-keyword">AND</span> T.NodeID <span class="hljs-keyword">IS</span> <span class="hljs-keyword">NULL</span></span>

This helps find traps that aren't properly associated with a SolarWinds node.

Hope this helps.

chad.every

FYI @ronan.tonilon , the Orion.Traps and Orion.TrapVarBinds are no longer valid. That is the old namespace prior to migration to Orion.OLM.

From an alerting standpoint, and mentioned by @adam.beedell above, the variables are found here:
https://documentation.solarwinds.com/en/success_center/orionplatform/content/la-log-analyzer-variables-syslog-trap.htm