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
  • we have a report that generates a count of alerts grouped by the alert type, over a specific number of days, this includes a severity field (by numeric). I didn't write this myself, i think it was one of the prosperon guys, but hopefully this gives you something to start on:

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

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-1)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastOne ON ahv.Name = lastOne.Name AND ahv.RelatedNodeCaption = lastOne.RelatedNodeCaption AND ahv.EntityCaption = lastOne.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-7)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-30)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastThirty ON ahv.Name = lastThirty.Name AND ahv.RelatedNodeCaption = lastThirty.RelatedNodeCaption AND ahv.EntityCaption = lastThirty.EntityCaption

    WHERE ahv.Name IS NOT NULL
    AND
    (
    lastOne.Qty > 0 OR
    lastSeven.Qty > 0 OR
    lastThirty.Qty > 0
    )
    AND ahv.Severity >1
    ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC, ahv.RelatedNodeCaption, ahv.EntityCaption

Reply
  • we have a report that generates a count of alerts grouped by the alert type, over a specific number of days, this includes a severity field (by numeric). I didn't write this myself, i think it was one of the prosperon guys, but hopefully this gives you something to start on:

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

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-1)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastOne ON ahv.Name = lastOne.Name AND ahv.RelatedNodeCaption = lastOne.RelatedNodeCaption AND ahv.EntityCaption = lastOne.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-7)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-30)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastThirty ON ahv.Name = lastThirty.Name AND ahv.RelatedNodeCaption = lastThirty.RelatedNodeCaption AND ahv.EntityCaption = lastThirty.EntityCaption

    WHERE ahv.Name IS NOT NULL
    AND
    (
    lastOne.Qty > 0 OR
    lastSeven.Qty > 0 OR
    lastThirty.Qty > 0
    )
    AND ahv.Severity >1
    ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC, ahv.RelatedNodeCaption, ahv.EntityCaption

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

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-1)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastOne ON ahv.Name = lastOne.Name AND ahv.RelatedNodeCaption = lastOne.RelatedNodeCaption AND ahv.EntityCaption = lastOne.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-7)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption

    FULL OUTER JOIN
    (SELECT
    Name
    ,RelatedNodeCaption
    ,EntityCaption
    ,COUNT(1) 'Qty'
    FROM AlertHistoryView
    WHERE EventTypeWord = 'Triggered'
    AND TimeStamp > (GetDate()-30)
    GROUP BY Name, RelatedNodeCaption, EntityCaption) lastThirty ON ahv.Name = lastThirty.Name AND ahv.RelatedNodeCaption = lastThirty.RelatedNodeCaption AND ahv.EntityCaption = lastThirty.EntityCaption

    WHERE ahv.Name IS NOT NULL
    AND
    (
    lastOne.Qty > 0 OR
    lastSeven.Qty > 0 OR
    lastThirty.Qty > 0
    )
    AND ahv.Severity >1
    ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC, ahv.RelatedNodeCaption, ahv.EntityCaption

  • Ohhhhhh that's one good-looking query VERY NICE! Thanks for sharing.

  • Hi Graham,

      Thanks a lot for your reply and answer, Given report almost met my requirement but few modifications needs to be done  to fill my requirement those details are given below

    1.  Alert Name

    2.  Device Name

    3.Message

    4. Object that triggered this alert

    5. time stamp

    We are looking report for individual alerts but on weekly basis, Could you please guide me that how to create one report for critical and warning alerts on weekly basis 

  • are you after something like this:
    alert name, parent device, alert object and count over past 7 days?

  • Hi Garham,

    Thanks a lot for your reply.

    I am looking report like as below screen shot for critical and warning alerts and count is not required for me but required 7 days alert details.

    Please let me know if you required any more details from my end

  • 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