Number of Events Collected Per Day (Report Dataset)


image

/*Number of events collected per day (trending)
Jonathan Almquist (http://blogs.technet.com/b/jonathanalmquist/)
04-08-2011
*/
 
DECLARE @RowCount AS INT,
    @MGID AS INT,
    @TimeZoneOffset AS INT,
    @OffSetDays AS INT,
    @StartDate AS DATE,
    @EndDate AS DATE
    
SET @RowCount = 20
SET @MGID = 1
SET @TimeZoneOffset = 5
SET @OffSetDays = 60
SET @StartDate = DATEADD(hour, @TimeZoneOffset, DATEADD(day, -@OffSetDays, GETDATE()))
SET @EndDate = DATEADD(hour, @TimeZoneOffset, GETDATE())
 
SELECT CONVERT(VARCHAR(10), vevent.DateTime, 101) AS 'Date',
        COUNT(*) AS 'Events'
FROM  Event.vEvent AS vEvent INNER JOIN
        Event.vEventRule AS vRule ON vRule.EventOriginId = vEvent.EventOriginId INNER JOIN
        vManagedEntity AS vME ON vME.ManagedEntityRowId = vRule.ManagedEntityRowId INNER JOIN
        vManagementGroup AS vMG ON vMG.ManagementGroupRowId = vME.ManagementGroupRowId
where vMG.ManagementGroupRowId = @MGID AND 
        (vEvent.DateTime BETWEEN @StartDate AND @EndDate)
GROUP BY CONVERT(VARCHAR(10), vEvent.DateTime, 101)
ORDER BY 'Date' DESC

 

Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.

Comments (0)

Skip to main content