<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.Availability" 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_AvailabilityReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@MonitorName nvarchar(256),
@DataAggregation tinyint = 0,
@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 <> 0 GOTO QuitError
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, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName
FROM vStateDailyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId 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
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(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, vManagedEntity.ManagedEntityGuid,
vState.ManagedEntityMonitorRowId, vManagedEntityTypeImage.Image, vManagedEntity.ManagedEntityDefaultName,
vManagedEntityType.ManagedEntityTypeGuid, vManagedEntity.Path, vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName, ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName
FROM vStateHourlyFull as vState INNER JOIN
#ObjectList As ObjectList ON vState.ManagedEntityRowId = ObjectList.ManagedEntityRowId INNER JOIN
vManagedEntity ON vState.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId INNER JOIN
vMonitor ON vState.MonitorRowId = vMonitor.MonitorRowId 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
WHERE (vMonitor.MonitorSystemName = @MonitorName) AND
(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 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_AvailabilityReportDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_Library_AvailabilityReportDataGet]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@MonitorName nvarchar(256),
@DataAggregation tinyint = 0,
@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 <> 0 GOTO QuitError
Home
