IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_UniqueConcurrentSiteUsers_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_UniqueConcurrentSiteUsers_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComtradeMPXAXD_UniqueConcurrentSiteUsers_DataGet]
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@ObjectList xml, --= NULL,
@StartDate_BaseValueGMT datetime
AS
DECLARE @ExecError int
--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 a table that contains all the delivery groups from selected site(s)
CREATE TABLE #ManagedEntityDG (ManagedEntityRowId int, Id nvarchar(255), Name nvarchar(255), SiteId int)
INSERT INTO #ManagedEntityDG
SELECT meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name, meSite.ManagedEntityRowId
FROM vManagedEntity meSite
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')
WHERE meSite.ManagedEntityRowId in (select * from #ManagedEntity)
--
--create an empty table filled with 5 minute intervals from @StartDate_BaseValue to @EndDate_BaseValue
CREATE TABLE #EmptyDateIntervals (StartDate datetime, EndDate datetime)
;WITH dateIntervals AS
(
SELECT
@StartDate_BaseValue AS StartDate,
DATEADD(MINUTE, 5, @StartDate_BaseValue) AS EndDate
UNION ALL
SELECT
EndDate,
DATEADD(MINUTE, 5, EndDate)
FROM dateIntervals
WHERE DATEADD(MINUTE, 5, EndDate) < @EndDate_BaseValue
)
INSERT INTO #EmptyDateIntervals
SELECT * FROM dateIntervals OPTION (MAXRECURSION 0);
--create an empty table filled with daily intervals from @StartDate_BaseValue to @EndDate_BaseValue
CREATE TABLE #EmptyDayIntervals (StartDate datetime)
;WITH dateDays AS
(
SELECT
DATEADD(dd, DATEDIFF(dd, 0, @StartDate_BaseValue), 0) AS StartDate
UNION ALL
SELECT
DATEADD(dd, 1, StartDate)
FROM dateDays
WHERE DATEADD(dd, 1, StartDate) <= @EndDate_BaseValue
)
INSERT INTO #EmptyDayIntervals
SELECT * FROM dateDays OPTION (MAXRECURSION 0);
--create a table with unique user connections for selected site(s)
CREATE TABLE #UserConnectionsForSites (UserRowId int, StartDate datetime, EndDate datetime)
INSERT INTO #UserConnectionsForSites
select c.UserRowId, DATEADD(minute, @GMTDiff, c.StartDate), DATEADD(minute, @GMTDiff, c.EndDate)
from ComtradeMPXAXD.vCTXAXDConnection c
inner join #ManagedEntityDG meDG on meDG.ManagedEntityRowId = c.ME_DeliveryGroupRowId
where c.StartDate < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue) and (c.EndDate > DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) or c.EndDate is null)
and c.Failure = 0
group by c.UserRowId, c.StartDate, c.EndDate
order by c.StartDate
-- user connections for 5 min interval
CREATE TABLE #ConcurrentUsersCountFor5minuteInterval (StartDate datetime, ConcurrentSessions int)
INSERT INTO #ConcurrentUsersCountFor5minuteInterval
SELECT s1.StartDate as StartDate, count(distinct nullif(s2.UserRowId, 0)) as ConcurrentSessions
FROM #EmptyDateIntervals s1
join #UserConnectionsForSites s2 ON (s2.StartDate between s1.StartDate and s1.EndDate) or (s2.StartDate <= s1.StartDate and (s2.EndDate >= s1.EndDate or s2.EndDate is null))
group by s1.StartDate
order by s1.StartDate
-- agg.
-- temporary table where we select max and average number of unique users for specific day
CREATE TABLE #Temp (StartDate datetime, MaxConcurrentUsers int, Average float)
INSERT INTO #Temp
SELECT
CONVERT(date, c.StartDate) as StartDate,
MAX(c.ConcurrentSessions) as MaxConcurrentUsers,
(sum(c.ConcurrentSessions) * 1.0 / count(*)) as Average
FROM #ConcurrentUsersCountFor5minuteInterval c
group by CONVERT(date, c.StartDate)
--
CREATE TABLE #Final(StartDate datetime, MaxUsersCount int, AverageCount float, SiteName nvarchar(255))
INSERT INTO #Final
SELECT ed.StartDate, ISNULL(t.MaxConcurrentUsers, 0) as Max, ISNULL(t.Average,0) as Average, NULL
FROM #EmptyDayIntervals ed
LEFT JOIN #Temp t on t.StartDate = ed.StartDate
INSERT INTO #Final
SELECT NULL, NULL, NULL, me.DisplayName
FROM #ManagedEntity m
inner join ManagedEntity me on me.ManagedEntityRowId = m.ManagedEntityRowId
SELECT * FROM #Final
DROP TABLE #ManagedEntity
DROP TABLE #ManagedEntityDG
DROP TABLE #UserConnectionsForSites
DROP TABLE #EmptyDateIntervals
DROP TABLE #ConcurrentUsersCountFor5minuteInterval
DROP TABLE #EmptyDayIntervals
DROP TABLE #Temp
DROP TABLE #Final
Home