EFSHARP nice handle btw. See my thwack post on basically what I constructed it will look at a variety of things around alerts and it is modifiable to work on not just nodes but other object types.. I just use SWQL studio to verify volgic and object type reference. Let me know if you need assistance with it.
All Related Alerts Per Object Query
You could do this:
Show acknowledged alerts and notes in a report - SolarWinds Worldwide, LLC. Help and Support
Updated May 22, 2017
This article describes how to create a report on acknowledged alerts showing the acknowledgment notes, which can be used to review historical alerts that have been addressed and the actions used to address them.
When Orion generates an alert, it is displayed under Active Alerts as long as the trigger condition is active. When the alert has been resolved and the user has added acknowledgment and resolution notes, the trigger condition is no longer active and the alert is removed from the list of active alerts.
NPM 11.5 and later
The AlertHistoryView table shows the history of the alert including those that have been acknowledged and the notes placed during the acknowledgment. Execute the following query using Orion Database Manager:
AlertHistoryView
01
Select
02
TimeStamp
,
03
EventTypeWord,
04
Name
05
ObjectType,
06
Message,
07
AccountID,
08
AlertObjectid,
09
EntityCaption,
10
LastTriggeredDateTime,
11
AlertNote,
12
Severity
13
FROM
[dbo].[AlertHistoryView]
14
WHERE
EventTypeWord =
'acknowledged'
15
Order
by
DESC
This query can also be used to generate a report by using the Advanced SQL template when creating the report, and then apply this query.
The following is a sample output of the query when executed in Orion Database Manager. The Message Column shows the details entered during alert acknowledgment:
Hi, d09h.
Thanks for your inputs. But though, this will return all the values for all nodes and display it on each node views. What I want to accomplish is get the values on these columns but the values should appear on each Node.
I tried this using custom table > Edit and unchecked the 'Data Source' then I execute/apply the query.
Thanks!
I thought I figured that out but my new problem is the AcknowledgedNote column from AlertActive table is NULL hence there's nothing to show.
Tried to check if there's a value from this AcknowledgedNote column in SQL but there's really not. I wonder if someone already get this view.
The abopve works but are you looking to put this in the Node Detail View? Do you want a report? What is the goal. I have SWQL queries that could fill each.
Hi Adam,
Yes, i'm looking to put this on in the node detail view..
Please share the swql query..
Thanks in advance.
Can you share the query please?
I am not sure what you are attempting to pull. I know for me, I was trying to get the notes to show on all All Active Alerts page, but could not figure it out. I even tried to locate the web page and modify the call to the DB. But I did find a way to create a view that showed it as seen here.
The two queries I used in the custom tables are
*** UPDATE 11/06/2018 ***
I forgot to factor in UTC Time in the tables. For the two below, I had to subtract 6 Hours from the alerted time. I did this be changing the time is each query.
In Acknowledged I changed AlertViewStatus.LastUpdate to DATEADD(HOUR,-6,AlertStatusView.LastUpdate) AS LastUpdate
In Unacknowledged I changed AlertActive.TriggeredDateTime to DATEADD(HOUR,-6,AlertActive.TriggeredDateTime) AS TriggeredDateTime
ACKNOWLEDGED:
SELECT AlertObjects.RelatedNodeCaption ,AlertStatusView.ObjectName, AlertStatusView.AlertMessage, AlertStatusView.AcknowledgedBy, AlertStatusView.Notes , DATEADD(HOUR,-6,AlertStatusView.LastUpdate) AS Lastupdate
FROM AlertStatusView
INNER JOIN AlertObjects ON AlertObjects.AlertObjectID = AlertStatusView.AlertObjectID
WHERE Acknowledged = '1'
UNACKNOWLEDGED:
SELECT
DATEADD(HOUR,-6,AlertActive.TriggeredDateTime) AS TriggeredDateTime, NodesData.Caption, AlertHistoryView.EntityCaption, AlertActive.AcknowledgedBy, AlertActive.AcknowledgedDateTime,AlertActive.TriggeredDateTime,AlertActive.TriggeredMessage, AlertHistoryView.Message
FROM AlertActive
INNER JOIN AlertHistoryView ON AlertHistoryView.AlertObjectId = AlertActive.AlertObjectID
INNER JOIN NodesData ON NodesData.NodeId = AlertHistoryView.RelatedNodeId
WHERE AlertActive.AcknowledgedBy is null AND Message is null
I then set up a TEXT TO SPEECH alert for all alerts that reads what is down to the notification app built in to Solarwinds. Below is the screen we have at the front of the NOC as well as on one of our workstation monitors.
The SQL Queries took me some time to get right, but it's working now. I still have some more dashboards to create.
efsharp,
Did you mean my query for the above?
select
a.name as [Alert Name]
,o.entitycaption as [Object]
,o.lasttriggereddatetime as [Last Occurrence]
from orion.alertobjects o
join orion.alertconfigurations a on a.alertid = o.alertid
where a.enabled = 'True'
and o.relatedNodeId = '7945'
and o.entitytype = 'Orion.Interfaces' ( Replace with the correct entity type and use () with and or to evaluate first if multiple entities types are required
group by a.name
Hello gjp1971
I looked at the screenshot for your Dashboard, they look very neat and professional, I envision to configure it the way you have it, could you show me or point me to the right direction on how to accomplish that view?
Thank you
