29 Replies Latest reply on Nov 23, 2016 10:08 AM by alexslv

    Alert Acknowledge massage in Report with event details.

    krishna mishra

      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 TimeNodeEvent TypeMessageAcknowledged-statusAcknowledged Alert-Note Acknowledged-timeAcknowledged-By

       

       

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

        • Re: Alert Acknowledge massage in Report with event details.
          krishna mishra

          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

          • Re: Alert Acknowledge massage in Report with event details.
            krishna mishra

            I think no one is using this type of report

              • Re: Alert Acknowledge massage in Report with event details.
                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

                  • Re: Alert Acknowledge massage in Report with event details.
                    krishna mishra

                    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.

                    • Re: Alert Acknowledge massage in Report with event details.
                      krishna mishra

                      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

                      k

                        • Re: Alert Acknowledge massage in Report with event details.
                          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 Owner
                          FROM
                          Nodes 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.NodeID
                          INNER JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID
                          
                          
                          WHERE
                          ( 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
                          
                            • Re: Alert Acknowledge massage in Report with event details.
                              krishna mishra

                              Hi,

                               

                              When i am running the above query the result is showing not fruitful means, node name are showing multiple times for same date alert, but when i checked the trigger event in node, not showing the all alert for same date, query is picking the data  from table for old days,

                                • Re: Alert Acknowledge massage in Report with event details.
                                  I LIKE EGGS
                                  SELECT n.Caption AS NODE
                                  ,CASE 
                                      WHEN DATEDIFF(DAY, e.EventTime, getdate()) > 3
                                      THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, e.EventTime, getUTCdate())) + ' days ago'
                                      ELSE CASE
                                        WHEN DATEDIFF(HOUR, e.EventTime, getdate()) > 3
                                        THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, e.EventTime, getdate())) + ' hours ago'
                                        ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, e.EventTime, getdate())) + ' min ago'
                                      END
                                    END AS 'Time'
                                    ,et.EventType AS 'TYPE'
                                  ,e.Message 'Message'
                                  ,ast.Notes
                                  ,ast.Acknowledged AS 'ACK'
                                  ,ast.AcknowledgedTime AS 'ACK TIME'
                                  ,ast.AcknowledgedBy AS 'ACK BY' 
                                  FROM Events E 
                                  INNER JOIN EventTypes ET ON (E.EventType = ET.EventType)
                                  INNER JOIN Nodes N ON (N.NodeID = E.NetworkNode)
                                  INNER JOIN AlertStatusView ast WITH(NOLOCK) ON (ast.ActiveObject = E.NetworkNode)
                                  WHERE 
                                  ((E.EventTime > DATEADD(DAY,-7,GETDATE())))
                                  AND ET.EventType IN ('5000','520','521','10')
                                  
                                  
                                  order by E.EventTime desc 
                                  
                                  
                                  
                                  

                                   

                                   

                                  try this little beauty out

                        • Re: Alert Acknowledge massage in Report with event details.
                          alexslv

                          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?

                            • Re: Alert Acknowledge massage in Report with event details.
                              krishna mishra

                              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,

                                • Re: Alert Acknowledge massage in Report with event details.
                                  alexslv

                                  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…

                                    • Re: Alert Acknowledge massage in Report with event details.
                                      krishna mishra

                                      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.

                                        • Re: Alert Acknowledge massage in Report with event details.
                                          alexslv

                                          Active Alert is "Active" regardless whether someone has acknowledged it or not. Those alerts that have been reset are not active anymore - they are history. You have another out-of-the-box report for those historical alerts - I will let you to find yourself (practise makes perfect). You can even combine both of them into one report in Layout Builder by having multiple sections

                                           

                                          If you want to go extra mile and want to link them both together into a single table - there are two different SQL Views that you need to check and possibly link together, one being [AlertStatusView] and another one [AlertHistoryView].

                                           

                                          "Nothing happens until something moves!" - Albert Einstein

                                            • Re: Alert Acknowledge massage in Report with event details.
                                              krishna mishra

                                              Alex - really i did the all efforts by the thwack team member, but unfortunately not get success for correct report, that's why i involved you also, if you really know the important of this report, kindly try to find the way where we can get the same report,

                                               

                                              Again thanks in advance for you.

                                                • Re: Alert Acknowledge massage in Report with event details.
                                                  alexslv

                                                  You need to be as specific as you can. I am just guessing that on this occasion you could not find out-of-the-box reports for your historical alerts. ... here you go... try this:

                                                   

                                                  (1)

                                                  Reports > All Reports

                                                   

                                                  (2)

                                                  Use search in top-right corner > search for "alert"

                                                   

                                                   

                                                  (3)

                                                  Here you go - your historical reports, including reset alerts, etc

                                                   

                                                  (4)

                                                  Create a copy of any of those if you wish (just not to mess up with original) and change any settings, update, fine-tune to your liking. I bet even the way it is by default would suit your needs (but I am guessing again here...)

                                                   

                                                  Some extra reading for you

                                                  Manage reports in the Orion Web Console with NPM - SolarWinds Worldwide, LLC. Help and Support

                                                  1 of 1 people found this helpful
                                                    • Re: Alert Acknowledge massage in Report with event details.
                                                      krishna mishra

                                                      Hi Alex,

                                                       

                                                      These alert are working based on event log, and as you advise that trigger alert and event have not any co-relation,

                                                      then this would be work,

                                                       

                                                      Could you please advise to solarwinds support team so they can help us also on this report. by the way i worked with team and they have advise us they are not supporting the customization, update the request in the thwack so that any one can help u on same.

                                                       

                                                      Case Update: 980589

                                                       

                                                      Thanks

                                                      K

                                                      • Re: Alert Acknowledge massage in Report with event details.
                                                        krishna mishra

                                                        Hi Jeremy

                                                         

                                                        Actually same details were given by Alex and same is not useful because I

                                                        am creating the report based on event(netperfmon event log ) and you are

                                                        referring to us for trigger alert if I am using this it is not giving the

                                                        required information,

                                                         

                                                        1- everyday number of alert is triggered, by event log we can identify how

                                                        many alerts are triggerd for node, based on we are doing investigation in

                                                        node level all alerts are true or false and infra team start to work also

                                                        If I can get the details who is ack the alert or not that would be good,

                                                        that's why I would like to this type of report

                                                          • Re: Alert Acknowledge massage in Report with event details.
                                                            alexslv

                                                            The Acknowledged flag does not exist in Events. It belongs to Alert. So, if you want this info - you MUST either report on Alerts OR use SQL to link your Events to Alerts (Dan above has given you some ideas already how to do it with SQL - you can take it from there and expand to what you need).

                                                             

                                                            If you are still not convinced - Albert Einstein will be able to give you further guidance

                                                              • Re: Alert Acknowledge massage in Report with event details.
                                                                krishna mishra

                                                                I used the all query to get the correct report but unfortunately report is

                                                                not correct,

                                                                 

                                                                That's why i am asking to all of you to get the correct report and i have

                                                                bit knowledge of SQL query.

                                                                 

                                                                Let me know you are not really interested to get the same report. Where

                                                                user is acknowledged on how many alERT in daily basis, and which is not

                                                                 

                                                                Thansk

                                                                  • Re: Alert Acknowledge massage in Report with event details.
                                                                    alexslv

                                                                    No, no, report is correct. I have checked it myself - it works good for me. Report is very fruitful and gives me all I need ...  kidding

                                                                    ...

                                                                     

                                                                    Anyway, try this SQL - it will extract all historical alerts for you. It will NOT show active alerts, as they are not history yet. You can see all active alerts in ALERTS & ACTIVITY > ALERTS, where you can find Acknowledgement status as well

                                                                     

                                                                    I believe your Message (*in bold below*) will now be extracted from the Alert as you wanted to

                                                                     

                                                                    SELECT

                                                                       a_log.RelatedNodeID

                                                                      ,n.Caption AS 'NODE'

                                                                      ,a_log.EntityCaption AS 'Object'

                                                                      ,a_def.AlertName AS 'AlertName'

                                                                      ,a_log.TimeStamp AS 'LogDateTime'

                                                                      ,CONVERT(date, a_log.TimeStamp) AS 'DATE'

                                                                      ,act.CategoryType AS 'TYPE'

                                                                      ,a_log.[Message] AS 'Message'

                                                                      ,a_log.EventTypeWord AS 'Status'

                                                                    FROM AlertHistoryView a_log WITH(NOLOCK)

                                                                     

                                                                    INNER JOIN AlertDefinitionsView a_def WITH(NOLOCK) ON a_def.AlertDefID = a_log.AlertRefID

                                                                    LEFT JOIN Nodes n ON n.NodeID = a_log.RelatedNodeID

                                                                    LEFT JOIN ActionsAssignments act WITH(NOLOCK) ON act.ActionID = a_log.ActionID

                                                                     

                                                                    WHERE

                                                                      --number of days to pull off logs from history

                                                                      DATEDIFF(DAY, a_log.TimeStamp, getUTCdate()) < 30

                                                                      --only return alerts which have triggered email action

                                                                      AND a_log.ActionTypeID = 'Email'

                                                                     

                                                                    ORDER BY a_log.TimeStamp desc

                                                                      • Re: Alert Acknowledge massage in Report with event details.
                                                                        krishna mishra

                                                                        Alex, -thanks for response but sorry given query is not useful, because it is giving the trigger email notification, which is not required.

                                                                         

                                                                        @i am using the below query which is little bit right for us but the point is, i need some help on this query where you can set limitation on "[AuditingEvents].TimeLoggedUtc," table, so that it would not show the old data from one day, because i am fetching the report only for one day.

                                                                         

                                                                        Select

                                                                        NetObjectID, Event_Time, NodeName,

                                                                        Event_Type,  Cast(Message As nvarchar(250)) as Message,TimeLoggedUtc,AccountID,ActionTypeID,AuditEventMessage From ( SELECT Nodes.NodeID AS NodeID,

                                                                        Events.EventTime AS Event_Time,

                                                                        events.NetObjectID,

                                                                        Nodes.Caption AS NodeName,

                                                                        Events.EventType AS Event_Type,

                                                                        Events.Message AS Message,

                                                                        [AuditingEvents].TimeLoggedUtc,

                                                                        [AuditingEvents].AccountID,

                                                                        [AuditingEvents].ActionTypeID,

                                                                        [AuditingEvents].AuditEventMessage

                                                                         

                                                                         

                                                                        FROM

                                                                        Nodes

                                                                        INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON

                                                                        (Events.EventType = Events_EventTypes.EventType)) ON

                                                                        (Nodes.NodeID = Events.NetworkNode)

                                                                        Inner Join [AuditingEvents] on (events.NetObjectID = [AuditingEvents].netobjectID)

                                                                         

                                                                         

                                                                        WHERE

                                                                        ( eventtime between (select (DATEADD(dd,-1,getdate()))) AND (select getdate()))

                                                                        AND 

                                                                        (

                                                                          (Events.EventType = 5000 ) OR

                                                                          (Events.EventType = 520 ) OR

                                                                          (Events.EventType =  521) OR

                                                                          (Events.EventType = 10))

                                                                          AND (AuditingEvents.ActionTypeID =6)

                                                                         

                                                                         

                                                                        ) As r ORDER BY 2 DESC

                                                  • Re: Alert Acknowledge massage in Report with event details.
                                                    krishna mishra

                                                    Any one can help me on this report

                                                     

                                                    Thanks in Advance for everyone

                                                     

                                                    thanks

                                                    K