IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ServerPerformanceByDeliveryGroup_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ServerPerformanceByDeliveryGroup_DataGet] AS RETURN 1')
END
GO
--calculate time
SET DATEFIRST 1 --monday = 1
DECLARE @GMTDiff int
SELECT @GMTDiff = DATEDIFF(minute, @StartDate_BaseValueGMT, @StartDate_BaseValue)
DECLARE @BH_start datetime --business hour start time
DECLARE @BH_end datetime --business hour end time
SELECT @BH_start = (@StartDate_BaseValue - CAST(FLOOR(CAST(@StartDate_BaseValue AS float)) AS datetime))
SELECT @BH_end = (@EndDate_BaseValue - CAST(FLOOR(CAST(@EndDate_BaseValue AS float)) AS datetime))
--create table of SCOM Server OS Machines! for Server performance report link.
create table #ME_ServerOS_tmp (ME_VM_RowId int, ME_VM_ID nvarchar(max) COLLATE database_default, ME_VM_Name nvarchar(max) COLLATE database_default, ME_VM_Type nvarchar(max) COLLATE database_default, ME_VM_ManagementGroupRowId int)
insert into #ME_ServerOS_tmp
select me.ManagedEntityRowId, me.Name, me.DisplayName, met.ManagedEntityTypeSystemName, me.ManagementGroupRowId
from vManagedEntity me
inner join vManagedEntityType met on me.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId
where met.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine'
create table #ME_ServerOS (ME_VM_RowId int, ME_VM_ID nvarchar(max) COLLATE database_default, ME_VM_Name nvarchar(max) COLLATE database_default, ME_VM_Type nvarchar(max) COLLATE database_default, ME_VM_ManagementGroupRowId int)
insert into #ME_ServerOS
SELECT ME_VM_RowId, ME_VM_ID, ME_VM_Name, ME_VM_Type, ME_VM_ManagementGroupRowId
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY ME_VM_Name, ME_VM_ManagementGroupRowId ORDER BY ME_VM_RowId DESC) AS RowNumber
FROM #ME_ServerOS_tmp
) AS a
WHERE a.RowNumber = 1
drop table #ME_ServerOS_tmp
IF @BusinessTimeType = 1 --business hours
BEGIN
--HOURLY DATA
INSERT INTO #PerfTable
SELECT
DATEADD(minute, @GMTDiff, vPerf.DateTime)
,vManagedEntityDG.ManagedEntityRowId AS DeliveryGroupId
,vManagedEntityDG.DisplayName AS DeliveryGroupName
,Machines.CTXAXDMachineRowId AS MachineId
,Machines.DNSName AS MachineName
,ME_Srv.ME_VM_RowId AS ME_MachineRowId
,vPerf.CpuUtil AS CpuUtil
,vPerf.PhysicalMemUtil AS PhysicalMemUtil
,vPerf.Load AS Load
,vPerf.SessionCount AS SessionCountAvg
,vPerf.DiskLatency AS DiskLatency
,vPerf.NetworkRead AS NetworkRead
,vPerf.NetworkWrite AS NetworkWrite
FROM vManagedEntityType
INNER JOIN vManagedEntity vManagedEntityDG ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityDG.ManagedEntityTypeRowId
AND ManagedEntityTypeSystemName IN
('ComTrade.Citrix.XenDesktop.DeliveryGroup.Private',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Private.DesktopOS',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.DesktopOS',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.ServerOS')
INNER JOIN [ComtradeMPXAXD].[vCTXAXDMachine] AS Machines ON Machines.ME_DeliveryGroupRowId = vManagedEntityDG.ManagedEntityRowId
inner join #ManagedEntityDeliveryGroups selectedDGs on selectedDGs.ManagedEntityRowId = vManagedEntityDG.ManagedEntityRowId
INNER JOIN [ComtradeMPXAXD].[vCTXAXDMachinePerformanceHourly] AS vPerf ON Machines.CTXAXDMachineRowId = vPerf.CTXAXDMachineRowId
LEFT JOIN #ME_ServerOS ME_Srv on Machines.DNSName = ME_Srv.ME_VM_Name and Machines.ManagementGroupRowId = ME_Srv.ME_VM_ManagementGroupRowId
WHERE
vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)
and vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
and vPerf.[DateTime] - CAST(FLOOR(CAST(vPerf.[DateTime] AS float)) AS datetime) >= DATEADD(minute, -@GMTDiff, @BH_start) --start hours
and vPerf.[DateTime] - CAST(FLOOR(CAST(vPerf.[DateTime] AS float)) AS datetime) < DATEADD(minute, -@GMTDiff, @BH_end) --end hours
and ( (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 1 and @BusinessDay1 = 1) --monday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 2 and @BusinessDay2 = 1) --tuesday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 3 and @BusinessDay3 = 1) --wednesday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 4 and @BusinessDay4 = 1) --thursday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 5 and @BusinessDay5 = 1) --friday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 6 and @BusinessDay6 = 1) --saturday
OR (DATEPART(WEEKDAY, DATEADD(minute, @GMTDiff, vPerf.[DateTime])) = 7 and @BusinessDay7 = 1)) --sunday
ORDER BY vPerf.DateTime
END
ELSE
BEGIN
--HOURLY DATA
INSERT INTO #PerfTable
SELECT
DATEADD(minute, @GMTDiff, vPerf.DateTime)
,vManagedEntityDG.ManagedEntityRowId AS DeliveryGroupId
,vManagedEntityDG.DisplayName AS DeliveryGroupName
,Machines.CTXAXDMachineRowId AS MachineId
,Machines.DNSName AS MachineName
,ME_Srv.ME_VM_RowId AS ME_MachineRowId
,vPerf.CpuUtil AS CpuUtil
,vPerf.PhysicalMemUtil AS PhysicalMemUtil
,vPerf.Load AS Load
,vPerf.SessionCount AS SessionCountAvg
,vPerf.DiskLatency AS DiskLatency
,vPerf.NetworkRead AS NetworkRead
,vPerf.NetworkWrite AS NetworkWrite
FROM vManagedEntityType
INNER JOIN vManagedEntity vManagedEntityDG ON vManagedEntityType.ManagedEntityTypeRowId = vManagedEntityDG.ManagedEntityTypeRowId
AND ManagedEntityTypeSystemName IN
('ComTrade.Citrix.XenDesktop.DeliveryGroup.Private',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Private.DesktopOS',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.DesktopOS',
'ComTrade.Citrix.XenDesktop.DeliveryGroup.Shared.ServerOS')
INNER JOIN [ComtradeMPXAXD].[vCTXAXDMachine] AS Machines ON Machines.ME_DeliveryGroupRowId = vManagedEntityDG.ManagedEntityRowId
inner join #ManagedEntityDeliveryGroups selectedDGs on selectedDGs.ManagedEntityRowId = vManagedEntityDG.ManagedEntityRowId
INNER JOIN [ComtradeMPXAXD].[vCTXAXDMachinePerformanceHourly] AS vPerf ON Machines.CTXAXDMachineRowId = vPerf.CTXAXDMachineRowId
LEFT JOIN #ME_ServerOS ME_Srv on Machines.DNSName = ME_Srv.ME_VM_Name and Machines.ManagementGroupRowId = ME_Srv.ME_VM_ManagementGroupRowId
WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)
AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
ORDER BY vPerf.DateTime
END
----------------------------------
-- add delivery group averages to final table (in addition add max sessions from standard SCOM perf rule)
INSERT INTO #PerformanceTableRaw
SELECT PerfFromMachineDataset.DateTime, PerfFromMachineDataset.DeliveryGroupId, PerfFromMachineDataset.DeliveryGroupName,
PerfFromMachineDataset.MachineId, PerfFromMachineDataset.MachineName, PerfFromMachineDataset.ME_MachineRowId,
PerfFromMachineDataset.CpuUtil, PerfFromMachineDataset.PhysicalMemUtil, PerfFromMachineDataset.Load,
PerfFromMachineDataset.SessionCountAvg, PerfFromSCOM.MaxValue as SessionCountMax, PerfFromMachineDataset.DiskLatency,
PerfFromMachineDataset.NetworkRead, PerfFromMachineDataset.NetworkWrite, PerfFromMachineDataset.MachineCount
from
(SELECT
A.DateTime AS DateTime
,A.DeliveryGroupId AS DeliveryGroupId
,A.DeliveryGroupName AS DeliveryGroupName
,NULL AS MachineId
,NULL AS MachineName
,NULL AS ME_MachineRowId
,AVG(A.CpuUtil) AS CpuUtil
,AVG(A.PhysicalMemUtil) AS PhysicalMemUtil
,AVG(A.Load) AS Load
,AVG(CAST(A.SessionCountAvg AS FLOAT)) AS SessionCountAvg
,AVG(A.DiskLatency) AS DiskLatency
,AVG(A.NetworkRead) AS NetworkRead
,AVG(A.NetworkWrite) AS NetworkWrite
,COUNT(*) AS MachineCount
FROM
#PerfTable A
GROUP BY A.DateTime
,A.DeliveryGroupId
,A.DeliveryGroupName) as PerfFromMachineDataset
LEFT JOIN (select DATEADD(minute, @GMTDiff, vPerfSCOM.DateTime) as DateTime, vPerfSCOM.ManagedEntityRowId, max(vPerfSCOM.MaxValue) as MaxValue --get maxvalues from SCOM vPerfHourly
from [Perf].[vPerfHourly] as vPerfSCOM
inner join vPerformanceRuleInstance vPRI on vPRI.PerformanceRuleInstanceRowId = vPerfSCOM.PerformanceRuleInstanceRowId
inner join vRule vR on vR.RuleRowId = vPRI.RuleRowId and vR.RuleSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroup.AllSessions.PerfDBDW'
where vPerfSCOM.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)
AND vPerfSCOM.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
group by vPerfSCOM.DateTime , vPerfSCOM.ManagedEntityRowId) as PerfFromSCOM
on PerfFromSCOM.ManagedEntityRowId = PerfFromMachineDataset.DeliveryGroupId and PerfFromSCOM.DateTime = PerfFromMachineDataset.DateTime
order by PerfFromMachineDataset.DateTime asc
IF @DataAggregation = 0 --HOURLY
BEGIN
insert into #PerformanceTableAggregated
select * from #PerformanceTableRaw order by DateTime asc
END
IF @DataAggregation = 1 --DAILY
BEGIN
insert into #PerformanceTableAggregated
SELECT
convert(datetime,CONVERT(varchar,DATEPART(month, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(day, A.[DateTime]))+'/'+CONVERT(varchar,DATEPART(yyyy, A.[DateTime]))) AS DateTime
,A.DeliveryGroupId
,A.DeliveryGroupName
,A.MachineId
,A.MachineName
,A.ME_MachineRowId
,AVG(A.CpuUtil) AS CpuUtil
,AVG(A.PhysicalMemUtil) AS PhysicalMemUtil
,AVG(A.Load) AS Load
,AVG(A.SessionCountAvg) AS SessionCountAvg
,MAX(A.SessionCountMax) AS SessionCountMax
,AVG(A.DiskLatency) AS DiskLatency
,AVG(A.NetworkRead) AS NetworkRead
,AVG(A.NetworkWrite) AS NetworkWrite
,MAX(MachineCount) AS MachineCount
FROM #PerformanceTableRaw A
GROUP BY DATEPART(yyyy, A.[DateTime]), DATEPART(month, A.[DateTime]), DATEPART(day, A.[DateTime])
,A.DeliveryGroupId
,A.DeliveryGroupName
,A.MachineId
,A.MachineName
,A.ME_MachineRowId
END
----------------------------------
-- add machine averages to final table (independent of data aggregation)
INSERT INTO #PerformanceTableAggregated
SELECT
NULL AS DateTime
,A.DeliveryGroupId AS DeliveryGroupId
,A.DeliveryGroupName AS DeliveryGroupName
,A.MachineId AS MachineId
,A.MachineName AS MachineName
,A.ME_MachineRowId AS ME_MachineRowId
,AVG(A.CpuUtil) AS CpuUtil
,AVG(A.PhysicalMemUtil) AS PhysicalMemUtil
,AVG(A.Load) AS Load
,AVG(CAST(A.SessionCountAvg AS FLOAT)) AS SessionCountAvg
,MAX(CAST(A.SessionCountAvg AS FLOAT)) AS SessionCountMax --we do not have this value for machines yet! but we can use max of hourly averages for now (TODO: extend machineperformance hourly and daily tables with MAX and MIN values)
,AVG(A.DiskLatency) AS DiskLatency
,AVG(A.NetworkRead) AS NetworkRead
,AVG(A.NetworkWrite) AS NetworkWrite
,NULL AS MachineCount
FROM #PerfTable A
GROUP BY A.DeliveryGroupId
,A.DeliveryGroupName
,A.MachineId
,A.MachineName
,A.ME_MachineRowId
SELECT pf.[DateTime] AS [DateTime],
pf.DeliveryGroupId AS DeliveryGroupId,
pf.DeliveryGroupName AS DeliveryGroupName,
pf.MachineId AS MachineId,
pf.MachineName AS MachineName,
pf.ME_MachineRowId AS ME_MachineRowId,
pf.CpuUtil AS CpuUtil,
pf.PhysicalMemUtil AS PhysicalMemUtil,
pf.[Load] AS [Load],
pf.SessionCountAvg AS SessionCountAvg,
pf.SessionCountMax AS SessionCountMax,
pf.DiskLatency AS AvgDiskSecPerTransfer,
pf.NetworkRead AS NetworkRead,
pf.NetworkWrite AS NetworkWrite,
pf.MachineCount AS MachineCount
FROM #PerformanceTableAggregated AS pf
ORDER BY pf.DateTime
DROP TABLE #ManagedEntityDeliveryGroups
DROP TABLE #PerfTable
DROP TABLE #PerformanceTableRaw
DROP TABLE #PerformanceTableAggregated
Home