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

SQL query for Active Alert with current assigned poller and object IP address

Does anyone know how I can get a sql query to report on triggered alerts + the alert object's associated IP / current assigned poller

It's a pain to constantly have to open each object or 'hover' to see the addresses when we have a mass outage. Makes troubleshooting difficult

I am not sure which tables to join for the information. There is little to join from in the active alerts table

Thank you!

Find more posts tagged with

Accepted answers

All comments

stuartd

I can do you a SWQL query without the polling engine as I can't see anyway (quickly) to join that info in.

SELECT 
aa.AlertActiveID, 
aa.Uri,
aa.AlertObjectID, 
n.IP_Address, 
n.Caption

FROM Orion.AlertActive AS AA
JOIN  Orion.Nodes AS N ON aa.AlertObjectID=n.NodeID

Consider it a starter for 10

yaquaholic

Use the EngineID and Orion.Engines table

SELECT aa.AlertActiveID, aa.Uri, aa.AlertObjectID, n.IP_Address, n.Caption,
E.ServerName AS Poller
FROM Orion.AlertActive AS AA
JOIN Orion.Nodes AS N ON aa.AlertObjectID=n.NodeID
INNER JOIN Orion.Engines AS E ON E.EngineID = N.EngineID

christopher.t.jones123

@yaquaholic, there is a navigational property from the Nodes table to the Engines table, you should simply need to use the below as your selection statement and then you can just remove the INNER Join you have in your Query.

N.Engine.ServerName

SELECT aa.AlertActiveID, aa.Uri, aa.AlertObjectID, n.IP_Address, n.Caption,
N.Engine.ServerName AS Poller
FROM Orion.AlertActive AS AA
JOIN Orion.Nodes AS N ON aa.AlertObjectID=n.NodeID

stuartd

I hate looking for navigational properties - I can never find the ones I need and yet there they are. It's like a word blindness thing.

Good spot and shout @christopher.t.jones123

adam.beedell

In addition to what everyone else is saying, Settings -> Manage Nodes -> Group by Polling Engine on the top left. Don't even need to write a report!

Also you might enjoy a block like this one:

<b><u>Other servers down in the last 15 minutes</b></u>
${SQL: SELECT Caption+Char(9)+Char(10) FROM Nodes WHERE Status like '2' AND ISServer like '1' AND DATEDIFF(minute, Nodes.LastSystemUpTimePollUTC, GETUTCDATE()) <= 15 AND Caption not like '${N=SwisEntity;M=Caption}' UNION SELECT ' ' AS Caption FROM Nodes For XML path('') }

leonejames1

Does anyone know where the historical data for the pollers are kept? performance/cpu/ram etc?