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

Active Alert (email) Report

I'm writing a report that will show active alerts which have triggered emails to the NOC's ticketing system. The alert is of course an advanced SQL query in report writer. I've made some good progress but have hit a sticking point. Here's what I have so far:

SELECT Distinct AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName, AlertDefinitions.AlertName
FROM AlertStatus, AlertDefinitions, AlertLog, ActionDefinitions
WHERE AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
AND AlertStatus.AlertDefID = AlertLog.AlertDefID
AND AlertStatus.AlertDefID = ActionDefinitions.AlertDefID
AND AlertLog.ActionType = 'EMail'
AND AlertStatus.ActiveObject = AlertLog.ObjectID
AND ActionDefinitions.Target like '%freuheat%'
Order By TriggerTimeStamp Desc

It works pretty well and gets details about all of my currently active email alerts. The problem comes from APM alerts. The "Object Name" is simply the Application Name, so if a website is down it shows simply "HTTP Monitor". I need a way to at least link it back to the node so it's possible to tell what is actually being affected. The same thing exists in the standard built in Orion "Alerts" screen, but at least there you can mouse over and get the device name. I was thinking there might be a way to link the AlertLog.ObjectID or AlertStatus.ActiveObject back to somewhere else, but not sure where. Also would need to use some kind of if statement in there to only do that on APM Objects. The only other possibility I could think of was to use a regex to pull the node name out of the email alert itself in AlertLog.Message, but just like if statements in SQL I don't know how to do this.

So anyone have any ideas?

Find more posts tagged with

Accepted answers

All comments

tlwoodstx

Have you considered renaming the individual component within the application monitor or within the application template. The "HTTP Monitor" could be renamed to "www.xyz.local".

HTH

FormerMember

Hello-

I agree with Troy. We went ahead and did that. For example, instead of plain old 'Custom Port Monitor', we changed it to 'Port 389-<name of our AD Server>'. This gives much better info to the NOC. Yes, some work in the beginning, but it's worth it!

And on a side note, thanks SOOO much for posting this SQL statement! I was able to mod it and use it this morning to produce an alert report for our NOC Manager.

Thanks so much!

Mike B.

Petr.Vilem

SELECT Distinct AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName, AlertDefinitions.AlertName, Nodes.Caption
FROM AlertStatus, AlertDefinitions, AlertLog, ActionDefinitions, APM_Application, Nodes
WHERE AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
AND AlertStatus.AlertDefID = AlertLog.AlertDefID
AND AlertStatus.AlertDefID = ActionDefinitions.AlertDefID
AND AlertLog.ActionType = 'EMail'
AND AlertStatus.ActiveObject = AlertLog.ObjectID
AND ActionDefinitions.Target like '%freuheat%'
AND AlertStatus.ObjectType = 'APM: Application'
AND AlertStatus.ActiveObject = APM_Application.ID
AND APM_Application.NodeID = Nodes.NodeID
Order By TriggerTimeStamp Desc

...but this filters alerts only to APM application related ones. If you want to keep others, you can try something like this:

SELECT Distinct AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName, AlertDefinitions.AlertName, AppNode.Caption AS ApplicationNode
FROM AlertStatus
INNER JOIN ActionDefinitions ON AlertStatus.AlertDefID = ActionDefinitions.AlertDefID
INNER JOIN AlertDefinitions ON AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
INNER JOIN AlertLog ON AlertStatus.AlertDefID = AlertLog.AlertDefID AND AlertStatus.ActiveObject = AlertLog.ObjectID
LEFT JOIN APM_Application ON AlertStatus.ActiveObject = APM_Application.ID AND AlertStatus.ObjectType = 'APM: Application'
LEFT JOIN Nodes as AppNode ON APM_Application.NodeID = AppNode.NodeID
WHERE AlertLog.ActionType = 'EMail'
AND ActionDefinitions.Target like '%freuheat%'
Order By TriggerTimeStamp Desc

...considering query performance and concurrency to other database activity, adding few NOLOCKs could be good idea if you plan to execute this query often:

SELECT Distinct AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName, AlertDefinitions.AlertName, AppNode.Caption AS ApplicationNode
FROM AlertStatus WITH (NOLOCK)
INNER JOIN ActionDefinitions ON AlertStatus.AlertDefID = ActionDefinitions.AlertDefID
INNER JOIN AlertDefinitions ON AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
INNER JOIN AlertLog ON AlertStatus.AlertDefID = AlertLog.AlertDefID AND AlertStatus.ActiveObject = AlertLog.ObjectID
LEFT JOIN APM_Application WITH (NOLOCK) ON AlertStatus.ActiveObject = APM_Application.ID AND AlertStatus.ObjectType = 'APM: Application'
LEFT JOIN Nodes as AppNode WITH (NOLOCK) ON APM_Application.NodeID = AppNode.NodeID
WHERE AlertLog.ActionType = 'EMail'
AND ActionDefinitions.Target like '%freuheat%'
Order By TriggerTimeStamp Desc

lhorstma

SELECT Distinct AlertStatus.TriggerTimeStamp, AlertStatus.ObjectName, AlertDefinitions.AlertName, AppNode.Caption AS ApplicationNode
FROM AlertStatus WITH (NOLOCK)
INNER JOIN ActionDefinitions ON AlertStatus.AlertDefID = ActionDefinitions.AlertDefID
INNER JOIN AlertDefinitions ON AlertStatus.AlertDefID = AlertDefinitions.AlertDefID
INNER JOIN AlertLog ON AlertStatus.AlertDefID = AlertLog.AlertDefID AND AlertStatus.ActiveObject = AlertLog.ObjectID
LEFT JOIN APM_Application WITH (NOLOCK) ON AlertStatus.ActiveObject = APM_Application.ID AND AlertStatus.ObjectType = 'APM: Application'
LEFT JOIN Nodes as AppNode WITH (NOLOCK) ON APM_Application.NodeID = AppNode.NodeID
WHERE AlertLog.ActionType = 'EMail'
AND ActionDefinitions.Target like '%freuheat%'
Order By TriggerTimeStamp Desc

This is what I'm looking for but the node/caption field isn't showing up as populated on the APM alerts, just blanks all the way down. Any ideas?

lhorstma

Have you considered renaming the individual component within the application monitor or within the application template. The "HTTP Monitor" could be renamed to "www.xyz.local". HTH

I've got thousands of APM monitors. I could do that here and there for a few things (and already do here and there), but not on a large scale.

mgibson

I started out like this, then seen your code, and I wonder how to merge the scripts to get more info. Because Group alerting is broken, I need to know the current status of the nodes and show if down or unreachable.

Select nodes.IP_Address, nodes.caption activeobject,Triggertimestamp, triggertimeoffset AS Duration, acknowledgedby, acknowledgedtime,nodes.status, nodes.support, nodes.groupstatus, alertstatus.notes AS Reason, department,nodeid
From alertstatus
INNER JOIN nodes ON nodes.nodeid=alertstatus.activeobject
where
((nodes.status = '2') OR (nodes.status = '12')
AND
(nodes.custviewa like 'erp'))
order by nodeid

The code above give current active alerts with the ack and notes fields, however if an alert is sent to more than one recipient, there will be more than one active alert for each node? So I am thinking an offshoot of your code and mine may give me also the alert recipient. But I do not know how to combine the code.

mgibson

Also if I could get the alertstatus.triggertimeoffset in duration and prevent the acknowledgedtime from showing unless it is a recent date, because many are filled with 12:00:00 AM.