image

/*Top events collected
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 TOP (@RowCount) COUNT(*) AS 'Count',
    vEvent.EventDisplayNumber AS 'Event',
    vRule.RuleDefaultName AS 'Rule',
    vMP.ManagementPackDefaultName AS 'MP'
FROM  vManagementGroup as vMG INNER JOIN
    vManagedEntity AS vME ON vMG.ManagementGroupRowId = vME.ManagementGroupRowId INNER JOIN
    Event.vEventRule AS vEventRule INNER JOIN
    vRule ON vEventRule.RuleRowId = vRule.RuleRowId INNER JOIN
    Event.vEvent AS vEvent ON vEventRule.EventOriginId = vEvent.EventOriginId INNER JOIN
    vManagementPack AS vMP ON vRule.ManagementPackRowId = vMP.ManagementPackRowId ON 
    vME.ManagedEntityRowId = vEventRule.ManagedEntityRowId
WHERE (vEvent.DateTime BETWEEN @StartDate AND @EndDate) AND (vMG.ManagementGroupRowId = @MGID)
GROUP BY vEvent.EventDisplayNumber, vRule.RuleDefaultName, vMP.ManagementPackDefaultName, 
               vMG.ManagementGroupRowId
ORDER BY 'Count' DESC

 

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