IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_DGUserActivity_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_DGUserActivity_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComtradeMPXAXD_DGUserActivity_DataGet]
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@StartDate_BaseValueGMT datetime,
@ObjectList xml,
@ReportType bit,
@UserNamesText nvarchar(max) = ''
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Error int
DECLARE @ExecError int
DECLARE @xmldoc int
DECLARE @RowCount int
CREATE TABLE #ManagedEntity (ManagedEntityRowId int)
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))
--CONVERT csv string to table
CREATE TABLE #UserIds("UserNameId" int)
DECLARE @P1 int
DECLARE @P2 int
-- add manually inserted users (selected user = -1)
IF @UserNamesText IS NOT NULL AND LEN(@UserNamesText) > 0
BEGIN
--parse usernames
SET @UserNamesText = REPLACE(@UserNamesText, ' ', '')
SET @UserNamesText = REPLACE(@UserNamesText, ';', ',')
SET @P1 = CharIndex(',',@UserNamesText,0)
SET @P2 = 1
WHILE @P1 > 0
BEGIN
INSERT INTO #UserIds(UserNameId)
SELECT AN.UserNameId FROM #UserNames AN WHERE AN.UserName = SUBSTRING(@UserNamesText,@P2,@P1-@P2)
SET @P2 = @P1 + 1
SET @P1 = CharIndex(',',@UserNamesText,@P1+1)
END
INSERT INTO #UserIds(UserNameId)
SELECT AN.UserNameId FROM #UserNames AN WHERE AN.UserName = SUBSTRING(@UserNamesText,@P2,DataLength(@UserNamesText))
END
CREATE TABLE #Users("UserNameId" int, "UserName" nvarchar(255) COLLATE database_default)
INSERT INTO #Users
SELECT u.UserNameId, u.UserName
FROM #UserNames AS u INNER JOIN #UserIds AS i ON u.UserNameId = i.UserNameId
-- DG -> SITE relation!
create table #DGs (DG_RowId int, DG_Name nvarchar(max) COLLATE database_default, Site_RowId int, Site_Name nvarchar(max) COLLATE database_default)
insert into #DGs
Select meDG.ManagedEntityRowId DG_RowId, meDG.DisplayName DG_Name,
meSite.ManagedEntityRowId Site_RowId, meSite.DisplayName Site_Name
from vManagedEntity meSite
inner join vManagedEntityManagementGroup mg on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
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 vManagementPack mp on metDG.ManagementPackRowId = mp.ManagementPackRowId and mp.ManagementPackSystemName IN ('Comtrade.Citrix.XenApp.And.XenDesktop')
inner join #ManagedEntity selectedDG on selectedDG.ManagedEntityRowId = meDG.ManagedEntityRowId
IF @ReportType = 1
INSERT INTO #FinalUsages
SELECT
DATEADD(minute, @GMTDiff, vConn.StartDate) as StartDate,
CASE
WHEN vConn.EndDate IS NULL AND vConn.StartDate IS NOT NULL
THEN
DATEADD(minute, @GMTDiff, GETUTCDATE())
ELSE
DATEADD(minute, @GMTDiff, vConn.EndDate)
END as EndDate,
CASE
WHEN vConn.EndDate IS NULL AND vConn.StartDate IS NOT NULL
THEN
DATEDIFF(second, vConn.StartDate, GETUTCDATE())
ELSE
DATEDIFF(second, vConn.StartDate, vConn.EndDate)
END as Session_Duration,
CASE WHEN vConn.EndDate IS NULL
THEN 1 ELSE 0
END as Session_IsActive,
ltUser.UserName,
ltUser.LTUserRowId as UserId,
DGs.DG_Name,
DGs.DG_RowId,
DGs.Site_Name,
DGs.Site_RowId,
client.ClientIp,
vMac.DNSName
FROM ComtradeMPXAXD.vCTXAXDConnection vConn
inner join ComtradeMPXAXD.vCTXAXDSession vSess on vSess.SessionKey = vConn.SessionKey and vSess.IsApplication = 0
inner join ComtradeMPXAXD.vCTXAXDMachine vMac on vMac.CTXAXDMachineRowId = vSess.CTXAXDMachineRowId
inner join ComtradeMPXAXD.LTClientIp client on client.LTClientIpRowId = vConn.ClientIpRowId
inner join ComtradeMPXAXD.LTUser ltUser on ltUser.LTUserRowId = vConn.UserRowId
INNER JOIN #DGs DGs on vMac.ME_DeliveryGroupRowId = DGs.DG_RowID
WHERE ((vConn.StartDate >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vConn.StartDate < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue))
OR (vConn.StartDate <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vConn.EndDate >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue))
or (vConn.EndDate IS NULL AND vSess.EndDate = '1901-01-01 00:00:00.000')
or (vConn.StartDate <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vConn.EndDate >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)))
and vConn.StartDate <= DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
and vConn.Failure = 0
and vConn.StartDate > '1901-02-01 00:00:00.000' --issue with connections being inserted with min start date
ELSE
INSERT INTO #FinalUsages
SELECT
DATEADD(minute, @GMTDiff, vSess.StartDate) as StartDate,
CASE
WHEN vSess.EndDate = '1901-01-01 00:00:00.000' AND vSess.StartDate IS NOT NULL
THEN
DATEADD(minute, @GMTDiff, GETUTCDATE())
ELSE
DATEADD(minute, @GMTDiff, vSess.EndDate)
END as EndDate,
CASE
WHEN vSess.EndDate = '1901-01-01 00:00:00.000' AND vSess.StartDate IS NOT NULL
THEN
DATEDIFF(second, vSess.StartDate, GETUTCDATE())
ELSE
DATEDIFF(second, vSess.StartDate, vSess.EndDate)
END as Session_Duration,
CASE WHEN vSess.EndDate = '1901-01-01 00:00:00.000'
THEN 1 ELSE 0
END as Session_IsActive,
ltUser.UserName,
ltUser.LTUserRowId as UserId,
DGs.DG_Name,
DGs.DG_RowId,
DGs.Site_Name,
DGs.Site_RowId,
client.ClientIp,
vMac.DNSName
FROM ComtradeMPXAXD.vCTXAXDConnection vConn
inner join ComtradeMPXAXD.vCTXAXDSession vSess on vSess.SessionKey = vConn.SessionKey and vSess.IsApplication = 0 --and vSess.EndDate = '1901-01-01 00:00:00.000'
inner join ComtradeMPXAXD.vCTXAXDMachine vMac on vMac.CTXAXDMachineRowId = vSess.CTXAXDMachineRowId
inner join ComtradeMPXAXD.LTClientIp client on client.LTClientIpRowId = vConn.ClientIpRowId
inner join ComtradeMPXAXD.LTUser ltUser on ltUser.LTUserRowId = vConn.UserRowId
INNER JOIN #DGs DGs on vMac.ME_DeliveryGroupRowId = DGs.DG_RowID
WHERE ((vSess.StartDate >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vSess.StartDate < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue))
OR (vSess.StartDate <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vSess.EndDate >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue))
or (vConn.EndDate IS NULL AND vSess.EndDate = '1901-01-01 00:00:00.000'))
and vConn.StartDate <= DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)
and vConn.Failure = 0
Group by vSess.SessionKey, vSess.StartDate, vSess.EndDate, ltUser.UserName, ltUser.LTUserRowId, DGs.DG_Name,DGs.DG_RowId,DGs.Site_Name,DGs.Site_RowId,
client.ClientIp,vMac.DNSName
--using filter for users
if exists (select UserNameId from #Users)
select * from #FinalUsages final
inner join #Users users on users.UserNameId = final.UserId
else if LEN(@UserNamesText) = 0
select * from #FinalUsages
DROP TABLE #FinalUsages
DROP TABLE #Users
DROP TABLE #UserIds
DROP TABLE #UserNames
DROP TABLE #ManagedEntity
DROP TABLE #DGs
Home