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

  • I thought you requested a count. I'm pretty sure some of the out of the box reports based on events rather than alerthistory might be better suited to your need but if you want to carry on with what i've already given you just add a select from 'message' and 'timestamp' to get access to those fields:

    SELECT DISTINCT
    ahv.Name 'Alert Name'
    ,ahv.RelatedNodeCaption 'Parent Device'
    ,ahv.Severity 'Severity'
    ,ahv.EntityCaption 'Alert Object'
    ,ahv.message 'message'
    ,ahv.timestamp 'timestamp'
    ,ISNULL(lastOne.Qty,0) 'Last 1 Day'
    ,ISNULL(lastSeven.Qty,0) 'Last 7 Days'
    ,ISNULL(lastThirty.Qty,0) 'Last 30 Days'

    Then build the report like so:


    You'll probably need to tweak the SQL further to get exactly what you need.

  • Are you looking for like this Ashwin. we have time stamp also available for requried alerts report. 

  • yes and what kind of information will get in the NAME column

  • Create the web based report using the below script. you will get the required  report and let me know. 

    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

    Cheers..

  • Hi,

    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.

    Thanks

    Aswanikumar

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

  • Please find the updated query details. Name of alert act as Node Name.

    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 Node 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

    Please let me know

  • I just spent a little time and build a report that might have what you need or at least give you a good jumping off point.

    Historical Triggered Alerts for the Last 7 Days - Reports - The Orion Platform - THWACK (solarwinds.com)

  • Hi,

    Thanks for your reply. i have executed the above given query but title showing as node name but it is giving interface names instead of host name. 

    I have executed the below query since i want alert information as well as Device name but it is giving error. Could you please correct it.

    select aa.[timestamp] as TriggeredDateTime , ao.EntityCaption, ao.RelatedNodeCaption, 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
    ,ac.name,ao.RelatedNodeCaption as DeviceName 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 and severity='2'
    order by aa.[timestamp] Desc

  • 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