IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ApplicationUsageByUsers_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ApplicationUsageByUsers_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationUsageByUsers_DataGet]
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@StartDate_BaseValueGMT datetime,
@ObjectList xml,
@LanguageCode varchar(3) = 'ENU'
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)
SELECT @StartDate_BaseValue = DATEADD(minute, -@GMTDiff, @StartDate_BaseValue)
SELECT @EndDate_BaseValue = DATEADD(minute, -@GMTDiff, @EndDate_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))
---- applications
CREATE TABLE #ApplicationDistinguishedNamesFinal
("ManagedEntityRowId" int PRIMARY KEY, "ApplicationName" nvarchar(max) COLLATE database_default, "ApplicationKey" nvarchar(max) COLLATE database_default, "SiteId" nvarchar(max) COLLATE database_default,"FromDateTime" datetime)
CREATE TABLE #ApplicationDistinguishedNamesFinalTmp
("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)
INSERT INTO #ApplicationDistinguishedNamesFinalTmp
SELECT me.ManagedEntityRowId, metp.PropertySystemName, meps.PropertyValue,
meps.FromDateTime
FROM vManagedEntityTypeProperty metp with (nolock)
INNER JOIN vManagedEntityType met with (nolock) ON metp.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId
INNER JOIN vManagedEntity me with (nolock) ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
INNER JOIN #ManagedEntity selectedApps on selectedApps.ManagedEntityRowId = me.ManagedEntityRowId
INNER JOIN (select mpnoxml.ManagedEntityRowId, MAX(mpnoxml.ManagedEntityPropertyRowId) as MaxProperyId
from vManagedEntityProperty mpnoxml with (nolock)
group by mpnoxml.ManagedEntityRowId) LatestProperties on LatestProperties.ManagedEntityRowId = me.ManagedEntityRowId
INNER JOIN vManagedEntityPropertySet meps with (nolock) ON metp.PropertyGuid = meps.PropertyGuid AND LatestProperties.MaxProperyId = meps.ManagedEntityPropertyRowId
WHERE met.ManagedEntityTypeSystemName IN ('ComTrade.Citrix.XenDesktop.Application')
and metp.PropertySystemName IN ('ApplicationKey', 'Name','SiteId')
INSERT INTO #ApplicationDistinguishedNamesFinal
SELECT distinct(ServerNames.ManagedEntityRowId), ServerNames.PropertyValue, FarmNames.PropertyValue, TestNames.PropertyValue,ServerNames.FromDateTime
FROM #ApplicationDistinguishedNamesFinalTmp AS ServerNames
INNER JOIN #ApplicationDistinguishedNamesFinalTmp AS FarmNames ON ServerNames.ManagedEntityRowId = FarmNames.ManagedEntityRowId
AND FarmNames.PropertyDefaultName = 'ApplicationKey' AND ServerNames.PropertyDefaultName = 'Name'
INNER JOIN #ApplicationDistinguishedNamesFinalTmp AS TestNames on TestNames.ManagedEntityRowId = ServerNames.ManagedEntityRowId
AND TestNames.PropertyDefaultName = 'SiteId'
DROP TABLE #ApplicationDistinguishedNamesFinalTmp
--select * from #ApplicationDistinguishedNamesFinal
--site data collectors
CREATE TABLE #SDCNamesFinal
("ManagedEntityRowId" int PRIMARY KEY, "SiteName" nvarchar(max) COLLATE database_default, "SiteId" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)
CREATE TABLE #SDCNamesFinalTmp
("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)
INSERT INTO #SDCNamesFinalTmp
SELECT me.ManagedEntityRowId, metp.PropertySystemName, meps.PropertyValue,
meps.FromDateTime
FROM vManagedEntityTypeProperty metp with (nolock)
INNER JOIN vManagedEntityType met with (nolock) ON metp.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId
INNER JOIN vManagedEntity me with (nolock) ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId
INNER JOIN (select mpnoxml.ManagedEntityRowId, MAX(mpnoxml.ManagedEntityPropertyRowId) as MaxProperyId
from vManagedEntityProperty mpnoxml with (nolock)
group by mpnoxml.ManagedEntityRowId) LatestProperties on LatestProperties.ManagedEntityRowId = me.ManagedEntityRowId
INNER JOIN vManagedEntityPropertySet meps with (nolock) ON metp.PropertyGuid = meps.PropertyGuid AND LatestProperties.MaxProperyId = meps.ManagedEntityPropertyRowId
WHERE met.ManagedEntityTypeSystemName IN ('ComTrade.Citrix.XenDesktop.SiteDataCollector')
and metp.PropertySystemName IN ('Id', 'SiteName')
INSERT INTO #SDCNamesFinal
SELECT distinct(SiteNames.ManagedEntityRowId), SiteNames.PropertyValue, IdNames.PropertyValue, SiteNames.FromDateTime
FROM #SDCNamesFinalTmp AS SiteNames
INNER JOIN #SDCNamesFinalTmp AS IdNames ON SiteNames.ManagedEntityRowId = IdNames.ManagedEntityRowId
AND IdNames.PropertyDefaultName = 'Id' AND SiteNames.PropertyDefaultName = 'SiteName'
Select me1.DisplayName, me1.ManagedEntityRowId, meDG.DisplayName from vManagedEntity me1 WITH (NOLOCK)
inner join vManagedEntityType met2 WITH (NOLOCK) on met2.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Application'
inner join vRelationship re3 WITH (NOLOCK) on re3.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret3 WITH (NOLOCK) on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId AND (ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Application.Contains.DeliveryGroupSharedServerOS' OR ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Application.Contains.DeliveryGroupSharedDesktopOS')
inner join vManagedEntity meDG WITH (NOLOCK) on meDG.ManagedEntityRowId = re3.TargetManagedEntityRowId
CREATE TABLE #ApplicationDG ("ApplicationID" int, "DeliveryGroup" nvarchar(256))
INSERT INTO #ApplicationDG
Select t.ApplicationID, STUFF(
(SELECT ', ' + s.DeliveryGroup
FROM #ApplicationDG1 s
WHERE s.ApplicationID = t.ApplicationID
FOR XML PATH('')),1,1,'') AS DeliveryGroup
FROM #ApplicationDG1 AS t
GROUP by t.ApplicationID
DROP TABLE #ApplicationDG1
--select * from #ApplicationDG
create table #lastUsagesOnApp (CTXAXDApplicationRowId int, LTUserRowId int, LastUsage datetime)
insert into #lastUsagesOnApp
SELECT vAppInst.CTXAXDApplicationRowId,
ltUser.LTUserRowId as UserId,
Max(vAppInst.StartTime) as LastUsage
FROM (
select distinct
ManagementGroupRowId
, SessionKey
, MachineDNSNameRowId
, ConnectionId
, StartDate
, EndDate
, UserRowId
, ClientIpRowId
, ClientHostnameRowId
, ConnectedViaIpRowId
, ConnectedViaHostnameRowId
, LaunchedViaIpRowId
, LaunchedViaHostnameRowId
, ClientVersionRowId
, ControllerRowId
, IsReconnect
, Failure
, FailureTime
, FailureTypeCode
, FailureReasonCode
, ME_DeliveryGroupRowId
, DWCreatedDateTime
from
ComtradeMPXAXD.vCTXAXDConnection WITH (NOLOCK)
) vConn
inner join (select distinct
ConnectionId
, SessionKey
, CTXAXDApplicationRowId
, PID
, StartTime
, EndTime
, DWCreatedDateTime
from
ComtradeMPXAXD.vCTXAXDApplicationInstance WITH (NOLOCK)
) vAppInst on vAppInst.ConnectionId = vConn.ConnectionId and vAppInst.SessionKey= vConn.SessionKey
inner join ComtradeMPXAXD.vCTXAXDApplication vApp WITH (NOLOCK) on vApp.CTXAXDApplicationRowId = vAppInst.CTXAXDApplicationRowId
inner join ComtradeMPXAXD.LTUser ltUser WITH (NOLOCK) on ltUser.LTUserRowId = vConn.UserRowId
inner join #ApplicationDistinguishedNamesFinal appnames on appnames.ApplicationKey = vApp.ApplicationKey
WHERE vConn.Failure = 0
GROUP BY vAppInst.CTXAXDApplicationRowId, ltUser.LTUserRowId
SELECT vApp.Name as ApplicationName,
vApp.Folder as ApplicationFolder,
vApp.ApplicationKey,
appnames.ManagedEntityRowId as ApplicationRowId,
appdg.DeliveryGroup,
allsites.SiteName,
ltUser.UserName,
ltUser.LTUserRowId as UserId,
SUM (case when (vAppInst.StartTime >= @StartDate_BaseValue and vAppInst.EndTime is not null and vAppInst.EndTime <= @EndDate_BaseValue) then DATEDIFF(second, vAppInst.StartTime, vAppInst.EndTime ) --inside range
when (vAppInst.StartTime < @StartDate_BaseValue and vAppInst.EndTime is not null and vAppInst.EndTime between @StartDate_BaseValue and @EndDate_BaseValue) then DATEDIFF(second, @StartDate_BaseValue, vAppInst.EndTime ) --starts before the report time range, ends within
when (vAppInst.StartTime between @StartDate_BaseValue and @EndDate_BaseValue and (vAppInst.EndTime is not null or vAppInst.EndTime > @EndDate_BaseValue)) then DATEDIFF(second, vAppInst.StartTime, @EndDate_BaseValue ) --starts within report time range, ends after
when (vAppInst.StartTime < @StartDate_BaseValue and (vAppInst.EndTime is not null or vAppInst.EndTime > @EndDate_BaseValue)) then DATEDIFF(second, @StartDate_BaseValue, @EndDate_BaseValue ) --starts before and ends after report range
--corner case: application is still running:
when (vAppInst.StartTime between @StartDate_BaseValue and @EndDate_BaseValue and (vAppInst.EndTime is null) and (@EndDate_BaseValue < GETUTCDATE())) then DATEDIFF(second, vAppInst.StartTime, @EndDate_BaseValue ) --starts within report time range, still running, report range < current time
when (vAppInst.StartTime between @StartDate_BaseValue and @EndDate_BaseValue and (vAppInst.EndTime is null) and (@EndDate_BaseValue >= GETUTCDATE())) then DATEDIFF(second, vAppInst.StartTime, GETUTCDATE() ) --starts within report time range, still running, report range >= current time
when ((vAppInst.StartTime < @StartDate_BaseValue) and (vAppInst.EndTime is null) and (@EndDate_BaseValue < GETUTCDATE())) then DATEDIFF(second, @StartDate_BaseValue, @EndDate_BaseValue ) --starts before and still running, report range < current time
when ((vAppInst.StartTime < @StartDate_BaseValue) and (vAppInst.EndTime is null) and (@EndDate_BaseValue >= GETUTCDATE())) then DATEDIFF(second, @StartDate_BaseValue, GETUTCDATE() ) --starts before and still running, report range >= current time
else 0
end ) as Total_Duration_Sec,
sum(case when vAppInst.StartTime < @StartDate_BaseValue then 0 else 1 end) as Launches,
count(*) as Instances,
SUM (case when vAppInst.EndTime is not null then
DATEDIFF(second, vAppInst.StartTime, vAppInst.EndTime ) else
DATEDIFF(second, vAppInst.StartTime, GETUTCDATE() ) end )
/ count(*) as Average_Duration_Sec ,
DATEADD(minute, @GMTDiff, lastUsages.LastUsage) as LastUsage
FROM (
select distinct
ManagementGroupRowId
, SessionKey
, MachineDNSNameRowId
, ConnectionId
, StartDate
, EndDate
, UserRowId
, ClientIpRowId
, ClientHostnameRowId
, ConnectedViaIpRowId
, ConnectedViaHostnameRowId
, LaunchedViaIpRowId
, LaunchedViaHostnameRowId
, ClientVersionRowId
, ControllerRowId
, IsReconnect
, Failure
, FailureTime
, FailureTypeCode
, FailureReasonCode
, ME_DeliveryGroupRowId
, DWCreatedDateTime
from
ComtradeMPXAXD.vCTXAXDConnection WITH (NOLOCK)
) vConn
inner join (
select distinct
CTXAXDMachineRowId
, SessionKey
, StartDate
, EndDate
, UserRowId
, Protocol
, IsApplication
, SystemSessionId
, DWCreatedDateTime
from
ComtradeMPXAXD.vCTXAXDSession WITH (NOLOCK)
) vSess on vSess.SessionKey = vConn.SessionKey and vSess.IsApplication = 1
inner join (select distinct
ConnectionId
, SessionKey
, CTXAXDApplicationRowId
, PID
, StartTime
, EndTime
, DWCreatedDateTime
from
ComtradeMPXAXD.vCTXAXDApplicationInstance WITH (NOLOCK)
) vAppInst on vAppInst.ConnectionId = vConn.ConnectionId and vAppInst.SessionKey= vConn.SessionKey
inner join ComtradeMPXAXD.vCTXAXDApplication vApp WITH (NOLOCK) on vApp.CTXAXDApplicationRowId = vAppInst.CTXAXDApplicationRowId
inner join ComtradeMPXAXD.LTUser ltUser WITH (NOLOCK) on ltUser.LTUserRowId = vConn.UserRowId
inner join #ApplicationDistinguishedNamesFinal appnames on appnames.ApplicationKey = vApp.ApplicationKey
inner join #ApplicationDG appdg on appdg.ApplicationID = appnames.ManagedEntityRowId
inner join #SDCNamesFinal allsites on allsites.SiteId = appnames.SiteId
inner join #lastUsagesOnApp as lastUsages on lastUsages.CTXAXDApplicationRowId = vApp.CTXAXDApplicationRowId and lastUsages.LTUserRowId = ltUser.LTUserRowId
WHERE (vAppInst.StartTime >= @StartDate_BaseValue and vAppInst.StartTime < @EndDate_BaseValue) --start time is inside the range
OR (vAppInst.EndTime >= @StartDate_BaseValue and vAppInst.EndTime < @EndDate_BaseValue) --end time is inside the range
OR (vAppInst.StartTime < @StartDate_BaseValue and (vAppInst.EndTime >= @EndDate_BaseValue OR vAppInst.EndTime IS NULL)) --usage starts before and ends after range
and vConn.Failure = 0
GROUP BY appnames.ManagedEntityRowId, vApp.Name, vApp.ApplicationKey, appdg.DeliveryGroup, ltUser.UserName, ltUser.LTUserRowId,allsites.SiteName, vApp.Folder, lastUsages.LastUsage
ORDER BY Total_Duration_Sec desc
drop table #lastUsagesOnApp
DROP TABLE #ManagedEntity
DROP TABLE #ApplicationDistinguishedNamesFinal
DROP TABLE #SDCNamesFinal
DROP TABLE #ApplicationDG
Home