Table for PagerDuty Alert Counts

bmwortman

I'm attempting to create a custom report and custom table that simply displays the sum of all alerts with PagerDuty in the name triggered over the last day, 7 days, and 30 days. I've modified a report I found on the forums but I'm not very handy with SQL and I think I'm missing the sums portion as it doesn't seem to be adding up correctly. Here's where I am so far would anyone be willing to assist?

Thanks!

SELECT DISTINCT  ahv.Name 'Alert Name'  ,ISNULL(lastOne.Qty,0) 'Last 1 Day'  ,ISNULL(lastSeven.Qty,0) 'Last 7 Days'  ,ISNULL(lastThirty.Qty,0) 'Last 30 Days'FROM AlertHistoryView ahvFULL OUTER JOIN  (SELECT  Name  ,COUNT(1) 'Qty'  FROM AlertHistoryView  WHERE EventTypeWord = 'Triggered'  AND TimeStamp > (GetDate()-1)  GROUP BY Name) lastOne ON ahv.Name = lastOne.Name FULL OUTER JOIN  (SELECT  Name  ,COUNT(1) 'Qty'  FROM AlertHistoryView  WHERE EventTypeWord = 'Triggered'  AND  Name LIKE '%PagerDuty%'  AND TimeStamp > (GetDate()-7)  GROUP BY Name) lastSeven ON ahv.Name = lastSeven.Name FULL OUTER JOIN  (SELECT  Name  ,COUNT(1) 'Qty'  FROM AlertHistoryView  WHERE EventTypeWord = 'Triggered'  AND  Name LIKE '%PagerDuty%'  AND TimeStamp > (GetDate()-30)  GROUP BY Name) lastThirty ON ahv.Name = lastThirty.Name WHERE ahv.Name LIKE '%PagerDuty%'AND(  lastOne.Qty > 0 OR  lastSeven.Qty > 0 OR  lastThirty.Qty > 0)ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC

i_like_eggs

for this query you only need to add the Filerting to the end. for example:

WHERE ahv.Name LIKE '%PagerDuty%'  AND    (    lastOne.Qty > 0 OR    lastSeven.Qty > 0 OR    lastThirty.Qty > 0  )  ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC  

this is pretty much the section you need to change. here is the full query:

SELECT DISTINCT    ahv.Name 'Alert Name'    ,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    ,COUNT(1) 'Qty'    FROM AlertHistoryView    WHERE EventTypeWord = 'Triggered'    AND TimeStamp > (GetDate()-1)    GROUP BY Name) lastOne ON ahv.Name = lastOne.Name     FULL OUTER JOIN      (SELECT    Name    ,COUNT(1) 'Qty'    FROM AlertHistoryView    WHERE EventTypeWord = 'Triggered'      AND TimeStamp > (GetDate()-7)    GROUP BY Name) lastSeven ON ahv.Name = lastSeven.Name     FULL OUTER JOIN      (SELECT    Name    ,COUNT(1) 'Qty'    FROM AlertHistoryView    WHERE EventTypeWord = 'Triggered'      AND TimeStamp > (GetDate()-30)      GROUP BY Name) lastThirty ON ahv.Name = lastThirty.Name     WHERE ahv.Name LIKE '%PagerDuty%'  AND    (    lastOne.Qty > 0 OR    lastSeven.Qty > 0 OR    lastThirty.Qty > 0  )  ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC