ServiceLevelObjectiveManagedEntityDetailDataGet.SP.sql (Resource)

Element properties:

Source Code:

<Resource ID="ServiceLevelObjectiveManagedEntityDetailDataGet.SP.sql" Accessibility="Internal" FileName="ServiceLevelObjectiveManagedEntityDetailDataGet.SP.sql"/>

File Content: ServiceLevelObjectiveManagedEntityDetailDataGet.SP.sql

IF (OBJECTPROPERTY(OBJECT_ID('sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet]'), 'IsProcedure') IS NULL)


BEGIN


		EXECUTE ('CREATE PROCEDURE sdk.[ServiceLevelObjectiveManagedEntityDetailDataGet] AS RETURN 1')


	END


GO





ALTER PROCEDURE [sdk].[ServiceLevelObjectiveManagedEntityDetailDataGet]


	 @StartDate                     datetime


	,@EndDate                       datetime


	,@AggregationTypeId             int


AS


BEGIN


  SET NOCOUNT ON


  


  DECLARE 


     @ErrorInd        bit


    ,@ErrorMessage    nvarchar(max)


    ,@ErrorNumber     int


    ,@ErrorSeverity   int


    ,@ErrorState      int


    ,@ErrorLine       int


    ,@ErrorProcedure  nvarchar(256)


    ,@ErrorMessageText nvarchar(max)





  SET @ErrorInd = 0


  


  DECLARE 


     @Statement nvarchar(max)


     


  -- adjust date & time inline with availability report


  -- exclude minutes, seconds, milliseconds


  -- and add 1 hour in case of daily aggregations


  SELECT 


     @StartDate = DATEADD(hh, DATEPART(hh, @StartDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @StartDate, 112))


    ,@EndDate = DATEADD(hh, DATEPART(hh, @EndDate) + CASE @AggregationTypeId WHEN 20 THEN 0 ELSE 1 END, convert(varchar(8), @EndDate, 112))


     


  BEGIN TRY





    SET @Statement =   


      ' SELECT'


      + '   slome.ServiceLevelObjectiveRowId'


      + '  ,slome.ManagementGroupRowId'


      + '  ,slome.ServiceLevelObjectiveManagedEntityRowId'


      + '  ,s.DateTime'


      + '  ,ActualValue = 100.0 * ('


      + '                    s.IntervalDurationMilliseconds '


      + '                  - s.InRedStateMilliseconds'


      + '                  - s.InYellowStateMilliseconds * mslompv.YellowStateUndesiredInd'


      + '                  - s.InPlannedMaintenanceMilliseconds * mslompv.PlannedMaintenanceStateUndesiredInd'


      + '                  - s.InUnplannedMaintenanceMilliseconds * mslompv.UnplannedMaintenanceStateUndesiredInd'


      + '                  - s.InDisabledStateMilliseconds * mslompv.DisabledStateUndesiredInd'


      + '                  - s.HealthServiceUnavailableMilliseconds * mslompv.HealthServiceUnavailableStateUndesiredInd'


      + '                  - s.InWhiteStateMilliseconds * mslompv.UnmonitoredStateUndersiredInd'


      + '                 ) / s.IntervalDurationMilliseconds'


      + '  ,Weight = 1'


      + '  ,TotalDowntimeMilliseconds = '


      + '             s.InRedStateMilliseconds'


      + '                  + s.InYellowStateMilliseconds * mslompv.YellowStateUndesiredInd'


      + '                  + s.InPlannedMaintenanceMilliseconds * mslompv.PlannedMaintenanceStateUndesiredInd'


      + '                  + s.InUnplannedMaintenanceMilliseconds * mslompv.UnplannedMaintenanceStateUndesiredInd'


      + '                  + s.InDisabledStateMilliseconds * mslompv.DisabledStateUndesiredInd'


      + '                  + s.HealthServiceUnavailableMilliseconds * mslompv.HealthServiceUnavailableStateUndesiredInd'


      + '                  + s.InWhiteStateMilliseconds * mslompv.UnmonitoredStateUndersiredInd'


      + ' FROM #ServiceLevelObjectiveManagedEntity slome'


      + '      JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)'


      + '      JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)' 


      + '      JOIN vMonitorServiceLevelObjectiveManagementPackVersion mslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = mslompv.ServiceLevelObjectiveManagementPackVersionRowId)'


      + '      JOIN vManagedEntityMonitor mem ON (slome.ServiceLevelObjectiveManagedEntityRowId = mem.ManagedEntityRowId) AND (mem.MonitorRowId = mslompv.MonitorRowId)'


      + '      JOIN %StateDataTable% s ON (mem.ManagedEntityMonitorRowId = s.ManagedEntityMonitorRowId)'


      + ' WHERE(s.[DateTime] >= @StartDate)'


      + '  AND (s.[DateTime] < @EndDate)'


      + '  AND (s.[Date] BETWEEN DATEADD(day, -1, @StartDate) AND DATEADD(day, 1, @EndDate))'


      + '  AND NOT EXISTS (SELECT *'


      + '                  FROM vServiceLevelObjectiveManagementPackVersion slompv_newer'


      + '                        JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)'


      + '                  WHERE (slompv_newer.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)'


      + '                    AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)'


      + '                    AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)'


      + '                 )'





      + ' UNION ALL'





      + ' SELECT'


      + '   slome.ServiceLevelObjectiveRowId'


      + '  ,slome.ManagementGroupRowId'


      + '  ,slome.ServiceLevelObjectiveManagedEntityRowId'


      + '  ,p.DateTime'


      + '  ,CASE pslompv.AggregationMethod'


      + '     WHEN ''min'' THEN p.MinValue'


      + '     WHEN ''avg'' THEN p.AverageValue'


      + '     WHEN ''max'' THEN p.MaxValue'


      + '   END '


      + '  ,p.SampleCount'


      + '  ,NULL'


      + ' FROM #ServiceLevelObjectiveManagedEntity slome'


      + '      JOIN vServiceLevelObjectiveManagementPackVersion slompv ON (slompv.ServiceLevelObjectiveRowId = slome.ServiceLevelObjectiveRowId)'


      + '      JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId) AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)' 


      + '      JOIN vPerformanceServiceLevelObjectiveManagementPackVersion pslompv ON (slompv.ServiceLevelObjectiveManagementPackVersionRowId = pslompv.ServiceLevelObjectiveManagementPackVersionRowId)'


      + '      JOIN vPerformanceRuleInstance pri ON (pslompv.RuleRowId = pri.RuleRowId)'


      + '      JOIN %PerfDataTable% p ON (pri.PerformanceRuleInstanceRowId = p.PerformanceRuleInstanceRowId) AND (slome.ServiceLevelObjectiveManagedEntityRowId = p.ManagedEntityRowId)'


      + ' WHERE(p.[DateTime] >= @StartDate)'


      + '  AND (p.[DateTime] < @EndDate)'


      + '  AND NOT EXISTS (SELECT *'


      + '                  FROM vServiceLevelObjectiveManagementPackVersion slompv_newer'


      + '                        JOIN vManagementGroupManagementPackVersion mgmpv ON (slompv_newer.ManagementPackVersionRowId = mgmpv.ManagementPackVersionRowId)'


      + '                  WHERE (slompv_newer.ServiceLevelObjectiveRowId = slompv.ServiceLevelObjectiveRowId)'


      + '                    AND (mgmpv.ManagementGroupRowId = slome.ManagementGroupRowId)'


      + '                    AND (slompv_newer.ServiceLevelObjectiveManagementPackVersionRowId > slompv.ServiceLevelObjectiveManagementPackVersionRowId)'


      + '                 )'





    IF (@AggregationTypeId = 20)


    BEGIN


      SET @Statement = REPLACE(@Statement, '%StateDataTable%', 'GetStateHourly(@StartDate, @EndDate)')


      SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfHourly]')


    END


    ELSE IF (@AggregationTypeId = 30)


    BEGIN


      SET @Statement = REPLACE(@Statement, '%StateDataTable%', 'GetStateDaily(@StartDate, @EndDate)')


      SET @Statement = REPLACE(@Statement, '%PerfDataTable%', '[Perf].[vPerfDaily]')


    END





    EXEC sp_executesql @Statement, N'@StartDate datetime, @EndDate datetime', @StartDate = @StartDate, @EndDate = @EndDate


  END TRY


  BEGIN CATCH


    SELECT 


       @ErrorNumber = ERROR_NUMBER()


      ,@ErrorSeverity = ERROR_SEVERITY()


      ,@ErrorState = ERROR_STATE()


      ,@ErrorLine = ERROR_LINE()


      ,@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-')


      ,@ErrorMessageText = ERROR_MESSAGE()





    SET @ErrorInd = 1


  END CATCH





Cleanup:





  -- report error if any


  IF (@ErrorInd = 1)


  BEGIN


    DECLARE @AdjustedErrorSeverity int





    SET @AdjustedErrorSeverity = CASE


                                   WHEN @ErrorSeverity > 18 THEN 18


                                   ELSE @ErrorSeverity


                                 END


    


    RAISERROR (777971002, @AdjustedErrorSeverity, 1


      ,@ErrorNumber


      ,@ErrorSeverity


      ,@ErrorState


      ,@ErrorProcedure


      ,@ErrorLine


      ,@ErrorMessageText


    )


  END


END