This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

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

Parents
  • 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?

  • 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,

  • 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

  • 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.

  • 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"

    pastedImage_17.png

    (3)

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

    pastedImage_18.png

    (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

  • 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

  • My friend, I am not representing SolarWinds anyhow... I am just like you - user/customer who seeks my own answers and also helps others to get theirs. What exactly is not working for you? Why you don't like out-of-the-box alerts and reports which I have shown above as an examples?

  • Thanks i am respecting your word

    But the given example o report os not fruitful

    If you really understand the requirement of report then pleade let us know

    the way to get the same report

    Thanks

    K

  • How would it look like if it were "fruitful" then?

  • Hi Alex,

    Actually as you know with previous conversation , the report is working based on event and if we want to add below column details

    Event TimeNodeEvent TypeMessageAcknowledged-statusAcknowledged Alert-NoteAcknowledged-time

    Acknowledge

    By

    which is not showing the correct output, we have posted the number of sql query which we have used,

    May i know you are not using the such type of report In your infra,

    you are interested to know ho many alert is generated every day and in which alert is not being acknowledge by team or not.

    i want to such type of report where team is working on all alert and crating a ticket. or you can say acknowledged or not

    thanks

    k

  • 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

  • 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

Reply Children
  • 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

  • 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 emoticons_happy.png

    ...

    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

  • 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

  • Use DATETIFF SQL function in your WHERE clause to filter out everything older than 24 hours:

    DATEDIFF(HOUR, [AuditingEvents].TimeLoggedUtc, getUTCdate()) < 24

    Reference:

    DATEDIFF (Transact-SQL)

  • as for the trigger filter - feel free to alter query to change it to whatever action type IS required (yet again - you are telling me what you DON'T want, rather than what you DO WANT. So, I have no idea what trigger actions you are looking for. I am doing this for Emails because all my alerts have email trigger actions)