Top events collected (Report Dataset)

 /*Top events collected
 Jonathan Almquist (https://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.