IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ServerOSTop_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ServerOSTop_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))
--get all ServerOS, Delivery groups and Site names for selected DeliveryGroups!
create table #SelectedServerOSs ( ServerOS_RowId int, ServerOS_Id nvarchar(max) COLLATE database_default, ServerOS_Name nvarchar(max) COLLATE database_default,
DG_RowId int, DG_Id nvarchar(max) COLLATE database_default, DG_Name nvarchar(max) COLLATE database_default,
Site_RowId int, Site_Id nvarchar(max) COLLATE database_default, Site_Name nvarchar(max) COLLATE database_default)
insert into #SelectedServerOSs
Select meServerOS.ManagedEntityRowId ServerOS_RowId, meServerOS.Name ServerOS_Id, meServerOS.DisplayName ServerOS_Name,
meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name,
meSite.ManagedEntityRowId Site_RowId, meSite.Name Site_Id, meSite.DisplayName Site_Name
from vManagedEntity meSite
inner join vManagedEntityManagementGroup memgSite on memgSite.ManagedEntityRowId = meSite.ManagedEntityRowId AND memgSite.ToDateTime IS NULL --use only currently existing site
inner join vManagedEntityType metSite on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId AND metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site'
inner join vRelationship re1 on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId
inner join vRelationshipType ret1 on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId AND ret1.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site.Contains.SiteProxy'
inner join vManagedEntity me1 on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId
inner join vManagedEntityType met1 on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met1.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector'
inner join vRelationship re2 on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret2 on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId AND ret2.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector.Hosts.DeliveryGroupContainer'
inner join vManagedEntity me2 on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId
inner join vManagedEntityType met2 on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroups.Container'
inner join vRelationship re3 on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId
inner join vRelationshipType ret3 on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId AND ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroupContainer.Hosts.DeliveryGroup'
inner join vManagedEntity meDG on meDG.ManagedEntityRowId = re3.TargetManagedEntityRowId
inner join vManagedEntityType metDG on metDG.ManagedEntityTypeRowId = meDG.ManagedEntityTypeRowId AND metDG.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 #ManagedEntityDGs SelectedDGs on SelectedDGs.ManagedEntityRowId = meDG.ManagedEntityRowId
inner join vRelationship re4 on re4.SourceManagedEntityRowId = meDG.ManagedEntityRowId
inner join vRelationshipType ret4 on ret4.RelationshipTypeRowId = re4.RelationshipTypeRowId AND ret4.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroupSharedServerOS.Hosts.ApplicationServer'
inner join vManagedEntity meServerOS on meServerOS.ManagedEntityRowId = re4.TargetManagedEntityRowId
inner join vManagedEntityType metServerOS on metServerOS.ManagedEntityTypeRowId = meServerOS.ManagedEntityTypeRowId AND metServerOS.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine'
--select * from #SelectedServerOSs
IF @BusinessTimeType = 1 --business hours
BEGIN
SELECT
ROW_NUMBER() OVER (ORDER BY ft.MaxValue * @SortOrder, ft.AvgValue DESC, ft.ServerOS_Name ASC) AS Row,
ServerOS_RowId, ServerOS_Name, DG_RowId, DG_Name, Site_RowId, Site_Name, Samples, AvgValue, MinValue, MaxValue
FROM
(SELECT TOP (@TopCount) WITH TIES
MESERV.ServerOS_RowId, MESERV.ServerOS_Name,
MESERV.DG_RowId, MESERV.DG_Name,
MESERV.Site_RowId, MESERV.Site_Name,
SUM(vPerf.SampleCount) as Samples,
SUM(vPerf.AverageValue * vPerf.SampleCount)/SUM(vPerf.SampleCount) as AvgValue,
MIN(vPerf.MinValue) as MinValue,
MAX(vPerf.MaxValue) as MaxValue
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 vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN #SelectedServerOSs AS MESERV ON vManagedEntity.ManagedEntityRowId = MESERV.ServerOS_RowId
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
AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine.ActiveSessions.PerfDW'
GROUP BY MESERV.ServerOS_RowId, MESERV.DG_RowId, MESERV.Site_RowId,
MESERV.ServerOS_Name, MESERV.DG_Name, MESERV.Site_Name
ORDER BY MAX(vPerf.MaxValue) * @SortOrder, AvgValue DESC ) as ft
END
ELSE
BEGIN
SELECT
ROW_NUMBER() OVER (ORDER BY ft.MaxValue * @SortOrder, ft.AvgValue DESC, ft.ServerOS_Name ASC) AS Row,
ServerOS_RowId, ServerOS_Name, DG_RowId, DG_Name, Site_RowId, Site_Name, Samples, AvgValue, MinValue, MaxValue
FROM
(SELECT TOP (@TopCount) WITH TIES
MESERV.ServerOS_RowId, MESERV.ServerOS_Name,
MESERV.DG_RowId, MESERV.DG_Name,
MESERV.Site_RowId, MESERV.Site_Name,
SUM(vPerf.SampleCount) as Samples,
SUM(vPerf.AverageValue * vPerf.SampleCount)/SUM(vPerf.SampleCount) as AvgValue,
MIN(vPerf.MinValue) as MinValue,
MAX(vPerf.MaxValue) as MaxValue
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 vManagedEntity.ManagedEntityTypeRowId = vManagedEntityType.ManagedEntityTypeRowId
INNER JOIN vManagementGroup ON vManagedEntity.ManagementGroupRowId = vManagementGroup.ManagementGroupRowId
INNER JOIN vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN vRule ON vPerformanceRuleInstance.RuleRowId = vRule.RuleRowId
INNER JOIN #SelectedServerOSs AS MESERV ON vManagedEntity.ManagedEntityRowId = MESERV.ServerOS_RowId
WHERE vPerf.DateTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)
AND vPerf.DateTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.ServerOSMachine.ActiveSessions.PerfDW'
GROUP BY MESERV.ServerOS_RowId, MESERV.DG_RowId, MESERV.Site_RowId,
MESERV.ServerOS_Name, MESERV.DG_Name, MESERV.Site_Name
ORDER BY MAX(vPerf.MaxValue) * @SortOrder, AvgValue DESC ) as ft
END
DROP TABLE #SelectedServerOSs
--DROP TABLE #PerfTable
Home