Res.ComTrade.Citrix.XenDesktop.Reports.ReportScript.ApplicationAvailabilityDowntimeEventsStoredProcedure.Upgrade (Resource)

Element properties:

File Content: ComTrade.Citrix.XenDesktop.Reports.ReportScript.ApplicationAvailabilityDowntimeEventsStoredProcedure.Upgrade.sql

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

BEGIN

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

END

GO



ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationAvailability_DownTimeEvents_DataGet] 

@StartDate_BaseValue  datetime,

@EndDate_BaseValue  datetime,

@BusinessTimeType int, -- 0-Regular, 1-Business

@BusinessDay1 int, --monday

@BusinessDay2 int, --tuesday

@BusinessDay3 int, --wednesday

@BusinessDay4 int, --thursday

@BusinessDay5 int, --friday

@BusinessDay6 int, --saturday

@BusinessDay7 int, --sunday

@StartDate_BaseValueGMT datetime,

@ManagedEntityMonitorRowId int



AS





--calculate time

SET DATEFIRST 1 --monday = 1

DECLARE @GMTDiff int

SELECT @GMTDiff = DATEDIFF(minute, @StartDate_BaseValueGMT, @StartDate_BaseValue)

DECLARE @BH_start datetime --business hour start time

DECLARE @BH_end datetime   --business hour end time

SELECT @BH_start = (@StartDate_BaseValue - CAST(FLOOR(CAST(@StartDate_BaseValue AS float)) AS datetime))

SELECT @BH_end = (@EndDate_BaseValue - CAST(FLOOR(CAST(@EndDate_BaseValue AS float)) AS datetime))	

DECLARE @StartDateTimeGMT datetime

DECLARE @EndDateTimeGMT datetime

SELECT @StartDateTimeGMT = DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)

SELECT @EndDateTimeGMT = DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)







DECLARE @ExecError int

CREATE TABLE #DowntimeEvents (ManagedEntityMonitorRowId int, StartDateTime datetime, EndDateTime datetime, HealthState tinyint)



  DECLARE @StateEvent TABLE (

     ManagedEntityMonitorRowId int

    ,[DateTime]           datetime

    ,OldHealthState       tinyint

    ,NewHealthState       tinyint

  )

  

  INSERT @StateEvent (ManagedEntityMonitorRowId, [DateTime], OldHealthState, NewHealthState)

  EXEC StateChangeEventList 

        @ManagedEntityMonitorRowId = @ManagedEntityMonitorRowId

       ,@IntervalStartDateTime = @StartDateTimeGMT

       ,@IntervalEndDateTime = @EndDateTimeGMT



INSERT INTO #DowntimeEvents

  SELECT 

     ManagedEntityMonitorRowId

    ,FromDateTime

    ,ToDateTime

    ,HealthState

  FROM (SELECT

           l.ManagedEntityMonitorRowId

          ,FromDateTime = l.[DateTime]

          ,ToDateTime = r.[DateTime]

          ,HealthState = 

             CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)

               THEN l.NewHealthState

               ELSE ISNULL(r.OldHealthState, l.NewHealthState)

             END

        FROM @StateEvent l

              LEFT JOIN @StateEvent r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.[DateTime] < r.[DateTime])

        WHERE (NOT EXISTS (SELECT * FROM @StateEvent m

                           WHERE (l.ManagedEntityMonitorRowId = m.ManagedEntityMonitorRowId)

                             AND (m.[DateTime] > l.[DateTime])

                             AND (m.[DateTime] < ISNULL(r.[DateTime], '99991231'))

                          )

              )

      ) AS tbl

  WHERE HealthState <> 1





CREATE TABLE #DowntimeEventsFinal (ManagedEntityMonitorRowId int, StartDateTime datetime, EndDateTime datetime, HealthState tinyint)

INSERT INTO #DowntimeEventsFinal

SELECT dte.ManagedEntityMonitorRowId, DATEADD(minute, @GMTDiff, dte.StartDateTime), DATEADD(minute, @GMTDiff, dte.EndDateTime), dte.HealthState FROM #DowntimeEvents dte



SELECT * from #DowntimeEventsFinal



DROP TABLE #DowntimeEventsFinal

DROP TABLE #DowntimeEvents







GO



/* 	=============================================================== 

	PERMISSIONS 

	===============================================================  */

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_ApplicationAvailability_DownTimeEvents_DataGet] TO OpsMgrReader 



GO