Top events collected (Report Dataset)


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.

Comments (0)

Skip to main content