<DataWarehouseScript ID="Microsoft.SystemCenter.DataWarehouse.Report.Script.CustomPerformance" Accessibility="Public"> <Install>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet]
@StartDate datetime,
@EndDate datetime,
@OptionList xml,
@DataAggregation tinyint = 0,
@LanguageCode varchar(3) = 'ENU',
@UnitScale int
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @xmldoc int
DECLARE @RowCount int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ObjectList
(ManagedEntityRowId int,
Position int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ContainmentObjectList
(ManagedEntityRowId int,
Position int,
[Level] int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #RelationshipType (
RelationshipTypeRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #InstanceList
(PerformanceRuleInstanceRowId int,
InstanceName nvarchar(256),
Position int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @OptionList
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError
INSERT INTO #OptionList ([Group], GroupTitle, Position, ChartScale, ChartType, ChartColor, OptionXml)
SELECT [Group], GroupTitle, Position, ChartScale, ChartType, ChartColor, OptionXml
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
([Group] int '@mp:parentid',
GroupTitle nvarchar(255) '../@Title',
Position int '@mp:id',
ChartScale float 'Scale',
ChartType nvarchar(255) 'Type',
ChartColor nvarchar(255) 'Color',
OptionXml xml '@mp:xmltext')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT ManagedEntityRowId, Position, 0
FROM OPENXML(@xmldoc, '/Data/Values/Value/Object', 2) WITH
(ManagedEntityRowId int 'text()',
Position int '@mp:parentid',
[Use] nvarchar(255) '@Use') AS ObjectList
WHERE ISNULL([Use], '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 (@RowCount > 0)
BEGIN
/* Removed checking for Group creation time before reporting end date, so that Reporting data come for objects irrespective of group's creation time.*/
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT 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)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
INSERT INTO #ObjectList (ManagedEntityRowId, Position)
SELECT ManagedEntityRowId, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value/Object', 2) WITH
(ManagedEntityRowId int 'text()',
Position int '@mp:parentid',
[Use] nvarchar(255) '@Use') AS ObjectList
WHERE ISNULL([Use], 'Self') = 'Self'
UNION
SELECT ManagedEntityRowId, Position
FROM #ContainmentObjectList
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId, InstanceName, Position)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId, vPerformanceRuleInstance.InstanceName, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value/Instance', 2) WITH
(RuleGuid uniqueidentifier '../Rule',
InstanceName nvarchar(256) 'text()',
Position int '@mp:parentid') As InstanceList INNER JOIN
vRule ON (vRule.RuleGuid = InstanceList.RuleGuid) INNER JOIN
vPerformanceRuleInstance ON (vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId) AND
(InstanceList.InstanceName = vPerformanceRuleInstance.InstanceName)
UNION ALL
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId, vPerformanceRuleInstance.InstanceName, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
(RuleGuid uniqueidentifier 'Rule',
Position int '@mp:id') As InstanceList INNER JOIN
vRule ON (vRule.RuleGuid = InstanceList.RuleGuid) INNER JOIN
vPerformanceRuleInstance ON (vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId)
WHERE InstanceList.Position NOT IN (SELECT Position FROM OPENXML(@xmldoc, '/Data/Values/Value/Instance', 2)
WITH( Position int '@mp:parentid'))
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Update #OptionList
Set GroupTitle = [Name]
From
(
Select Me.Name, Se.Position
from #ObjectList Se
Inner Join vManagedEntity Me
On Se.ManagedEntityRowId = Me.ManagedEntityRowId
Inner Join #OptionList Op
On Se.Position = Op.Position
) Upd
Where Upd.Position = #OptionList.Position
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
IF @DataAggregation = 1
SELECT vPerf.DateTime,
vPerf.SampleCount,
(vPerf.AverageValue/@UnitScale) As AverageValue,
(vPerf.MinValue/@UnitScale) As MinValue,
(vPerf.MaxValue/@UnitScale) As MaxValue,
(vPerf.StandardDeviation/@UnitScale) As StandardDeviation,
vPerformanceRuleInstance.RuleRowId,
vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId,
vManagedEntity.ManagedEntityGuid,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid,
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,
vPerformanceRule.MultiInstanceInd,
OptionList.[Group],
OptionList.GroupTitle,
OptionList.Position,
OptionList.ChartScale,
OptionList.ChartType,
OptionList.ChartColor,
OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM Perf.vPerfDaily as vPerf
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN vManagementGroup
ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN #InstanceList As InstanceList
ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)
INNER JOIN #OptionList As OptionList
ON (InstanceList.Position = OptionList.Position)
INNER JOIN #ObjectList AS ObjectList
ON (InstanceList.Position = ObjectList.Position)
AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)
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 vDisplayStringRule
ON vRule.RuleGuid = vDisplayStringRule.ElementGuid
AND vDisplayStringRule.LanguageCode = @LanguageCode
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112)))
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))
ELSE
SELECT vPerf.DateTime, vPerf.SampleCount, (vPerf.AverageValue/@UnitScale) As AverageValue, (vPerf.MinValue/@UnitScale) As MinValue, (vPerf.MaxValue/@UnitScale) As MaxValue, (vPerf.StandardDeviation/@UnitScale) As StandardDeviation,
vPerformanceRuleInstance.RuleRowId, vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid, ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName, vPerformanceRule.MultiInstanceInd,
OptionList.[Group],
OptionList.GroupTitle, OptionList.Position, OptionList.ChartScale, OptionList.ChartType, OptionList.ChartColor, OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM Perf.vPerfHourly as vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN
vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN
#InstanceList As InstanceList ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)INNER JOIN
#OptionList As OptionList ON (InstanceList.Position = OptionList.Position) INNER JOIN
#ObjectList AS ObjectList ON (InstanceList.Position = ObjectList.Position) AND
(vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId) 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 vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND
vDisplayStringRule.LanguageCode = @LanguageCode
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))
SET @Error = @@ERROR
QuitError:
-- remove xml document if opened
IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #OptionList
DROP TABLE #ObjectList
DROP TABLE #ContainmentObjectList
DROP TABLE #RelationshipType
DROP TABLE #InstanceList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet] TO OpsMgrReader
GO
</Install> <Uninstall>
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet')
BEGIN
DROP PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet]
END
GO
</Uninstall> <Upgrade>
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet]
@StartDate datetime,
@EndDate datetime,
@OptionList xml,
@DataAggregation tinyint = 0,
@LanguageCode varchar(3) = 'ENU',
@UnitScale int
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error int
DECLARE @ExecError int
DECLARE @xmldoc int
DECLARE @RowCount int
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ObjectList
(ManagedEntityRowId int,
Position int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #ContainmentObjectList
(ManagedEntityRowId int,
Position int,
[Level] int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #RelationshipType (
RelationshipTypeRowId int,
[Level] int
)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
CREATE TABLE #InstanceList
(PerformanceRuleInstanceRowId int,
InstanceName nvarchar(256),
Position int)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
EXEC @ExecError = sp_xml_preparedocument @xmldoc OUTPUT, @OptionList
SET @Error = @@ERROR
IF @ExecError <> 0 OR @Error <> 0 GOTO QuitError
INSERT INTO #OptionList ([Group], GroupTitle, Position, ChartScale, ChartType, ChartColor, OptionXml)
SELECT [Group], GroupTitle, Position, ChartScale, ChartType, ChartColor, OptionXml
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
([Group] int '@mp:parentid',
GroupTitle nvarchar(255) '../@Title',
Position int '@mp:id',
ChartScale float 'Scale',
ChartType nvarchar(255) 'Type',
ChartColor nvarchar(255) 'Color',
OptionXml xml '@mp:xmltext')
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT ManagedEntityRowId, Position, 0
FROM OPENXML(@xmldoc, '/Data/Values/Value/Object', 2) WITH
(ManagedEntityRowId int 'text()',
Position int '@mp:parentid',
[Use] nvarchar(255) '@Use') AS ObjectList
WHERE ISNULL([Use], '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 (@RowCount > 0)
BEGIN
/* Removed checking for Group creation time before reporting end date, so that Reporting data come for objects irrespective of group's creation time.*/
INSERT INTO #ContainmentObjectList (ManagedEntityRowId, Position, [Level])
SELECT 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)
IF @Error <> 0 OR @ExecError <> 0 GOTO QuitError
END
END
INSERT INTO #ObjectList (ManagedEntityRowId, Position)
SELECT ManagedEntityRowId, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value/Object', 2) WITH
(ManagedEntityRowId int 'text()',
Position int '@mp:parentid',
[Use] nvarchar(255) '@Use') AS ObjectList
WHERE ISNULL([Use], 'Self') = 'Self'
UNION
SELECT ManagedEntityRowId, Position
FROM #ContainmentObjectList
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
INSERT INTO #InstanceList (PerformanceRuleInstanceRowId, InstanceName, Position)
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId, vPerformanceRuleInstance.InstanceName, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value/Instance', 2) WITH
(RuleGuid uniqueidentifier '../Rule',
InstanceName nvarchar(256) 'text()',
Position int '@mp:parentid') As InstanceList INNER JOIN
vRule ON (vRule.RuleGuid = InstanceList.RuleGuid) INNER JOIN
vPerformanceRuleInstance ON (vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId) AND
(InstanceList.InstanceName = vPerformanceRuleInstance.InstanceName)
UNION ALL
SELECT vPerformanceRuleInstance.PerformanceRuleInstanceRowId, vPerformanceRuleInstance.InstanceName, Position
FROM OPENXML(@xmldoc, '/Data/Values/Value', 2) WITH
(RuleGuid uniqueidentifier 'Rule',
Position int '@mp:id') As InstanceList INNER JOIN
vRule ON (vRule.RuleGuid = InstanceList.RuleGuid) INNER JOIN
vPerformanceRuleInstance ON (vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId)
WHERE InstanceList.Position NOT IN (SELECT Position FROM OPENXML(@xmldoc, '/Data/Values/Value/Instance', 2)
WITH( Position int '@mp:parentid'))
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
Update #OptionList
Set GroupTitle = [Name]
From
(
Select Me.Name, Se.Position
from #ObjectList Se
Inner Join vManagedEntity Me
On Se.ManagedEntityRowId = Me.ManagedEntityRowId
Inner Join #OptionList Op
On Se.Position = Op.Position
) Upd
Where Upd.Position = #OptionList.Position
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
IF @DataAggregation = 1
SELECT vPerf.DateTime,
vPerf.SampleCount,
(vPerf.AverageValue/@UnitScale) As AverageValue,
(vPerf.MinValue/@UnitScale) As MinValue,
(vPerf.MaxValue/@UnitScale) As MaxValue,
(vPerf.StandardDeviation/@UnitScale) As StandardDeviation,
vPerformanceRuleInstance.RuleRowId,
vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId,
vManagedEntity.ManagedEntityGuid,
vManagedEntity.ManagedEntityDefaultName,
vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid,
vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid,
ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName,
vPerformanceRule.MultiInstanceInd,
OptionList.[Group],
OptionList.GroupTitle,
OptionList.Position,
OptionList.ChartScale,
OptionList.ChartType,
OptionList.ChartColor,
OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM Perf.vPerfDaily as vPerf
INNER JOIN vPerformanceRuleInstance
ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity
ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
INNER JOIN vManagedEntityType
ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId
INNER JOIN vManagementGroup
ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId
INNER JOIN vRule
ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN vPerformanceRule
ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN #InstanceList As InstanceList
ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)
INNER JOIN #OptionList As OptionList
ON (InstanceList.Position = OptionList.Position)
INNER JOIN #ObjectList AS ObjectList
ON (InstanceList.Position = ObjectList.Position)
AND (vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId)
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 vDisplayStringRule
ON vRule.RuleGuid = vDisplayStringRule.ElementGuid
AND vDisplayStringRule.LanguageCode = @LanguageCode
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112)))
AND (vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))
ELSE
SELECT vPerf.DateTime, vPerf.SampleCount, (vPerf.AverageValue/@UnitScale) As AverageValue, (vPerf.MinValue/@UnitScale) As MinValue, (vPerf.MaxValue/@UnitScale) As MaxValue, (vPerf.StandardDeviation/@UnitScale) As StandardDeviation,
vPerformanceRuleInstance.RuleRowId, vPerformanceRuleInstance.InstanceName,
vManagedEntity.ManagedEntityRowId, vManagedEntity.ManagedEntityGuid, vManagedEntity.ManagedEntityDefaultName, vManagedEntity.Path,
ISNULL(vDisplayString.Name,vManagedEntityType.ManagedEntityTypeDefaultName) AS DisplayName,
vManagementGroup.ManagementGroupGuid, vManagementGroup.ManagementGroupDefaultName,
vRule.RuleGuid, ISNULL(vDisplayStringRule.Name,vRule.RuleDefaultName) AS RuleDisplayName, vPerformanceRule.MultiInstanceInd,
OptionList.[Group],
OptionList.GroupTitle, OptionList.Position, OptionList.ChartScale, OptionList.ChartType, OptionList.ChartColor, OptionList.OptionXml,
vManagedEntityTypeImage.Image
FROM Perf.vPerfHourly as vPerf INNER JOIN
vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON vPerf.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vManagedEntityType ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntity.ManagedEntityTypeRowId INNER JOIN
vManagementGroup ON vManagementGroup.ManagementGroupRowId = vManagedEntity.ManagementGroupRowId INNER JOIN
vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId INNER JOIN
#InstanceList As InstanceList ON (vPerformanceRuleInstance.PerformanceRuleInstanceRowId = InstanceList.PerformanceRuleInstanceRowId)INNER JOIN
#OptionList As OptionList ON (InstanceList.Position = OptionList.Position) INNER JOIN
#ObjectList AS ObjectList ON (InstanceList.Position = ObjectList.Position) AND
(vManagedEntity.ManagedEntityRowId = ObjectList.ManagedEntityRowId) 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 vDisplayStringRule ON vRule.RuleGuid = vDisplayStringRule.ElementGuid AND
vDisplayStringRule.LanguageCode = @LanguageCode
WHERE (vPerf.DateTime >= DATEADD(hh, DATEPART(hh, @StartDate), convert(varchar(8), @StartDate, 112))) AND
(vPerf.DateTime < DATEADD(hh, DATEPART(hh, @EndDate), convert(varchar(8), @EndDate, 112)))
SET @Error = @@ERROR
QuitError:
-- remove xml document if opened
IF @xmldoc IS NOT NULL EXEC sp_xml_removedocument @xmldoc
IF ((@Error = 0) AND (@ExecError <> 0)) SET @Error = @ExecError
DROP TABLE #OptionList
DROP TABLE #ObjectList
DROP TABLE #ContainmentObjectList
DROP TABLE #RelationshipType
DROP TABLE #InstanceList
RETURN @Error
END
GO
GRANT EXECUTE ON dbo.[Microsoft_SystemCenter_DataWarehouse_Report_CustomPerformanceDataGet] TO OpsMgrReader
GO
Home
