-----
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('sdk') AND type = 'P' AND name = 'Veeam_VE_Widget_StateSummaryByGroupGet')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[Veeam_VE_Widget_StateSummaryByGroupGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Veeam_VE_Widget_StateSummaryByGroupGet]
@DataXml NVARCHAR(MAX)
, @ItemsXml NVARCHAR(MAX)
, @Include50State INT =0
AS
BEGIN
BEGIN TRY
IF (OBJECT_ID('tempdb..#StateEventChange') IS NOT NULL) DROP TABLE #StateEventChange
CREATE TABLE #StateEventChange
(
ManagedEntityMonitorRowId INT
,ManagedEntityRowId int
,[DateTime] datetime
,OldHealthState tinyint
,NewHealthState tinyint
)
CREATE CLUSTERED INDEX IDX_T_StateEvCh ON #StateEventChange(ManagedEntityMonitorRowId)
IF (OBJECT_ID('tempdb..#monitor') IS NOT NULL) DROP TABLE #monitor
CREATE TABLE #monitor
(
ManagedEntityMonitorRowId INT
,ManagedEntityRowId int
,GroupName NVARCHAR(MAX)
,ClassName NVARCHAR(MAX)
)
CREATE CLUSTERED INDEX IDX_T_Mon ON #monitor (ManagedEntityMonitorRowId)
DECLARE
@MonitorRowId INT
,@ExecResult INT
,@XmlDocHandle INT
,@XmlDocItemsHandle INT
SELECT @MonitorRowId=vm.MonitorRowId
FROM dbo.vMonitor AS vm WHERE vm.MonitorSystemName=N'System.Health.EntityState'
CREATE TABLE #rootentities(
ManagedEntityRowId INT
,ManagedEntityGuid UNIQUEIDENTIFIER
)
INSERT INTO #rootentities
(
ManagedEntityRowId,
ManagedEntityGuid
)
SELECT
me.ManagedEntityRowId
,x.Id
FROM OPENXML (@XmlDocItemsHandle, '/Root/Item', 1)
WITH(
Id NVARCHAR(MAX)
) x
JOIN ManagedEntity me ON me.ManagedEntityGuid=CAST(x.Id AS UNIQUEIDENTIFIER)
CREATE TABLE #metypes(
ManagedEntityTypeRowId INT
)
INSERT INTO #metypes
(
ManagedEntityTypeRowId
)
SELECT
met.ManagedEntityTypeRowId
FROM OPENXML (@XmlDocHandle, '/Root/Group/Class', 1)
WITH(
name NVARCHAR(MAX)
) x
JOIN ManagedEntityType met ON met.ManagedEntityTypeSystemName=x.name
DECLARE @rootObjects XML
SET @rootObjects =
(
SELECT
(
SELECT
r.ManagedEntityRowId AS [Object]
FROM #rootentities AS r
FOR XML PATH('') ,TYPE
)
FOR XML PATH('Objects') , ROOT('Data'),TYPE
)
CREATE TABLE #entities
(
ManagedEntityRowId INT,
)
INSERT INTO #entities
(
ManagedEntityRowId
)
SELECT
vme.ManagedEntityRowId
FROM @xmlout.nodes('/Objects/Object/ManagedEntityRowId') AS n(p)
INNER JOIN dbo.vManagedEntity AS vme ON vme.ManagedEntityRowId=n.p.value('.','int') AND vme.ManagedEntityTypeRowId IN (SELECT m.ManagedEntityTypeRowId
FROM #metypes m)
;WITH cte(GroupName,ClassName,ManagedEntityRowId)
AS
(
SELECT DISTINCT
GroupName,
ClassName,
vme.ManagedEntityRowId
FROM OPENXML(@XmlDocHandle, '/Root/Group/Class', 2)
WITH
(
GroupName NVARCHAR(MAX) '../@name',
ClassName NVARCHAR(MAX) '@name'
)
INNER JOIN dbo.vManagedEntityType AS vmet ON vmet.ManagedEntityTypeSystemName = ClassName
INNER JOIN dbo.vManagedEntity AS vme ON vmet.ManagedEntityTypeRowId=vme.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityManagementGroup AS vmemg ON vmemg.ManagedEntityRowId=vme.ManagedEntityRowId
JOIN #entities ON #entities.ManagedEntityRowId = vme.ManagedEntityRowId
WHERE vmemg.ToDateTime IS NULL
)
INSERT INTO #monitor
(
ManagedEntityMonitorRowId,
ManagedEntityRowId,
GroupName,
ClassName
)
SELECT
DISTINCT
vmem.ManagedEntityMonitorRowId,
vmem.ManagedEntityRowId,
o.GroupName,
o.ClassName
FROM
[dbo].[vManagedEntityMonitor] vmem
INNER JOIN [dbo].[vMonitorManagementPackVersion] vmmpv ON vmmpv.MonitorRowId = vmem.MonitorRowId
INNER JOIN cte o ON o.ManagedEntityRowId = vmem.ManagedEntityRowId
WHERE vmem.MonitorRowId = @MonitorRowId
;WITH lastStateChange(ManagedEntityMonitorRowId,ManagedEntityRowId,[DateTime])
AS
(
SELECT
s.ManagedEntityMonitorRowId
,m.ManagedEntityRowId
,MAX(s.[DateTime])
FROM [State].[vStateRaw] s
JOIN #monitor AS m ON m.ManagedEntityMonitorRowId=s.ManagedEntityMonitorRowId
GROUP BY
s.ManagedEntityMonitorRowId
,m.ManagedEntityRowId
)
INSERT INTO #StateEventChange
(
ManagedEntityMonitorRowId,
ManagedEntityRowId,
[DateTime],
OldHealthState,
NewHealthState
)
SELECT
s.ManagedEntityMonitorRowId
,m.ManagedEntityRowId
,s.[DateTime]
,0
,s.NewHealthState
FROM [State].[vStateRaw] s
JOIN lastStateChange AS sd ON (s.ManagedEntityMonitorRowId = sd.ManagedEntityMonitorRowId) AND (s.[DateTime] = sd.[DateTime])
JOIN #monitor AS m ON m.ManagedEntityMonitorRowId=s.ManagedEntityMonitorRowId
IF EXISTS (SELECT * FROM StateHealthServiceOutage)
BEGIN
IF (OBJECT_ID('tempdb..#ManagesRelationshipType') IS NOT NULL) DROP TABLE #ManagesRelationshipType
CREATE TABLE #ManagesRelationshipType
(
RelationshipTypeRowId int NOT NULL
)
INSERT #ManagesRelationshipType
(
RelationshipTypeRowId
)
SELECT
RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.HealthServiceManagesEntity')
INSERT #ManagesRelationshipType
(
RelationshipTypeRowId
)
SELECT
RelationshipTypeRowId
FROM RelationshipType rt
WHERE (rt.RelationshipTypeSystemName = 'Microsoft.SystemCenter.ManagementActionPointManagesEntity')
IF (OBJECT_ID('tempdb..#HealthServiceOutageEvent') IS NOT NULL) DROP TABLE #HealthServiceOutageEvent
CREATE TABLE #HealthServiceOutageEvent
(
ManagedEntityRowId int NOT NULL
,[DateTime] datetime NOT NULL
,OldHealthState tinyint NOT NULL
,NewHealthState tinyint NOT NULL
)
CREATE CLUSTERED INDEX IDX_T_HeaSeOuEv ON #HealthServiceOutageEvent (ManagedEntityRowId)
IF @Include50State=1
BEGIN
INSERT #HealthServiceOutageEvent
(
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT DISTINCT
hso.ManagedEntityRowId
,CASE
WHEN interval.StartIntervalInd = 1 THEN hso.StartDateTime
ELSE ISNULL(hso.EndDateTime, '99991230')
END
,CASE WHEN interval.StartIntervalInd = 1 THEN 0 ELSE CASE WHEN hso.EndDateTime IS NULL THEN 1 ELSE 50 END END
,CASE WHEN interval.StartIntervalInd = 1 THEN 50
ELSE CASE WHEN hso.EndDateTime IS NULL THEN 50 ELSE 1 END
END
FROM vHealthServiceOutage hso
JOIN StateHealthServiceOutage shso ON (hso.ReasonCode = shso.ReasonCode)
CROSS JOIN (SELECT StartIntervalInd = 0
UNION ALL
SELECT 1
) AS interval
WHERE NOT EXISTS (SELECT *
FROM StateInvalidatedHealthServiceOutage
WHERE (HealthServiceOutageRowId = hso.HealthServiceOutageRowId)
)
IF EXISTS (SELECT * FROM #HealthServiceOutageEvent)
BEGIN
DECLARE @agentTypeId INT
SELECT @agentTypeId=vmet.ManagedEntityTypeRowId
FROM dbo.vManagedEntityType AS vmet WHERE vmet.ManagedEntityTypeSystemName='Microsoft.SystemCenter.HealthService'
INSERT #HealthServiceOutageEvent
(
ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
SELECT
vme.ManagedEntityRowId,
GETUTCDATE(),
50,
1
FROM dbo.vManagedEntity AS vme
LEFT OUTER JOIN #HealthServiceOutageEvent hso ON hso.ManagedEntityRowId=vme.ManagedEntityRowId
WHERE vme.ManagedEntityTypeRowId = @agentTypeId AND hso.ManagedEntityRowId IS NULL
;with cte(ManagedEntityMonitorRowId,SourceManagedEntityRowId,ManagedEntityRowId,[DateTime])
as(
SELECT
mem.ManagedEntityMonitorRowId
,r.SourceManagedEntityRowId
,m.ManagedEntityRowId
,MAX(hso.[DateTime])
FROM #HealthServiceOutageEvent hso
JOIN vRelationship r ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN #ManagesRelationshipType mrt ON (mrt.RelationshipTypeRowId = r.RelationshipTypeRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vManagedEntity me ON (r.TargetManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
INNER JOIN #monitor AS m ON m.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId
where rmg.ToDateTime IS NULL
group by
mem.ManagedEntityMonitorRowId
,r.SourceManagedEntityRowId
,m.ManagedEntityRowId
),
allState(ManagedEntityMonitorRowId,SourceManagedEntityRowId,ManagedEntityRowId,[DateTime],OldHealthState,NewHealthState)
as
(
SELECT
mem.ManagedEntityMonitorRowId
,r.SourceManagedEntityRowId
,m.ManagedEntityRowId
,hso.[DateTime]
,hso.OldHealthState
,hso.NewHealthState
FROM #HealthServiceOutageEvent hso
JOIN vRelationship r ON (r.SourceManagedEntityRowId = hso.ManagedEntityRowId)
JOIN #ManagesRelationshipType mrt ON (mrt.RelationshipTypeRowId = r.RelationshipTypeRowId)
JOIN vRelationshipManagementGroup rmg ON (r.RelationshipRowId = rmg.RelationshipRowId)
JOIN vManagedEntity me ON (r.TargetManagedEntityRowId = me.TopLevelHostManagedEntityRowId)
JOIN vManagedEntityMonitor mem ON (me.ManagedEntityRowId = mem.ManagedEntityRowId)
INNER JOIN #monitor AS m ON m.ManagedEntityMonitorRowId = mem.ManagedEntityMonitorRowId
WHERE rmg.ToDateTime IS NULL --GETUTCDATE() BETWEEN rmg.FromDateTime AND ISNULL(rmg.ToDateTime, '99991231')
AND (mem.MonitorRowId = @MonitorRowId)
),
bestState(ManagedEntityRowId,ManagedEntityMonitorRowId,NewHealthState)
as
(
select
bs.ManagedEntityRowId,
bs.ManagedEntityMonitorRowId,
MIN(bs.NewHealthState)
from allState bs
join cte c on c.[DateTime]=bs.[DateTime] and bs.ManagedEntityRowId=c.ManagedEntityRowId
group by bs.ManagedEntityRowId,
bs.ManagedEntityMonitorRowId
)
INSERT #StateEventChange
(
ManagedEntityMonitorRowId
,ManagedEntityRowId
,[DateTime]
,OldHealthState
,NewHealthState
)
select
bs.ManagedEntityMonitorRowId,
bs.ManagedEntityRowId,
c.[DateTime],
als.OldHealthState,
bs.NewHealthState
from bestState bs
JOIN allState als ON als.ManagedEntityRowId=bs.ManagedEntityRowId and
als.ManagedEntityMonitorRowId=bs.ManagedEntityMonitorRowId and
als.NewHealthState=bs.NewHealthState
join cte c ON c.ManagedEntityRowId=als.ManagedEntityRowId and
c.SourceManagedEntityRowId=als.SourceManagedEntityRowId and
c.ManagedEntityMonitorRowId=als.ManagedEntityMonitorRowId
UPDATE se
SET se.NewHealthState = ISNULL(previous_real_transition.NewHealthState, 1)
FROM #StateEventChange se
JOIN (
SELECT
MEMRowId = previous_se.ManagedEntityMonitorRowId
,CutOffDateTime = cutoff_se.[DateTime]
,previous_se.NewHealthState
,StateEventNumber = RANK() OVER (PARTITION BY previous_se.ManagedEntityMonitorRowId, cutoff_se.[DateTime] ORDER BY previous_se.[DateTime] DESC, previous_se.NewHealthState DESC)
FROM #StateEventChange cutoff_se
JOIN #StateEventChange previous_se ON (cutoff_se.ManagedEntityMonitorRowId = previous_se.ManagedEntityMonitorRowId) AND (cutoff_se.[DateTime] > previous_se.[DateTime])
WHERE (cutoff_se.OldHealthState = 50)
AND (previous_se.OldHealthState < 50)
AND (previous_se.NewHealthState < 50) -- real state transition, not hso or mmode
) AS previous_real_transition ON (se.ManagedEntityMonitorRowId = previous_real_transition.MEMRowId) AND (se.[DateTime] = previous_real_transition.CutOffDateTime)
WHERE (se.OldHealthState = 50)
AND (previous_real_transition.StateEventNumber = 1)
END
END
END
IF (OBJECT_ID('tempdb..#StateEventRanked') IS NOT NULL)
DROP TABLE #StateEventRanked
CREATE TABLE #StateEventRanked
(
ManagedEntityMonitorRowId INT
,ManagedEntityRowId INT
,[DateTime] datetime
,IntervalNumber int
,OldHealthState tinyint
,NewHealthState tinyint
)
CREATE CLUSTERED INDEX IDX_T_StEvRa ON #StateEventRanked (ManagedEntityRowId,ManagedEntityMonitorRowId,IntervalNumber)
INSERT #StateEventRanked (ManagedEntityMonitorRowId, ManagedEntityRowId, [DateTime], IntervalNumber, OldHealthState, NewHealthState)
SELECT
ManagedEntityMonitorRowId
,ManagedEntityRowId
,[DateTime]
,IntervalNumber = RANK() OVER (PARTITION BY ManagedEntityMonitorRowId ORDER BY [DateTime])
,OldHealthState
,NewHealthState
FROM #StateEventChange
IF (OBJECT_ID('tempdb..#StateInterval') IS NOT NULL)
DROP TABLE #StateInterval
CREATE TABLE #StateInterval
(
ManagedEntityMonitorRowId INT
,ManagedEntityRowId INT
,IntervalStartDateTime DATETIME
,IntervalEndDateTime DATETIME
,HealthState TINYINT
)
CREATE CLUSTERED INDEX IDX_T_SI ON #StateInterval (ManagedEntityRowId,IntervalStartDateTime desc,HealthState)
INSERT #StateInterval
(
ManagedEntityMonitorRowId
,ManagedEntityRowId
,IntervalStartDateTime
,IntervalEndDateTime
,HealthState
)
SELECT
l.ManagedEntityMonitorRowId
,l.ManagedEntityRowId
,l.[DateTime]
,r.[DateTime]
,CASE WHEN l.NewHealthState > ISNULL(r.OldHealthState, l.NewHealthState)
THEN l.NewHealthState
ELSE ISNULL(r.OldHealthState, l.NewHealthState)
END
FROM #StateEventRanked AS l
LEFT JOIN #StateEventRanked AS r ON (l.ManagedEntityMonitorRowId = r.ManagedEntityMonitorRowId) AND (l.[DateTime] + 1 = r.IntervalNumber)
;WITH lastStateChangeDate(IntervalStartDateTime,ManagedEntityRowId)
AS
(
SELECT
MAX(si.IntervalStartDateTime),
si.ManagedEntityRowId
FROM #StateInterval AS si
GROUP BY si.ManagedEntityRowId
),
distinctState(GroupName,ManagedEntityRowId,Success,Critical,Warning,[Disabled],MaintenanceMode,Unmonitored)
AS
(
SELECT DISTINCT
GroupName=m.GroupName,
m.ManagedEntityRowId,
Success=CASE WHEN si.HealthState = 1 AND vmm.ManagedEntityRowId IS NULL THEN 1 ELSE 0 END,
Critical=CASE WHEN si.HealthState = 3 AND vmm.ManagedEntityRowId IS NULL THEN 1 ELSE 0 END,
Warning=CASE WHEN si.HealthState = 2 AND vmm.ManagedEntityRowId IS NULL THEN 1 ELSE 0 END,
[Disabled]=CASE WHEN si.HealthState = 0 AND vmm.ManagedEntityRowId IS NULL THEN 1 ELSE 0 END,
MaintenanceMode=CASE WHEN vmm.ManagedEntityRowId IS NOT NULL THEN 1 ELSE 0 END,
Unmonitored=CASE WHEN si.HealthState = 50 AND vmm.ManagedEntityRowId IS NULL THEN 1 ELSE 0 END
FROM lastStateChangeDate AS lscd
INNER JOIN #StateInterval AS si ON si.IntervalStartDateTime = lscd.IntervalStartDateTime
AND si.ManagedEntityRowId = lscd.ManagedEntityRowId
INNER JOIN #monitor AS m ON m.ManagedEntityRowId = si.ManagedEntityRowId
LEFT OUTER JOIN dbo.vMaintenanceMode AS vmm ON vmm.ManagedEntityRowId = si.ManagedEntityRowId AND vmm.EndDateTime IS NULL
)
SELECT
ds.GroupName,
Success=SUM(ds.Success),
Critical=SUM(ds.Critical),
Warning=SUM(ds.Warning),
[Disabled]=SUM(ds.[Disabled]),
MaintenanceMode=SUM(ds.MaintenanceMode),
Unmonitored=SUM(ds.Unmonitored)
FROM distinctState AS ds
GROUP BY ds.GroupName
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..#thresholds') IS NOT NULL DROP TABLE #thresholds
DECLARE @errMsg VARCHAR(1024)
SET @errMsg = ERROR_MESSAGE()
RAISERROR(@errMsg, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON sdk.[Veeam_VE_Widget_StateSummaryByGroupGet] TO OpsMgrReader
GO
-------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [schema_id]=SCHEMA_ID('sdk') AND type = 'P' AND name = 'Veeam_VE_Widget_PerfSummaryByGroupGet')
BEGIN
EXECUTE ('CREATE PROCEDURE sdk.[Veeam_VE_Widget_PerfSummaryByGroupGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [sdk].[Veeam_VE_Widget_PerfSummaryByGroupGet]
@DataXml NVARCHAR(MAX)
, @ItemsXml NVARCHAR(MAX)
AS
BEGIN
BEGIN TRY
DECLARE
@ExecResult INT,
@XmlDocHandle INT,
@XmlDocItemsHandle INT,
@ObjectParam NVARCHAR(MAX)
CREATE TABLE #rootentities(
ManagedEntityRowId INT
,ManagedEntityGuid UNIQUEIDENTIFIER
)
INSERT INTO #rootentities
(
ManagedEntityRowId,
ManagedEntityGuid
)
SELECT
me.ManagedEntityRowId
,x.Id
FROM OPENXML (@XmlDocItemsHandle, '/Root/Item', 1)
WITH(
Id NVARCHAR(MAX)
) x
JOIN ManagedEntity me ON me.ManagedEntityGuid=CAST(x.Id AS UNIQUEIDENTIFIER)
CREATE TABLE #metypes(
ManagedEntityTypeRowId INT
)
INSERT INTO #metypes
(
ManagedEntityTypeRowId
)
SELECT
met.ManagedEntityTypeRowId
FROM OPENXML (@XmlDocHandle, '/Root/Group/Class', 1)
WITH(
name NVARCHAR(MAX)
) x
JOIN ManagedEntityType met ON met.ManagedEntityTypeSystemName=x.name
DECLARE @rootObjects XML
SET @rootObjects =
(
SELECT
(
SELECT
r.ManagedEntityRowId AS [Object]
FROM #rootentities AS r
FOR XML PATH('') ,TYPE
)
FOR XML PATH('Objects') , ROOT('Data'),TYPE
)
CREATE TABLE #entities
(
ManagedEntityRowId INT,
)
INSERT INTO #entities
(
ManagedEntityRowId
)
SELECT
vme.ManagedEntityRowId
FROM @xmlout.nodes('/Objects/Object/ManagedEntityRowId') AS n(p)
INNER JOIN dbo.vManagedEntity AS vme ON vme.ManagedEntityRowId=n.p.value('.','int') AND vme.ManagedEntityTypeRowId IN (SELECT m.ManagedEntityTypeRowId
FROM #metypes m)
;WITH parseXml(GroupName,ClassName,RuleId,PropertyId,ManagedEntityRowId,ManagedEntityTypeRowId)
AS
(
SELECT
DISTINCT
GroupName,
ClassName,
RuleId,
PropertyId,
vme.ManagedEntityRowId,
vme.ManagedEntityTypeRowId
FROM OPENXML(@XmlDocHandle, '/Root/Group/Class/Datapair', 2)
WITH
(
GroupName NVARCHAR(MAX) '../../@name',
ClassName NVARCHAR(MAX) '../@name',
RuleId NVARCHAR(MAX) '@rule',
PropertyId NVARCHAR(MAX) '@property'
)
INNER JOIN dbo.vManagedEntityType AS vmet ON vmet.ManagedEntityTypeSystemName = ClassName
INNER JOIN dbo.vManagedEntity AS vme ON vmet.ManagedEntityTypeRowId=vme.ManagedEntityTypeRowId
INNER JOIN dbo.vManagedEntityManagementGroup AS vmemg ON vmemg.ManagedEntityRowId=vme.ManagedEntityRowId
JOIN #entities ON #entities.ManagedEntityRowId = vme.ManagedEntityRowId
WHERE vmemg.ToDateTime IS NULL
),
property(PropertyGuid,GroupName,ClassName,ManagedEntityRowId,ManagedEntityTypeRowId,PropertyXml,RuleId)
AS
(
SELECT
vmetp.PropertyGuid,
px.GroupName,
px.ClassName,
px.ManagedEntityRowId,
px.ManagedEntityTypeRowId,
vmep.PropertyXml,
px.RuleId
FROM dbo.vManagedEntityTypeProperty AS vmetp
INNER JOIN parseXml AS px ON px.ManagedEntityTypeRowId = vmetp.ManagedEntityTypeRowId AND px.PropertyId=vmetp.PropertySystemName
INNER JOIN dbo.vManagedEntityProperty AS vmep ON px.ManagedEntityRowId=vmep.ManagedEntityRowId
WHERE vmep.ToDateTime IS NULL
),
propertyvalue(PropertyGuid,PropertyValue,GroupName,ClassName,ManagedEntityRowId,ManagedEntityTypeRowId,RuleId)
AS
(
SELECT
PropertyGuid = T.p.value('@Guid', 'uniqueidentifier'),
PropertyValue = CASE WHEN ISNUMERIC(T.p.value('data(.)', 'nvarchar(max)')) = 1 THEN CONVERT(FLOAT, T.p.value('data(.)', 'nvarchar(max)')) ELSE T.p.value('data(.)', 'nvarchar(max)') END,
p.GroupName,
p.ClassName,
p.ManagedEntityRowId,
p.ManagedEntityTypeRowId,
p.RuleId
FROM property AS p
CROSS APPLY p.PropertyXml.nodes('Root/Property') AS T(p)
WHERE T.p.value('@Guid', 'uniqueidentifier')=p.PropertyGuid
),
perfdata(PerformanceRuleInstanceRowId,ManagedEntityRowId,[DateTime],RuleSystemName,GroupName)
AS
(
SELECT DISTINCT
pri.PerformanceRuleInstanceRowId,
vpr.ManagedEntityRowId,
MAX(vpr.[DateTime]),
r.RuleSystemName,
px.GroupName
FROM parseXml AS px
INNER JOIN dbo.[Rule] AS r ON r.RuleSystemName=px.RuleId
INNER JOIN dbo.PerformanceRule AS pr ON pr.RuleRowId = r.RuleRowId
INNER JOIN dbo.PerformanceRuleInstance AS pri ON pri.RuleRowId = pr.RuleRowId
INNER JOIN perf.vPerfRaw AS vpr ON vpr.PerformanceRuleInstanceRowId = pri.PerformanceRuleInstanceRowId AND vpr.ManagedEntityRowId=px.ManagedEntityRowId
INNER JOIN dbo.vManagedEntity AS vme ON vme.ManagedEntityRowId = vpr.ManagedEntityRowId
GROUP BY
pri.PerformanceRuleInstanceRowId,
vpr.ManagedEntityRowId,
r.RuleSystemName,
px.GroupName
),
[result](GroupName,Value,Total)
AS
(
SELECT
pd.GroupName,
[Value]=SUM(vpr.SampleValue/100*p.PropertyValue),
Total=SUM(p.PropertyValue)
FROM perfdata AS pd
INNER JOIN perf.vPerfRaw AS vpr ON vpr.PerformanceRuleInstanceRowId = pd.PerformanceRuleInstanceRowId AND vpr.ManagedEntityRowId=pd.ManagedEntityRowId AND vpr.[DateTime]=pd.[DateTime]
INNER JOIN propertyvalue AS p ON vpr.ManagedEntityRowId=p.ManagedEntityRowId AND p.RuleId=pd.RuleSystemName AND pd.GroupName=p.GroupName
GROUP BY
pd.GroupName
)
SELECT
GroupName,
ROUND(([Value]/Total)*100,2),
Total
FROM result
END TRY
BEGIN CATCH
IF OBJECT_ID('tempdb..#thresholds') IS NOT NULL DROP TABLE #thresholds
DECLARE @errMsg VARCHAR(1024)
SET @errMsg = ERROR_MESSAGE()
RAISERROR(@errMsg, 16, 1)
END CATCH
END
GO
GRANT EXECUTE ON sdk.[Veeam_VE_Widget_PerfSummaryByGroupGet] TO OpsMgrReader
GO
-------
Home