Event Totals By Shift Report

mdriskell

Ok so I need a lot of help with this one.  I need to create a custom report similar to the event summaries that are shown within Solarwinds.  What I need to accomplish is a break down of events over a series of shifts showing the daily average.

Shift 1 7AM - 4 PM

Shift 2 4PM - 11PM

Shift 3 11PM - 7 AM

I found some other reports using counting methods that got me started and I created this.

select cast(EventType as varchar(300)) as EventType, COUNT(*) as Occurances

from Events

Where EventType <> 5
AND  EventType <> 9
AND  EventType <> 11
AND  EventType <> 16
AND  EventType <> 19
AND  EventType <> 52
AND  EventType <> 56
AND  EventType <> 150
AND  EventType <> 202
AND  EventType <> 400
AND  EventType <> 500
AND  EventType <> 506
AND  EventType <> 500
AND  EventType <> 700
AND  EventType <> 910
AND  EventType <> 920
AND  EventType <> 930
AND  EventType <> 940
AND  EventType <> 950
AND  EventType <> 1500
AND  EventType <> 5001
AND  EventType <> 6200
group by cast(EventType as varchar(300)), EventType

ORDER BY 2 DESC

 

The exclusions are for normal events (example I'm exluding the resets and some internal Solarwinds Events).  Where I need to go from here is figure out how to join the information so I get the EventTypes.name instead of the Number associated in my report.  I also like I said need to figure out how to break it down in a per shift manner.

Any help would be greatly appreciated.

netlogix

I am doing everything through SQL Query Manager, but you could use this.

declare @StartDate datetime, @EndDate datetime
-- Variable Range (30 days):
-- Set @StartDate = DATEADD(Day,-30,GETDATE())
-- Set @EndDate = GetDate() -- Now
-- Variable Last Month
-- Set @StartDate = DATEADD(MONTH,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
-- Set @EndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
--Specific Range:
Set @StartDate = '2011/08/01'
Set @EndDate = '2011/09/01'


Select EventTypeName, isnull([Shift1],0) as Shift1, isnull([Shift2],0) as Shift2, isnull([Shift3],0) as Shift3, (isnull([Shift1],0) + isnull([Shift2],0) + isnull([Shift3],0)) as Total
  From  (
    select EventTypes.Name As EventTypeName, 'Shift1' as Shift, COUNT(*) as Occurances
      from Events inner join EventTypes on Events.EventType = EventTypes.EventType
      Where not Events.EventType in (5,9,11,16,19,52,56,150,202,400,500,506,500,700,910,920,930,940,950,1500,5001,6200)
        and DatePart(Hour,EventTime) between 7 and 16
        And EventTime between @StartDate and @EndDate
      Group by EventTypes.Name, DatePart(Hour,EventTime)
    Union
    select EventTypes.Name As EventTypeName, 'Shift2' as Shift, COUNT(*) as Occurances
      from Events inner join EventTypes on Events.EventType = EventTypes.EventType
      Where not Events.EventType in (5,9,11,16,19,52,56,150,202,400,500,506,500,700,910,920,930,940,950,1500,5001,6200)
        and DatePart(Hour,EventTime) between 16 and 23
        And EventTime between @StartDate and @EndDate
      Group by EventTypes.Name, DatePart(Hour,EventTime)
    Union
    select EventTypes.Name As EventTypeName, 'Shift3' as Shift, COUNT(*) as Occurances
      from Events inner join EventTypes on Events.EventType = EventTypes.EventType
      Where not Events.EventType in (5,9,11,16,19,52,56,150,202,400,500,506,500,700,910,920,930,940,950,1500,5001,6200)
        and not DatePart(Hour,EventTime) between 7 and 23
        And EventTime between @StartDate and @EndDate
      Group by EventTypes.Name, DatePart(Hour,EventTime)
  ) As RawData Pivot (
    Sum(Occurances)
    for Shift in ([Shift1],[Shift2],[Shift3])
  ) AS PivotTable
  order by [Total] desc