Microsoft.SystemCenter.DataWarehouse.Report.Script.AvailabilityTime (DataWarehouseScript)

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.AvailabilityTime" Accessibility="Public" DataWarehouseDataSet="DataWarehouse!Microsoft.SystemCenter.DataWarehouse.DataSet.State">

  <Install> 

                 IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet')

	BEGIN

		EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet] AS RETURN 1')

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ManagedEntityMonitorRowId int,

	@DataAggregation tinyint = 0

AS

BEGIN

  SET NOCOUNT ON



  IF @DataAggregation = 1



	  SELECT vState.DateTime, vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

			 vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

			 vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

			 vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

			 vState.HealthServiceUnavailableMilliseconds

	  FROM   vStateDailyFull as vState

	  WHERE  (vState.ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId) AND

			 (vState.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert(varchar(8), @StartDate, 112))) AND 

			 (vState.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate) + 1, convert(varchar(8), @EndDate, 112))) AND

			 (vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))

  ELSE



	  SELECT vState.DateTime, vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

			 vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

			 vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

			 vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

			 vState.HealthServiceUnavailableMilliseconds

	  FROM   vStateHourlyFull as vState

	  WHERE  (vState.ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId) AND

			 (vState.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND 

			 (vState.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND

			 (vState.Date BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))



END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

                 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet')

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

                 IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet')

	BEGIN

		EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet] AS RETURN 1')

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ManagedEntityMonitorRowId int,

	@DataAggregation tinyint = 0

AS

BEGIN

  SET NOCOUNT ON



  IF @DataAggregation = 1



	  SELECT vState.DateTime, vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

			 vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

			 vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

			 vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

			 vState.HealthServiceUnavailableMilliseconds

	  FROM   vStateDailyFull as vState

	  WHERE  (vState.ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId) AND

			 (vState.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert(varchar(8), @StartDate, 112))) AND 

			 (vState.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate) + 1, convert(varchar(8), @EndDate, 112))) AND

			 (vState.Date BETWEEN DATEADD(day, - 1, @StartDate) AND DATEADD(day, 1, @EndDate))

  ELSE



	  SELECT vState.DateTime, vState.IntervalDurationMilliseconds, vState.InWhiteStateMilliseconds, 

			 vState.InGreenStateMilliseconds, vState.InYellowStateMilliseconds,

			 vState.InRedStateMilliseconds, vState.InDisabledStateMilliseconds,

			 vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds,

			 vState.HealthServiceUnavailableMilliseconds

	  FROM   vStateHourlyFull as vState

	  WHERE  (vState.ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId) AND

			 (vState.DateTime &gt;= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND 

			 (vState.DateTime &lt; DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND

			 (vState.Date BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))



END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityTimeReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>