Alert History with Notes and timestamp for both

I'm looking for a little help. I need to create a swql query to pull alert history for along with the notes entered for that alert.  However I would also like to obtain the timestamp for those entries. The ultimately goal is to see how long it took for an alert to be triggered and how long the operator took to acknowledge and document all the entries with the alert notes section.  Any help would be really appreciated 

SELECT ao.alertconfigurations.Name as [AlertName], ah.AccountID, ah.Message, ao.EntityCaption, ao.RelatedNodeCaption, ao.TriggeredCount, ao.RealEntityType, ah.TimeStamp, ao.AlertNote, ao.LastTriggeredDateTime as [Note Date]
FROM Orion.AlertHistory ah
left join orion.alertobjects ao on ao.AlertObjectID=ah.AlertObjectID
where
ah.AccountID = 'user1'
order by ah.timestamp desc

  • How do you plan on displaying this information?  Like in what format (node names, user id's, timestamps, etc.) would be your ideal output?  Just sketching up a table with what you'd want should be sufficient.

  • Alert name, Node/interface/ etc (alerting), message for the alert,  severity, triggercount, timestamp (of the alert), note details, timestamp of note entered, Calc of how long it took to ack the alert.  (also include any additional entries made in the notes) I know this can cause a larger list, but if the operator jots down ('confirmed a call to engineer but vm was left, will follow up). (operator re-types in the notes, "engineer 2 located, he forgot to mute the node and it was a known outage, can disregard".  

  • So, like this, but with the notes added?

  • I'm not trying to be obtuse, but what you are requesting is already shown in this list:

  • This probably doesn't have everything you need, but I need to step away and I don't want to lose this work.

    -- Alert History Event Types
    -- 0 Alert triggered / message contains the name
    -- 1 alert reset
    -- 2 ack and (optionally) add note / message contains the note --> this appends
    -- 3 add note outside of ack / message contains the note --> this replaces
    -- 4 (fire icon, but don't know what that's for) - maybe escalated to next level?
    -- 5 an action didn't work properly.
    -- 6 action completed successfully
    -- 7 Unack alert
    -- 8 alert manually cleared
    SELECT [AH].AlertObjects.AlertConfigurations.Name AS [Alert], [AH].AlertObjects.EntityCaption AS [Target], [AH].AlertObjects.EntityDetailsUrl AS [Target_URL]
         --     , [AH].Message
         , [AH].Timestamp, [AH].AccountID, CONCAT ('/Orion/images/ActiveAlerts/HistoryResourceEvents/Event_', [AH].EventType, '.png') AS EventIcon, CASE [AH].EventType
              WHEN 0
                   THEN CONCAT ('Triggered: ', [AH].AlertObjects.AlertConfigurations.Name)
              WHEN 1
                   THEN CONCAT ('Reset: ', [AH].AlertObjects.AlertConfigurations.Name)
              WHEN 2
                   THEN CONCAT (
                             'Acknowledged by: [', [AH].AccountID, ']', CASE 
                                  WHEN [AH].Message IS NOT NULL
                                       THEN CONCAT (' Note: ', [AH].Message)
                                  END
                             )
              WHEN 3
                   THEN CONCAT (
                             'Notes Updated by: [', [AH].AccountID, ']', CASE 
                                  WHEN [AH].Message IS NOT NULL
                                       THEN CONCAT (' Note: ', [AH].Message)
                                  END
                             )
              WHEN 7
                   THEN CONCAT ('Un-Acknowledged by: ', [AH].AccountID)
              WHEN 8
                   THEN CONCAT ('Cleared by: ', [AH].AccountID)
              END AS [Information], CASE 
              WHEN [AH].AlertObjects.AlertActive.Acknowledged = 1
                   THEN 'Ack''d'
              ELSE 'Not yet...'
              END AS [AckState], CASE 
              WHEN [AH].AlertObjects.AlertActive.Acknowledged = 1
                   THEN MINUTEDIFF([AH].AlertObjects.AlertActive.TriggeredDateTime, [AH].AlertObjects.AlertActive.AcknowledgedDateTime)
              END AS [MinutedUntilAcknowledged], MINUTEDIFF([AH].AlertObjects.AlertActive.TriggeredDateTime, GETUTCDATE()) AS [MinutesSinceTriggered]
    FROM Orion.AlertHistory AS [AH]
    WHERE [AH].EventType IN (
              0
              , 1
              , 2
              , 3
              , 7
              , 8
              )
    ORDER BY [AH].Timestamp DESC
    

    Quick update to "fix" the Ack Note field: (Needed to use the IsNull function to properly check)

    -- Alert History Event Types
    -- 0 Alert triggered / message contains the name
    -- 1 alert reset
    -- 2 ack and (optionally) add note / message contains the note --> this appends
    -- 3 add note outside of ack / message contains the note --> this replaces
    -- 4 (fire icon, but don't know what that's for) - maybe escalated to next level?
    -- 5 an action didn't work properly.
    -- 6 action completed successfully
    -- 7 Unack alert
    -- 8 alert manually cleared
    SELECT [AH].AlertObjects.AlertConfigurations.Name AS [Alert], [AH].AlertObjects.EntityCaption AS [Target], [AH].AlertObjects.EntityDetailsUrl AS [Target_URL]
    --          , [AH].Message
         , [AH].Timestamp, [AH].AccountID, CONCAT ('/Orion/images/ActiveAlerts/HistoryResourceEvents/Event_', [AH].EventType, '.png') AS EventIcon, CASE [AH].EventType
              WHEN 0
                   THEN CONCAT ('Triggered: ', [AH].AlertObjects.AlertConfigurations.Name)
              WHEN 1
                   THEN CONCAT ('Reset: ', [AH].AlertObjects.AlertConfigurations.Name)
              WHEN 2
                   THEN CONCAT (
                             'Acknowledged by: [', [AH].AccountID, ']', CASE 
                                  WHEN IsNull([AH].Message, '') <> ''
                                       THEN CONCAT (' Ack Note: ', [AH].Message)
                                  END
                             )
              WHEN 3
                   THEN CONCAT (
                             'Notes Updated by: [', [AH].AccountID, ']', CASE 
                                  WHEN IsNull([AH].Message, '') <> ''
                                       THEN CONCAT (' Note: ', [AH].Message)
                                  END
                             )
              WHEN 7
                   THEN CONCAT ('Un-Acknowledged by: ', [AH].AccountID)
              WHEN 8
                   THEN CONCAT ('Cleared by: ', [AH].AccountID)
              END AS [Information], CASE 
              WHEN [AH].AlertObjects.AlertActive.Acknowledged = 1
                   THEN 'Ack''d'
              ELSE 'Not yet...'
              END AS [AckState], CASE 
              WHEN [AH].AlertObjects.AlertActive.Acknowledged = 1
                   THEN MINUTEDIFF([AH].AlertObjects.AlertActive.TriggeredDateTime, [AH].AlertObjects.AlertActive.AcknowledgedDateTime)
              END AS [MinutedUntilAcknowledged], MINUTEDIFF([AH].AlertObjects.AlertActive.TriggeredDateTime, GETUTCDATE()) AS [MinutesSinceTriggered]
    FROM Orion.AlertHistory AS [AH]
    WHERE [AH].EventType IN (
              0
              , 1
              , 2
              , 3
              , 7
              , 8
              )
    ORDER BY [AH].Timestamp DESC

  • Thanks...yes its missing a few things, but from the query you wrote. It doesnt show the minutes until ack minutes since triggered.  I know they got ack because my alert screen shows them ack'd. Perhaps I'm missing something else? 

  • What Platform version are you running?  I want to make sure that I'm working with a similar version.

  • SWQL is at 3.1.0.343 and Solarwinds is 2020.2.6

  • OK - I'm running the same here, so that's not an issue.  The query sorts by the Timestamp in descending order, so the chances that elements are acknowledged already is lower.  If you add:

    AND [AH].AlertObjects.AlertActive.Acknowledged = 1

    as the second to last line (as an extension to the existing WHERE clause), you should (hopefully) see things that have been acknowledged.