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
Store

Query for alerts of a specific node

I would like to create an SQL query - targeting NPM 12.2 database - for retrieving the alert history of a specific node given by name (or NodeID or IP_Address, etc...)

I found below query on this thread what I could use a starting point, except it does not work with current database schema. How can I update this to work with current version ?

Select Nodes.Caption, Nodes.IP_Address, AlertLog.LogDateTime From Nodes

Join AlertLog on Nodes.NodeID = AlertLog.ObjectID

Where AlertLog.ObjectType = 'Node' and ..........

Thank you.

Find more posts tagged with

Accepted answers

All comments

neomatrix1217

I'm not much of a SQL guy but you could try something like this.

Select Nodes.Caption, Nodes.IP_Address, AlertHistoryView.Message From Nodes

Join AlertHistoryView on Nodes.NodeID = AlertHistoryView.RelatedNodeID

Where Nodes.Status = 2 and AlertHistoryView.ObjectType = 'Node'

mesverrum

I literally just happened to be copy pasting this query in my client's environment today, it is written to be used on the Node Details page, but if you remove the line with ${nodeid} then it would work as a summary overall.

select ah.AlertObjects.AlertConfigurations.Name as [Alert Name],

case when EventType=0 then concat('Triggered - ',ah.Message)

when EventType=1 then concat('Alert Reset',ah.message)

when EventType=2 then concat('Acknowledged - ',ah.Message)

when EventType=3 then concat('Notes - ',ah.Message)

end as [Message],

ah.AlertObjects.EntityCaption as [Triggering Object],

ToLocal(ah.Timestamp) as [Time],

'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(ah.AlertObjectID) as [_linkfor_Message],

'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(ah.AlertObjectID) as [_linkfor_Alert Name]

from Orion.AlertHistory ah

where

eventtype=0

--EventType in (0,1,2,3)

and ah.AlertObjects.RelatedNodeID='${NodeID}'

--and (Message like '%${SEARCH_STRING}%'

--or Timestamp like '%${SEARCH_STRING}%')

order by TimeStamp desc

-Marc Netterfield

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

lafrank

Thank you, it seems really promising, only I am having issues executing this.

To narrow down the problem, I simplified above to :

select ah.AlertObjects.AlertConfigurations.Name,ah.Message,ah.AlertObjects.EntityCaption,ah.Timestamp,

from Orion.AlertHistory ah

But still receiving message : RunQuery failed, check fault information

Unfortunately I don't know where to find the referenced "fault information" so uncertain what is causeing the error.

Any ideas please ?

mesverrum

Are you putting this into the custom query resource on your view? Maybe alert history has too many rows in your environment and it is timing out? Try doing top 10 and see how that goes

lafrank

I was using a custom query tool written by me, but as I pointed out I could not find what "fault information" was.

Now I ran the same with PowerShell and got below output:

Get-SwisData : Source entity [Orion.AlertHistory] not found in catalog

I am using NPM12.2, is this table missing there or has other name ?

james.heads

Thanks for the script, does the exact job i was after.