Number of State Change Events Per Day (Report Dataset)

  • [アーティクル]

image

 /*Number of state change events per day (trending)
 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 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

 

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