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

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