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

Need some SWQL Query assistance in a NetPath Alert

Hello, I am using the below form for my netpath alerting and I wanted to include some netpath stats regarding response time, packet loss percentage, etc that occurred at the last probed time.

The below only shows the top row/record which is unfortunately the record that occured earlier in the day. When I simulate the below alert action I am getting a record for midnight instead of the last probed date.

NetPath Name: ${N=SwisEntity;M=ServiceName}
NetPath Source: ${N=SwisEntity;M=ProbeName}
NetPath Destination: ${N=SwisEntity;M=EndpointServices.HostName}
NetPath Destination Port: ${N=SwisEntity;M=EndpointServices.Port}
NetPath Protocol: ${N=SwisEntity;M=EndpointServices.Protocol}
NetPath Probe Interval: ${N=SwisEntity;M=EndpointServices.ProbeIntervalInMinutes} minute(s)
NetPath Last Probe Time: ${N=SwisEntity;M=LastProbeTime}
NetPath Status: ${N=SwisEntity;M=Status}
NetPath Graph: ${N=SwisEntity;M=DetailsUrl}

NetPath Executed At: ${N=SWQL;M=SELECT ExecutedAt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID} }

NetPath Route Change Count: ${N=SWQL;M=SELECT RouteChangeCount
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}}

NetPath Edge Change Count: ${N=SWQL;M=SELECT EdgeChangeCount
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}}

NetPath Packet Loss: ${N=SWQL;M=SELECT PacketLoss
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}}%

NetPath Response Time: ${N=SWQL;M=SELECT Rtt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}}

View full object details here: ${N=SwisEntity;M=DetailsUrl}.
View full alert details here: ${N=Alerting;M=AlertDetailsUrl}
Click here to acknowledge the alert: ${N=Alerting;M=AcknowledgeUrl}

This message was brought to you by the alert named: ${N=Alerting;M=AlertName}

If I do a query simlar to the below then all it fills out is the endpoint service ID and provides the rest of this query as is without processing it. I am not sure why using the AND breaks the query.

Is there any wya to select the LAST row instead of TOP or if not how would I go about ordering queried data and using TOP 1 for the selection?

NetPath Executed At: ${N=SWQL;M=SELECT ExecutedAt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID} AND ExecutedAt = ${N=SwisEntity;M=LastProbeTime}}

Find more posts tagged with

Accepted answers

maciej.sandecki

Can you see if changing your queries like below helps?:

SELECT TOP 1 ExecutedAt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC

All comments

maciej.sandecki

Can you see if changing your queries like below helps?:

SELECT TOP 1 ExecutedAt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC

DGNetops

HA! You are awesome!!! The below section from my alert works and the only issue I had was the UTC generated time from ExecutedAt was showing to be in the evening. I did the ToLocal conversion below and it works!!!

Still learning all of the in's and out's of SWQL and WOW its always the simple solutions lol. Greatly appreciate the insight as my brain was hurting from all of the research in my very limited spare time.

I am primarily a Network Engineer who has dabbled in programming and I have been learning SWQL for alerting as well as ansible automation.

NetPath Executed At: ${N=SWQL;M=SELECT TOP 1 ToLocal(ExecutedAt) as LTime
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC}

NetPath Route Change Count: ${N=SWQL;M=SELECT TOP 1 RouteChangeCount
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC}

NetPath Edge Change Count: ${N=SWQL;M=SELECT TOP 1 EdgeChangeCount
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC}

NetPath Packet Loss: ${N=SWQL;M=SELECT TOP 1 PacketLoss
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC}%

NetPath Response Time: ${N=SWQL;M=SELECT TOP 1 Rtt
FROM Orion.NetPath.Tests
WHERE EndpointServiceID = ${N=SwisEntity;M=EndpointServices.EndpointServiceID}
ORDER BY ExecutedAt DESC}