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.

alerts weekly report

Hi Team,

I want to create a alert report on weekly basis based upon severity.Could you please guide me that how to  create report.

client want to see that how many critical, warning and informational alert are triggered on weekly basis, Could you please guide me that how to  create report.

Thanks

aswani

Parents Reply Children
  • Hi Johnson,

    Thanks a lot for your reply and answer. Requester is asking me to create one report for critical alerts and another report for warning alerts on weekly basis. Could you please guide me that how to create the separate report for critical & warning alerts o weekly basis

    Thanks

    Aswanikumar

  • Hi Johnson,

    Thanks a lot. I have verified given query but it is giving only count but we are looking alert details for entire week on severity basis. Could you please guide me that how i can get the report.

    Thanks

    Aswani

  • Hey Aswani,

      I think below query helpful to you. 

    select aa.[timestamp] as TriggeredDateTime , aa.message,ac.severity,

    (CASE aa.eventtype

    WHEN 0 then 'Triggered'

    WHEN 1 THEN 'Reset'

    WHEN 2 THEN 'Acknowledged'

    WHEN 3 THEN 'Note Added'

    WHEN 4 THEN 'Added to Incident'

    WHEN 5 THEN 'Action Failed'

    WHEN 6 THEN 'Action Succeeded'

    WHEN 7 THEN 'Unacknowledge'

    WHEN 8 THEN 'Cleared'

    END) AS EventType

    ,ac.name,ao.entityCaption as Name_of_alert from Alerthistory aa

    join AlertObjects ao on ao.AlertObjectID = aa.AlertObjectID

    join AlertConfigurations ac on ao.AlertID = ac.AlertID

    where aa.[timestamp] between ${fromtime} and ${totime}

    and aa.eventtype=0

    order by  aa.[timestamp] Desc

    These are severity numbers

    Severity:

    0 - informational

    1 - warning

    2 - critical

    3 - Serious

    4 - Notice

  • Above given query almost met my requirement but device name is not showing in the report, Could you please add the device name also in the report and then share me the updated query.

  • HI Kishore,

    Almost it met my requirement but  need a report by including the objects like EntityCaption, RelatedNodeCaption. in the above report is giving only information about Entitycaption. Could you please include RelatedNodeCaption along with entitycaption and then share me the updated query so that i will get the alert message and respective Device names both in single report.

    Thanks

    Aswanikumar 

  • Hi Ashwin,

       we don't have node caption option attribute  not available in database query. so we have to given only Entity caption in database. i think Particular device for alert history report not aware. 

    Thanks

  • Hi Kishore,

    Thanks a lot for your answer. RelatedNodeCaption is available in the alert objective table in the database. I have verified and getting the report but i am unable to combine both RelatedNodeCaption & Entity caption since i am not expert in SQL. Could you please help me to get the exact report.

     

    If you have any possiblity, Please let me know  to schedule a call to discuss about it.