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

Element properties:

Source Code:

<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.AlertLoggingLatency" Accessibility="Public">

  <Install> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertLoggingLatencyReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

    @LanguageCode varchar(3) = 'ENU'

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError



/* ------------------------------ */



SELECT      vDateTime.DateTime,



ManagedEntityGuid, 

			(DATEDIFF(ms, Alert.vAlert.RaisedDateTime, Alert.vAlert.DBCreatedDateTime)/1000) AS Latency

FROM        vDateTime CROSS JOIN 

			Alert.vAlert INNER JOIN

            vManagedEntity ON Alert.vAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId AND

			Alert.vAlert.RaisedDateTime BETWEEN vDateTime.DateTime AND 

			DATEADD(hh, 1,vDateTime.DateTime) 

			INNER JOIN  #ObjectList ON #ObjectList.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

WHERE		(Alert.vAlert.RaisedDateTime &gt;= @StartDate) AND 

			(Alert.vAlert.RaisedDateTime &lt; @EndDate) AND 

			(vDateTime.Minute = 0) AND 

			(vDateTime.Date BETWEEN 

            DATEADD(day, - 1, @StartDate) AND 

			DATEADD(day, 1, @EndDate))





  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList



  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertLoggingLatencyReportDataGet] TO OpsMgrReader

GO



                </Install>

  <Uninstall>

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

	BEGIN

        DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertLoggingLatencyReportDataGet]

	END

GO



                </Uninstall>

  <Upgrade> 

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

	BEGIN

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

	END

GO



ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertLoggingLatencyReportDataGet]

	@StartDate datetime,

	@EndDate datetime,

	@ObjectList xml,

    @LanguageCode varchar(3) = 'ENU'

AS

BEGIN

  SET NOCOUNT ON



  DECLARE @Error int

  DECLARE @ExecError int



  CREATE TABLE #ObjectList (ManagedEntityRowId int)



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 GOTO QuitError



  INSERT INTO #ObjectList (ManagedEntityRowId)

  EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

    @ObjectList = @ObjectList,

    @StartDate = @StartDate,

    @EndDate = @EndDate



  SET @Error = @@ERROR

  IF @Error &lt;&gt; 0 OR @ExecError &lt;&gt; 0 GOTO QuitError



/* ------------------------------ */



SELECT      vDateTime.DateTime,



ManagedEntityGuid, 

			(DATEDIFF(ms, Alert.vAlert.RaisedDateTime, Alert.vAlert.DBCreatedDateTime)/1000) AS Latency

FROM        vDateTime CROSS JOIN 

			Alert.vAlert INNER JOIN

            vManagedEntity ON Alert.vAlert.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId AND

			Alert.vAlert.RaisedDateTime BETWEEN vDateTime.DateTime AND 

			DATEADD(hh, 1,vDateTime.DateTime) 

			INNER JOIN  #ObjectList ON #ObjectList.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId

WHERE		(Alert.vAlert.RaisedDateTime &gt;= @StartDate) AND 

			(Alert.vAlert.RaisedDateTime &lt; @EndDate) AND 

			(vDateTime.Minute = 0) AND 

			(vDateTime.Date BETWEEN 

            DATEADD(day, - 1, @StartDate) AND 

			DATEADD(day, 1, @EndDate))





  SET @Error = @@ERROR



QuitError:

  DROP TABLE #ObjectList



  RETURN @Error

END

GO



GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AlertLoggingLatencyReportDataGet] TO OpsMgrReader

GO



                </Upgrade>

</DataWarehouseScript>