Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Help with SWQL query to check Internet Status

Hi, I am trying create a KPI widget to display Internet Status based on SNMP Traps received from our WAN Router.

ciscoExperiment.91.1.2.3.1.11.7 = ICMP to 8.8.8.8 ok
ciscoExperiment.91.1.2.3.1.10.7 = 0
ciscoExperiment.91.1.2.3.1.9.7 = 0
ciscoExperiment.91.1.2.3.1.7.7 = applet: ICMP-INTERNET-OK
ciscoExperiment.91.1.2.3.1.6.7 =
ciscoExperiment.91.1.2.3.1.5.7 = 0
ciscoExperiment.91.1.2.3.1.4.7 = 0
ciscoExperiment.91.1.2.3.1.3.7 = 0
ciscoExperiment.91.1.2.3.1.2.7 = 211
snmpTrapOID = CISCO-SMI:ciscoExperiment.91.0.2

I wrote the following SWQL query and obviously I am missing something or the syntax is wrong (new to SWQL/SQL).

SELECT Message, NodeID
CASE WHEN Message = 'ciscoExperiment.91.1.2.3.1.11.7 = ICMP to 8.8.8.8 ok' Then 'Up'
ELSE 'Down'
END
FROM Orion.Traps
Where (NodeID = '123')

Need help to fix this please. Thank you very much.

Find more posts tagged with

swql query

internetstatus

Modern Dashboards

Accepted answers

jaed

SELECT TOP 1 Message, NodeID,
(CASE WHEN Message = 'ciscoExperiment.91.1.2.3.1.11.7 = ICMP to 8.8.8.8 ok' Then 'Up'
ELSE 'Down'
END) AS InternetStatus
FROM Orion.Traps
Where (NodeID = '123')
ORDER BY TimeStamp DESC

I cannot verify, but that's my best guess. The query definitely runs in the SWQL editor.

All comments

sunvad

Bump!

jaed

I don't use traps myself, but I did a query in a different table using the same idea and it seems to work.

SELECT TOP 1000 AlertDefID, ActiveObject, ObjectType,

(Case When State=2 Then 'Down' Else 'Up-ish' End) AS State,

WorkingState, ObjectName, AlertMessage, TriggerTimeStamp, TriggerTimeOffset, TriggerCount, ResetTimeStamp, Acknowledged, AcknowledgedBy, AcknowledgedTime, LastUpdate, AlertNotes, Notes, AlertObjectID, AlS.Description
FROM Orion.AlertStatus AlS
ORDER BY TriggerTimeStamp DESC

Looks like you're missing a comma and maybe some parenthesis. Plus SWQL doesn't like unnamed columns.

Those are my best guesses for you.

sunvad

Thanks jd, will try this out but I'm wondering how does this query corelate to internet status on a WAN router?

jaed

I was just giving you a query with the working case statement. Nothing at all to do with your internet query.

I don't know how your existing query isn't working, so I was assuming it didn't execute at all.

sunvad

Ah ok. I tried putting the case statement inside parenthesis but no luck. What should ideally go after END function based on the columns I've selected?