<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.AvailabilityMonitor" 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_AvailabilityMonitorReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagedEntityMonitorRowId int,
@DataAggregation tinyint = 0,
@LevelCount int = 0,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
CREATE TABLE #ManagedEntityMonitorDependency
(
[Level] int NOT NULL
,ParentManagedEntityMonitorRowId int NULL
,ChildManagedEntityMonitorRowId int NOT NULL
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ManagedEntityMonitorDependency
EXECUTE @ExecError = ManagedEntityMonitorDependencyList @ManagedEntityMonitorRowId, @LevelCount
SET @Error = @@ERROR
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
/* ------------------------------ */
IF @DataAggregation = 1
SELECT vState.DateTime, vState.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntityMonitor.ManagedEntityMonitorRowId,
vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName, vManagedEntityType.ManagedEntityTypeGuid,
vManagedEntityMonitorDependency.ParentManagedEntityMonitorRowId, vManagedEntityMonitorDependency.[Level], ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,
vManagedEntity.ManagedEntityGuid, vManagedEntity.Path, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName
FROM vStateDailyFull as vState INNER JOIN
vManagedEntityMonitor ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId INNER JOIN
vManagedEntity ON vManagedEntityMonitor.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
#ManagedEntityMonitorDependency
AS vManagedEntityMonitorDependency ON
vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode LEFT OUTER JOIN
vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND
vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate) + 1, convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < 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.InRedStateMilliseconds, vState.InYellowStateMilliseconds,
vState.InGreenStateMilliseconds, vState.InWhiteStateMilliseconds, vState.InDisabledStateMilliseconds,
vState.InPlannedMaintenanceMilliseconds, vState.InUnplannedMaintenanceMilliseconds, vState.HealthServiceUnavailableMilliseconds,
vState.IntervalDurationMilliseconds, vManagedEntity.ManagedEntityRowId, vManagedEntityMonitor.ManagedEntityMonitorRowId,
vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName, vManagedEntityType.ManagedEntityTypeGuid,
vManagedEntityMonitorDependency.ParentManagedEntityMonitorRowId, vManagedEntityMonitorDependency.[Level], ISNULL(vDisplayStringMonitor.Name,vMonitor.MonitorDefaultName) AS MonitorDisplayName,
vManagedEntity.ManagedEntityGuid, vManagedEntity.Path, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName
FROM vStateHourlyFull as vState INNER JOIN
vManagedEntityMonitor ON vState.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId INNER JOIN
vManagedEntity ON vManagedEntityMonitor.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
#ManagedEntityMonitorDependency
AS vManagedEntityMonitorDependency ON
vState.ManagedEntityMonitorRowId = vManagedEntityMonitorDependency.ChildManagedEntityMonitorRowId INNER JOIN
vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId LEFT OUTER JOIN
vManagedEntityTypeImage ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityTypeImage.ManagedEntityTypeRowId AND
vManagedEntityTypeImage.ImageCategory = N'u16x16Icon' LEFT OUTER JOIN
vDisplayString ON vManagedEntityType.ManagedEntityTypeGuid = vDisplayString.ElementGuid AND
vDisplayString.LanguageCode = @LanguageCode LEFT OUTER JOIN
vDisplayString vDisplayStringMonitor ON vMonitor.MonitorGuid = vDisplayStringMonitor.ElementGuid AND
vDisplayStringMonitor.LanguageCode = @LanguageCode
WHERE (vState.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vState.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112))) AND
(vState.Date >= DATEADD(day, -1, @StartDate)) AND (vState.Date < DATEADD(day, 1, @EndDate))
SET @Error = @@ERROR
set statistics io off
QuitError:
DROP TABLE #ManagedEntityMonitorDependency
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityMonitorReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ManagedEntityMonitorRowId int,
@DataAggregation tinyint = 0,
@LevelCount int = 0,
@LanguageCode varchar(3) = 'ENU'
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
CREATE TABLE #ManagedEntityMonitorDependency
(
[Level] int NOT NULL
,ParentManagedEntityMonitorRowId int NULL
,ChildManagedEntityMonitorRowId int NOT NULL
Home
