-- ##### Veeam_SystemCenter_Visualization_Library_TopNEntitiesByPerfGet.sql
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('sdk') AND type = 'P' AND name = 'Veeam_SystemCenter_Visualization_Library_PerfGetByManagedEntityAndRule')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[Veeam_SystemCenter_Visualization_Library_PerfGetByManagedEntityAndRule] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Veeam_SystemCenter_Visualization_Library_PerfGetByManagedEntityAndRule]
-- Add the parameters for the stored procedure here
@ContainerManagedEntityXml nvarchar(max),
@ContainerRuleXml nvarchar(max),
@StartTime DATETIME,
@EndTime DATETIME,
@NumberOfLastPoints int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ExecError INT
IF @EndTime IS NULL SET @EndTime=GETUTCDATE()
DECLARE @xmlhandle int
--read entity list
EXEC @ExecError = sp_xml_preparedocument @xmlhandle OUTPUT, @ContainerManagedEntityXml
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'ContainerManagedEntityXml'
,@ExecError)
INSERT INTO #EntityTable (EntityId, EntityRowId)
SELECT ManagedEntityId, vME.ManagedEntityRowId
FROM
OPENXML(@xmlhandle, '/ManagedEntityIds/ManagedEntityId', 2)
WITH
(ManagedEntityId nvarchar(max) '.') CX
JOIN vManagedEntity vME ON CX.ManagedEntityId = vME.ManagedEntityGuid
EXEC sp_xml_removedocument @xmlhandle
--read rules list
EXEC @ExecError = sp_xml_preparedocument @xmlhandle OUTPUT, @ContainerRuleXml
IF NOT @ExecError = 0
RAISERROR(777971000, 16, 1
,'@ContainerRuleXml'
,@ExecError)
INSERT INTO #RuleTable (RuleId, RuleRowId)
SELECT RuleId, vr.RuleRowId
FROM
OPENXML(@xmlhandle, '/RuleIds/RuleId', 2)
WITH
(RuleId nvarchar(max) '.') CMX
JOIN dbo.vRule vr ON CMX.RuleId = vr.RuleGuid
EXEC sp_xml_removedocument @xmlhandle
;
--get perf data
WITH cte AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY vpr.ManagedEntityRowId,vpr.PerformanceRuleInstanceRowId ORDER BY vpr.[DateTime] desc) AS rowno,vpr.ManagedEntityRowId,vpr.PerformanceRuleInstanceRowId,vpr.[DateTime],vpr.SampleValue
FROM
perf.vPerfRaw vpr
JOIN vPerformanceRuleInstance vpri ON vpri.PerformanceRuleInstanceRowId = vpr.PerformanceRuleInstanceRowId
JOIN #RuleTable rt ON vpri.RuleRowId=rt.RuleRowId
JOIN #EntityTable et ON et.EntityRowId=vpr.ManagedEntityRowId
WHERE vpr.[DateTime] BETWEEN @StartTime AND @EndTime
)
SELECT et.EntityId AS InstanceId, rt.RuleId AS RuleId,vpr2.ObjectName,vpr2.CounterName,vpri.InstanceName, cte.[DateTime] AS TimeSampled,cte.SampleValue AS Value FROM cte
JOIN vPerformanceRuleInstance vpri ON vpri.PerformanceRuleInstanceRowId = cte.PerformanceRuleInstanceRowId
JOIN #RuleTable rt ON vpri.RuleRowId=rt.RuleRowId
JOIN #EntityTable et ON et.EntityRowId=cte.ManagedEntityRowId
JOIN vPerformanceRule vpr2 ON vpr2.RuleRowId = rt.RuleRowId
WHERE rowno<=@NumberOfLastPoints
ORDER BY InstanceId,RuleId, cte.[DateTime] asc
END
GO
GRANT EXECUTE ON sdk.Veeam_SystemCenter_Visualization_Library_PerfGetByManagedEntityAndRule TO OpsMgrReader
GO
Home