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

Alert Acknowledge massage in Report with event details.

Hi,

Anyone is using the report where we can get alert triggered event with alert acknowledge massage, below is the test example test sheet based on this we can create report,

Event Time

Node

Event Type

Message

Acknowledged-status

Acknowledged Alert-Note

Acknowledged-time

Acknowledged-By

Please suggest, how we can create a report. with above details.

Find more posts tagged with

alert acknowledge with event

acknowledge alerts

Accepted answers

krishnamishra0786

I think no one is using this type of report

All comments

krishnamishra0786

Hello Everyone,

kindly anyone can suggest, how can i get the report,

i used the below both query with inner join but result is incorrect. can you all please modify or find the correct table with join.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select top 1 RelatedNodeCaption, eventID, EventTime, AlertMessage, triggertimeStamp, AcknowledgedTime, AcknowledgedBy, Notes

From Events

inner join alerthistory

on events.eventtype=alerthistory.eventtype

inner join alertstatusview

on alerthistory.alertobjectID=alertstatusview.alertobjectID

inner join AlertHistoryView

on alerthistory.alertobjectID=alertstatusview.alertobjectID

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Select top 100 RelatedNodeCaption, AlertMessage, triggertimeStamp, AcknowledgedTime, AcknowledgedBy, Notes

From AlertHistoryView

inner join AlertStatusView

on AlertHistoryView.alertobjectID=AlertStatusView.alertobjectID

krishnamishra0786

I think no one is using this type of report

i_like_eggs

something like this?

select

ahv.TimeStamp

,Caption

,ahv.EventTypeWord AS 'Event Type'

,Message

,asv.Acknowledged AS 'ACK Status'

,asv.Notes

,asv.AcknowledgedTime

,asv.AcknowledgedBy

FROM AlertHistoryView ahv with(nolock)

JOIN Nodes n WITH(NOLOCK) ON N.NodeID = RelatedNodeId

JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID

JOIN AlertDefinitionsView adv with(nolock) ON adv.AlertDefID = asv.AlertDefID

where ahv.EventTypeWord IN ('Note','Triggered','Acknowledged') AND

ahv.timestamp > DATEADD(dd,-7,getdate()) --7 day filter

krishnamishra0786

Hi,

thanks for giving the quick response, however the query is working fine but as i need again your help to get the correct report as i want,

Actually i am using the bellow query to get the report for last 7 days, which is working fine and giving the correct report

*************************************************************************************

Select NodeID, Event_Time, NodeName, Event_Type, Cast(Message As nvarchar(250)) as Message, Region_Country, Region, Company, Office, Server_Class, Server_Description, Owner From ( SELECT Nodes.NodeID AS NodeID,

Events.EventTime AS Event_Time,

Nodes.Caption AS NodeName,

Events.EventType AS Event_Type,

Events.Message AS Message,

Nodes.Region_Country AS Region_Country,

Nodes.Region AS Region,

Nodes.Company AS Company,

Nodes.Office AS Office,

Nodes.Server_Class AS Server_Class,

Nodes.Server_Description AS Server_Description,

Nodes.Owner AS Owner

FROM

Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE

( EventTime BETWEEN 42654 AND 42661.6666666667 )

AND

(

(Events.EventType = 5000 ) OR

(Events.EventType = 520 ) OR

(Events.EventType = 521) OR

(Events.EventType = 10)

)

) As r ORDER BY 2 DESC

{ Note - 5000 means - alert triggered, 521 - hardware critical, 520 - Hardware warning, 10 - Interface down }

***************************************************************************************************************

out put of report is like -

But i would like to add below more column in above query -

Could you please merge the below query with above one so that i can get the correct report.

****************************************************************

select

ahv.TimeStamp

,Caption

,ahv.EventTypeWord AS 'Event Type'

,Message

,asv.Acknowledged AS 'ACK Status'

,asv.Notes

,asv.AcknowledgedTime

,asv.AcknowledgedBy

FROM AlertHistoryView ahv with(nolock)

JOIN Nodes n WITH(NOLOCK) ON N.NodeID = RelatedNodeId

JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID

JOIN AlertDefinitionsView adv with(nolock) ON adv.AlertDefID = asv.AlertDefID

where ahv.EventTypeWord IN ('Note','Triggered','Acknowledged') AND

ahv.timestamp > DATEADD(dd,-7,getdate()) --7 day filter

**********************************************************************************************

Thanks in advance.

krishnamishra0786

Hi I_Like_Eggs,

Have you get any trick to merge the both query,

Thanks

krishnamishra0786

HI,

Have you got any trick to merge the both query. i need this report - please ,

Actually daily 1000 of alerts are generating, i want to report which one alert is acknowledge or not acknowledged by team.

Thanks

i_like_eggs

Yo dude, apologizes for the delay thanks for the nudge i had to comment out the CP but should give you what you want

Select NodeID, Event_Time, NodeName, Event_Type, RELATEDNODEID, Acknowledged,AcknowledgedBy, Cast(Message As nvarchar(250)) as Message From ( SELECT Nodes.NodeID AS NodeID,Events.EventTime AS Event_Time,Nodes.Caption AS NodeName,Events.EventType AS Event_Type,Events.Message AS Message,AHV.RelatedNodeId,ASV.Acknowledged,ASV.AcknowledgedBy--Nodes.Region_Country AS Region_Country,--Nodes.Region AS Region,--Nodes.Company AS Company,--Nodes.Office AS Office,--Nodes.Server_Class AS Server_Class,--Nodes.Server_Description AS Server_Description,--Nodes.Owner AS OwnerFROMNodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)INNER JOIN AlertHistoryView ahv with(nolock) ON AHV.RelatedNodeId = NODES.NodeIDINNER JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectIDWHERE( Events.EventTime > DATEADD(day,-7,getdate())) -- check for 7 days AND (  (Events.EventType = 5000 ) OR  (Events.EventType = 520 ) OR  (Events.EventType =  521) OR  (Events.EventType = 10))) As r ORDER BY 2 DESC

AlexSoul

OK, let's clarify few bits first - what are you expecting to see in "Event Type" and "Event Message"? Those are event- related fields. The rest are Alert-related fields. Please send few screenshots where do you see this info and explain reasoning behind this report - what are you trying to achieve by showing all those fields? Is this for yourself? To see what?

krishnamishra0786

actually i would like to achieve there, all alert trigger and they are being acknowledged by infra team member, i want such type of report who and when the alert acknowledged with what notes,

AlexSoul

Did you try web-based reporting? There is an out-of-the-box report for it, it is called "All Active Alerts". I suggest you create a copy and fine tune to your liking. Let me know if anything is not quite working there - we will look at it further

Here is demo:

http://oriondemo.solarwinds.com/Orion/Report.aspx?ReportID=6071&ReturnTo=aHR0cDovL29yaW9uZGVtby5zb2xhcndpbmRzLmNvbS9Pcml…

krishnamishra0786

Ok That is nice, that is being used only for active alert, i were used the same but the point is, what about those alert which one has reset and someone did acknowledged or someone not, If you can do something, that would be good,

Thanks in advance.

krishnamishra0786

Any one can help me on this report

Thanks in Advance for everyone

thanks

akjam

Please help on this