Blog Post: Number of State Change Events Per Day (Report Dataset)

/*Number of state change events 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), vSR.DateTime, 101) AS 'Date',

        COUNT(*) AS 'Count'

FROM  State.vStateRaw AS vSR INNER JOIN

        vManagedEntityMonitor AS vMEM ON vMEM.ManagedEntityMonitorRowId = vSR.ManagedEntityMonitorRowId INNER JOIN

        vManagedEntity AS VME ON VME.ManagedEntityRowId = vMEM.ManagedEntityRowId INNER JOIN

        vManagementGroup AS vMG ON vMG.ManagementGroupRowId = VME.ManagementGroupRowId

WHERE (vSR.DateTime BETWEEN @StartDate AND @EndDate) AND (vMG.ManagementGroupRowId = @MGID)

GROUP BY CONVERT(VARCHAR(10), vSR.DateTime, 101)

ORDER BY 'Date' DESC

Deanna Russo Cheryl Burke Olivia Wilde Paulina Rubio Kirsten Dunst