IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComTrade_XenDesktop_PerformaceDetail_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComTrade_XenDesktop_PerformaceDetail_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComTrade_XenDesktop_PerformaceDetail_DataGet]
@StartDate datetime,
@EndDate datetime,
@OptionList xml,
@RuleSystemName varchar(256),
@DataAggregation tinyint = 0,
@LanguageCode varchar(3) = 'ENU'
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
/* ------------------------------ */
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
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
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)
--Instances are not yet supported
/*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
(RuleSystemName varchar(256) 'Rule',
Position int '@mp:id') As InstanceList INNER JOIN
vRule ON (vRule.RuleSystemName = InstanceList.RuleSystemName) INNER JOIN
vManagementPack ON vManagementPack.ManagementPackRowId = vRule.ManagementPackRowId 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')) AND (vManagementPack.SealedInd = 1)
SET @Error = @@ERROR
IF @Error <> 0 GOTO QuitError
/* ------------------------------ */
IF @DataAggregation = 1
SELECT vPerf.DateTime, vPerf.SampleCount, vPerf.AverageValue, vPerf.MinValue, vPerf.MaxValue, vPerf.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, vPerf.MinValue, vPerf.MaxValue, vPerf.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
--===============================================================
-- PERMISSIONS
--===============================================================
GRANT EXECUTE ON dbo.[ComTrade_XenDesktop_PerformaceDetail_DataGet] TO OpsMgrReader
GO
Home