<DataWarehouseScript ID="Microsoft.SystemCenter.PowerManagement.Report.Script" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@ContainmentLevelCount int = 0,
@ContainmentStartLevel int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @RowCount int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF (OBJECT_ID('tempdb..#ContainmentObjectList') IS NOT NULL)
DROP TABLE #ContainmentObjectList
CREATE TABLE #ContainmentObjectList(
ManagedEntityRowId int,
Position int,
[Level] int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)
DROP TABLE #RelationshipType
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT ObjectList.ManagedEntityRowId.value('.', 'int'), ObjectList.ManagedEntityRowId.value('.', 'int'), 0
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Self' -- applies 'Containment' even if it is 'Self' since PowerSet is not a group but contains devices
OR ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Containment'
IF @RowCount > 0
BEGIN
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId FROM vRelationshipType WHERE RelationshipTypeSystemName = 'System.Containment'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipType(RelationshipTypeRowId, [Level])
SELECT RelationshipTypeRowId, [Level]
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @CurrentLevel int
SET @CurrentLevel = 1
WHILE (((@ContainmentLevelCount >= @CurrentLevel) OR (@ContainmentLevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT DISTINCT r.TargetManagedEntityRowId, me.Position, @CurrentLevel
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN #ContainmentObjectList me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (me.[Level] = @CurrentLevel - 1)
WHERE (rmg.FromDateTime <= @EndDate) AND (ISNULL(rmg.ToDateTime, '99991231') >= @StartDate)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
/* ------------------------------ */
SELECT col.ManagedEntityRowId, Position, me.DisplayName as PositionDisplayName
FROM #ContainmentObjectList col
JOIN vManagedEntity me ON me.ManagedEntityRowId = col.Position
WHERE [Level] >= @ContainmentStartLevel
SET @Error = @@ERROR
QuitError:
IF (OBJECT_ID('tempdb..#ContainmentObjectList') IS NOT NULL)
DROP TABLE #ContainmentObjectList
IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)
DROP TABLE #RelationshipType
RETURN @Error
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet] AS RETURN 1')
END
GO
DECLARE @Error int
DECLARE @ExecError int
DECLARE @PowerConsumption nvarchar(256)
DECLARE @PowerCapacity nvarchar(256)
DECLARE @PowerBudget nvarchar(256)
DECLARE @UnmonitoredPowerConsumption nvarchar(256)
--Gets localized strings of four power management perf counters from the report display strings
IF (OBJECT_ID('tempdb..#CounterDisplayName') IS NOT NULL)
DROP TABLE #CounterDisplayName
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #CounterDisplayName (CounterName, DisplayName)
SELECT
CounterName =
CASE vReportDisplayString.ReportDisplayStringSystemName
WHEN 'R.PowerConsumption' THEN 'Power Consumption'
WHEN 'R.PowerCapacity' THEN 'Power Capacity'
WHEN 'R.PowerBudget' THEN 'Power Budget'
WHEN 'R.UnmonitoredPowerConsumption' THEN 'Unmonitored Power Consumption'
END,
ISNULL(vDisplayString.Name, vReportDisplayString.ReportDisplayStringDefaultName) AS DisplayName
FROM vReportDisplayString
INNER JOIN vReport ON vReport.ReportRowId = vReportDisplayString.ReportRowId
LEFT OUTER JOIN vDisplayString ON (vDisplayString.ElementGuid = vReportDisplayString.ReportDisplayStringGuid AND vDisplayString.LanguageCode = @LanguageCode)
WHERE vReport.ReportSystemName = 'Microsoft.SystemCenter.PowerManagement.Report.PowerConsumption'
AND (vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerConsumption'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerCapacity'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerBudget'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.UnmonitoredPowerConsumption')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @PowerConsumption = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Consumption');
SET @PowerCapacity = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Capacity');
SET @PowerBudget = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Budget');
SET @UnmonitoredPowerConsumption = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Unmonitored Power Consumption');
-- Create a table of perf collection rules being used
IF (OBJECT_ID('tempdb..#PowerManagementRule') IS NOT NULL)
DROP TABLE #PowerManagementRule
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #PowerManagementRule (RuleRowId, RuleSystemName)
SELECT RuleRowId, RuleSystemName
FROM vRule
WHERE
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerConsumption.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerCapacity.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerBudget.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
-- Position is ManagedEntityRowId of the top level container
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
CREATE TABLE #ManagedEntity (ManagedEntityRowId int, Position int, PositionDisplayName nvarchar(256))
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError
SELECT
#ManagedEntity.Position,
#ManagedEntity.PositionDisplayName,
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation,
vManagedEntity.ManagedEntityRowId,
CASE WHEN #PowerManagementRule.RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection'
THEN @UnmonitoredPowerConsumption
ELSE ISNULL(vManagedEntity.DisplayName, vManagedEntity.ManagedEntityDefaultName)
END AS DeviceDisplayName,
ChartCounterName =
CASE #PowerManagementRule.RuleSystemName
WHEN 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection' THEN @PowerConsumption -- To add to Power Consumption
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerConsumption.Collection' THEN @PowerConsumption
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerCapacity.Collection' THEN @PowerCapacity
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerBudget.Collection' THEN @PowerBudget
END,
vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM Perf.vPerfHourly AS vPerf
INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN #PowerManagementRule ON #PowerManagementRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = #PowerManagementRule.RuleRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vPerf.ManagedEntityRowId
INNER JOIN #ManagedEntity ON #ManagedEntity.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
WHERE
vPerf.DateTime >= @StartDate AND vPerf.DateTime < @EndDate
SET @Error = @@ERROR
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
IF (OBJECT_ID('tempdb..#CounterDisplayName') IS NOT NULL)
DROP TABLE #CounterDisplayName
IF (OBJECT_ID('tempdb..#PowerManagementRule') IS NOT NULL)
DROP TABLE #PowerManagementRule
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
RETURN @Error
END
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse]
END
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse] AS RETURN 1')
END
GO
ALTER PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_ReportObjectListParse]
@StartDate datetime,
@EndDate datetime,
@ObjectList xml,
@ContainmentLevelCount int = 0,
@ContainmentStartLevel int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @RowCount int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF (OBJECT_ID('tempdb..#ContainmentObjectList') IS NOT NULL)
DROP TABLE #ContainmentObjectList
CREATE TABLE #ContainmentObjectList(
ManagedEntityRowId int,
Position int,
[Level] int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)
DROP TABLE #RelationshipType
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT ObjectList.ManagedEntityRowId.value('.', 'int'), ObjectList.ManagedEntityRowId.value('.', 'int'), 0
FROM @ObjectList.nodes('/Data/Objects/Object') AS ObjectList(ManagedEntityRowId)
WHERE ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Self' -- applies 'Containment' even if it is 'Self' since PowerSet is not a group but contains devices
OR ISNULL(ObjectList.ManagedEntityRowId.value('@Use', 'nvarchar(255)'), 'Self') = 'Containment'
IF @RowCount > 0
BEGIN
DECLARE @ContainmentRelationshipTypeRowId int
SELECT @ContainmentRelationshipTypeRowId = RelationshipTypeRowId FROM vRelationshipType WHERE RelationshipTypeSystemName = 'System.Containment'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT #RelationshipType(RelationshipTypeRowId, [Level])
SELECT RelationshipTypeRowId, [Level]
FROM dbo.RelationshipDerivedTypeHierarchy(@ContainmentRelationshipTypeRowId, 0)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
DECLARE @CurrentLevel int
SET @CurrentLevel = 1
WHILE (((@ContainmentLevelCount >= @CurrentLevel) OR (@ContainmentLevelCount = 0)) AND (@RowCount > 0))
BEGIN
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT DISTINCT r.TargetManagedEntityRowId, me.Position, @CurrentLevel
FROM Relationship r
JOIN RelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN #RelationshipType rt ON (r.RelationshipTypeRowId = rt.RelationshipTypeRowId)
JOIN #ContainmentObjectList me ON (me.ManagedEntityRowId = r.SourceManagedEntityRowId) AND (me.[Level] = @CurrentLevel - 1)
WHERE (rmg.FromDateTime <= @EndDate) AND (ISNULL(rmg.ToDateTime, '99991231') >= @StartDate)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
/* ------------------------------ */
SELECT col.ManagedEntityRowId, Position, me.DisplayName as PositionDisplayName
FROM #ContainmentObjectList col
JOIN vManagedEntity me ON me.ManagedEntityRowId = col.Position
WHERE [Level] >= @ContainmentStartLevel
SET @Error = @@ERROR
QuitError:
IF (OBJECT_ID('tempdb..#ContainmentObjectList') IS NOT NULL)
DROP TABLE #ContainmentObjectList
IF (OBJECT_ID('tempdb..#RelationshipType') IS NOT NULL)
DROP TABLE #RelationshipType
RETURN @Error
END
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_PowerManagement_Library_PerfDataGet] AS RETURN 1')
END
GO
DECLARE @Error int
DECLARE @ExecError int
DECLARE @PowerConsumption nvarchar(256)
DECLARE @PowerCapacity nvarchar(256)
DECLARE @PowerBudget nvarchar(256)
DECLARE @UnmonitoredPowerConsumption nvarchar(256)
--Gets localized strings of four power management perf counters from the report display strings
IF (OBJECT_ID('tempdb..#CounterDisplayName') IS NOT NULL)
DROP TABLE #CounterDisplayName
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #CounterDisplayName (CounterName, DisplayName)
SELECT
CounterName =
CASE vReportDisplayString.ReportDisplayStringSystemName
WHEN 'R.PowerConsumption' THEN 'Power Consumption'
WHEN 'R.PowerCapacity' THEN 'Power Capacity'
WHEN 'R.PowerBudget' THEN 'Power Budget'
WHEN 'R.UnmonitoredPowerConsumption' THEN 'Unmonitored Power Consumption'
END,
ISNULL(vDisplayString.Name, vReportDisplayString.ReportDisplayStringDefaultName) AS DisplayName
FROM vReportDisplayString
INNER JOIN vReport ON vReport.ReportRowId = vReportDisplayString.ReportRowId
LEFT OUTER JOIN vDisplayString ON (vDisplayString.ElementGuid = vReportDisplayString.ReportDisplayStringGuid AND vDisplayString.LanguageCode = @LanguageCode)
WHERE vReport.ReportSystemName = 'Microsoft.SystemCenter.PowerManagement.Report.PowerConsumption'
AND (vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerConsumption'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerCapacity'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.PowerBudget'
OR vReportDisplayString.ReportDisplayStringSystemName = 'R.UnmonitoredPowerConsumption')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @PowerConsumption = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Consumption');
SET @PowerCapacity = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Capacity');
SET @PowerBudget = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Power Budget');
SET @UnmonitoredPowerConsumption = (SELECT DisplayName FROM #CounterDisplayName WHERE CounterName = 'Unmonitored Power Consumption');
-- Create a table of perf collection rules being used
IF (OBJECT_ID('tempdb..#PowerManagementRule') IS NOT NULL)
DROP TABLE #PowerManagementRule
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #PowerManagementRule (RuleRowId, RuleSystemName)
SELECT RuleRowId, RuleSystemName
FROM vRule
WHERE
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerConsumption.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerCapacity.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.PowerBudget.Collection'
OR
RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection'
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
-- Position is ManagedEntityRowId of the top level container
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
CREATE TABLE #ManagedEntity (ManagedEntityRowId int, Position int, PositionDisplayName nvarchar(256))
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError
SELECT
#ManagedEntity.Position,
#ManagedEntity.PositionDisplayName,
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,
vPerf.MaxValue,
vPerf.StandardDeviation,
vManagedEntity.ManagedEntityRowId,
CASE WHEN #PowerManagementRule.RuleSystemName = 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection'
THEN @UnmonitoredPowerConsumption
ELSE ISNULL(vManagedEntity.DisplayName, vManagedEntity.ManagedEntityDefaultName)
END AS DeviceDisplayName,
ChartCounterName =
CASE #PowerManagementRule.RuleSystemName
WHEN 'Microsoft.SystemCenter.PowerManagement.UnmonitoredPowerConsumption.Collection' THEN @PowerConsumption -- To add to Power Consumption
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerConsumption.Collection' THEN @PowerConsumption
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerCapacity.Collection' THEN @PowerCapacity
WHEN 'Microsoft.SystemCenter.PowerManagement.PowerBudget.Collection' THEN @PowerBudget
END,
vPerformanceRule.ObjectName,
vPerformanceRule.CounterName
FROM Perf.vPerfHourly AS vPerf
INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN #PowerManagementRule ON #PowerManagementRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN vPerformanceRule ON vPerformanceRule.RuleRowId = #PowerManagementRule.RuleRowId
INNER JOIN vManagedEntity ON vManagedEntity.ManagedEntityRowId = vPerf.ManagedEntityRowId
INNER JOIN #ManagedEntity ON #ManagedEntity.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
WHERE
vPerf.DateTime >= @StartDate AND vPerf.DateTime < @EndDate
SET @Error = @@ERROR
QuitError:
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
IF (OBJECT_ID('tempdb..#CounterDisplayName') IS NOT NULL)
DROP TABLE #CounterDisplayName
IF (OBJECT_ID('tempdb..#PowerManagementRule') IS NOT NULL)
DROP TABLE #PowerManagementRule
IF (OBJECT_ID('tempdb..#ManagedEntity') IS NOT NULL)
DROP TABLE #ManagedEntity
Home