alerts weekly report

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

osborne_graham said:

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.

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