IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ApplicationUsage_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ApplicationUsage_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationUsage_DataGet]
@StartDate_BaseValue datetime, -- report range
@EndDate_BaseValue datetime, -- report range
@StartDate_BaseValueGMT datetime, -- report range
@ObjectList xml, -- site
@DeliveryGroup nvarchar(256), --dg
@Scope int, -- 1-all, 2-bottom n, 3-top n, 4-unused
@Count int, -- record count when @Scope is 2, 3 or 4
--@Nfilter int, -- 1-usage duration, 2-Launches, 3-Distinct users
@AppCreatedDays int, -- app created before X days or earlyer
@UserNamesText nvarchar(max) = '', -- users
@LanguageCode varchar(3) = 'ENU'
AS
begin
set nocount on;
declare @Error int
declare @ExecError int
declare @xmldoc int
declare @RowCount int
declare @ConcurrentTimeInterval int
set @ConcurrentTimeInterval = 15
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))
declare @StartDateTime datetime
declare @EndDateTime datetime
set @StartDateTime = dateadd(minute, -@GMTDiff, @StartDate_BaseValue)
set @EndDateTime = dateadd(minute, -@GMTDiff, @EndDate_BaseValue)
------------------------------------
-- Site and DG data
------------------------------------
create table #SiteAndDGs (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)
if @DeliveryGroup is not null and LEN(@DeliveryGroup) > 0
begin
insert into #SiteAndDGs
select meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name, meSite.ManagedEntityRowId Site_RowId, meSite.ManagedEntityRowId Site_Id, meSite.DisplayName Site_Name
from vManagedEntity meSite WITH (NOLOCK)
inner join vManagedEntityManagementGroup mg WITH (NOLOCK) on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
inner join vManagedEntityType metSite WITH (NOLOCK) on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId and metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site'
inner join vRelationship re1 WITH (NOLOCK) on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId
inner join vRelationshipType ret1 WITH (NOLOCK) on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId and ret1.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site.Contains.SiteProxy'
inner join vManagedEntity me1 WITH (NOLOCK) on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId
inner join vManagedEntityType met1 WITH (NOLOCK) on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId and met1.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector'
inner join vRelationship re2 WITH (NOLOCK) on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret2 WITH (NOLOCK) on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId and ret2.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector.Hosts.DeliveryGroupContainer'
inner join vManagedEntity me2 WITH (NOLOCK) on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId
inner join vManagedEntityType met2 WITH (NOLOCK) on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId and met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroups.Container'
inner join vRelationship re3 WITH (NOLOCK) on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId
inner join vRelationshipType ret3 WITH (NOLOCK) on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId and ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroupContainer.Hosts.DeliveryGroup'
inner join vManagedEntity meDG WITH (NOLOCK) on meDG.ManagedEntityRowId = re3.TargetManagedEntityRowId
inner join vManagedEntityType metDG WITH (NOLOCK) 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 #ManagedEntity me on me.ManagedEntityRowId = meSite.ManagedEntityRowId
where meDG.DisplayName = @DeliveryGroup
end
else
begin
insert into #SiteAndDGs
select meDG.ManagedEntityRowId DG_RowId, meDG.Name DG_Id, meDG.DisplayName DG_Name, meSite.ManagedEntityRowId Site_RowId, meSite.ManagedEntityRowId Site_Id, meSite.DisplayName Site_Name
from vManagedEntity meSite WITH (NOLOCK)
inner join vManagedEntityManagementGroup mg WITH (NOLOCK) on mg.ManagedEntityRowId = meSite.ManagedEntityRowId and mg.ToDateTime is null
inner join vManagedEntityType metSite WITH (NOLOCK) on metSite.ManagedEntityTypeRowId = meSite.ManagedEntityTypeRowId and metSite.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site'
inner join vRelationship re1 WITH (NOLOCK) on re1.SourceManagedEntityRowId = meSite.ManagedEntityRowId
inner join vRelationshipType ret1 WITH (NOLOCK) on ret1.RelationshipTypeRowId = re1.RelationshipTypeRowId and ret1.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.Site.Contains.SiteProxy'
inner join vManagedEntity me1 WITH (NOLOCK) on me1.ManagedEntityRowId = re1.TargetManagedEntityRowId
inner join vManagedEntityType met1 WITH (NOLOCK) on met1.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId and met1.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector'
inner join vRelationship re2 WITH (NOLOCK) on re2.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret2 WITH (NOLOCK) on ret2.RelationshipTypeRowId = re2.RelationshipTypeRowId and ret2.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.SiteDataCollector.Hosts.DeliveryGroupContainer'
inner join vManagedEntity me2 WITH (NOLOCK) on me2.ManagedEntityRowId = re2.TargetManagedEntityRowId
inner join vManagedEntityType met2 WITH (NOLOCK) on met2.ManagedEntityTypeRowId = me2.ManagedEntityTypeRowId and met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroups.Container'
inner join vRelationship re3 WITH (NOLOCK) on re3.SourceManagedEntityRowId = me2.ManagedEntityRowId
inner join vRelationshipType ret3 WITH (NOLOCK) on ret3.RelationshipTypeRowId = re3.RelationshipTypeRowId and ret3.RelationshipTypeSystemName = 'ComTrade.Citrix.XenDesktop.DeliveryGroupContainer.Hosts.DeliveryGroup'
inner join vManagedEntity meDG WITH (NOLOCK) on meDG.ManagedEntityRowId = re3.TargetManagedEntityRowId
inner join vManagedEntityType metDG WITH (NOLOCK) 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 #ManagedEntity me on me.ManagedEntityRowId = meSite.ManagedEntityRowId
end
---------------------------------------------
-- All users from session table #Users table
---------------------------------------------
create table #UserNames("UserNameId" int, "UserName" nvarchar(255) COLLATE database_default)
insert into #UserNames
execute @ExecError = [ComtradeMPXAXD_SessionUserNames_DataGet]
create table #UserIds("UserNameId" int)
declare @P1 int
declare @P2 int
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
---------------------------------------------
-- Application and DG data (apps belonging to)
---------------------------------------------
create table #ApplicationDG (ApplicationMERowId int,
ApplicationKey nvarchar(256),
DeliveryGroupMERowId int)
insert into #ApplicationDG
select me1.ManagedEntityRowId, appKeys.ApplicationKey, meDG.ManagedEntityRowId
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
inner join (
select me.ManagedEntityRowId AppKeyMERowId, meps.PropertyValue ApplicationKey
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 = 'Comtrade.Citrix.XenDesktop.Application'
and metp.PropertySystemName = 'ApplicationKey'
) as appKeys on appKeys.AppKeyMERowId = me1.ManagedEntityRowId
inner join #SiteAndDGs sdg on meDG.ManagedEntityRowId = sdg.DG_RowId
------------------------------------------------------------------------------------
-- All discovered applications (TotalLaunchCount and LastLaunch(not related to input datetime range))
------------------------------------------------------------------------------------
--select getdate() as AllDiscoveredApplications_Start
create table #AllDiscoveredApplications ("AppMERowId" int, "ProperyName" nvarchar(256), "ProperyValue" nvarchar(256))
insert into #AllDiscoveredApplications
select me.ManagedEntityRowId, metp.PropertySystemName, meps.PropertyValue
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 = 'Comtrade.Citrix.XenDesktop.Application'
create table #AllDiscoveredApplicationsFinal ("AppMERowId" int, SiteId uniqueidentifier, SiteName nvarchar(256),
AssociatedDesktopGroupUids nvarchar(256), ApplicationKey uniqueidentifier, ApplicationName nvarchar(256), CreatedDate nvarchar(64))
insert into #AllDiscoveredApplicationsFinal
select * from #AllDiscoveredApplications
pivot (min(ProperyValue) for ProperyName in ([SiteId], [SiteName], [AssociatedDesktopGroupUids], [ApplicationKey], [Name], [CreatedDate]) ) as pt
--select getdate() as AllDiscoveredApplications_End
------------------------------------------------------------------------------------
-- Usage duration (Time of all application instances at least partly in reporting range excluding time outside reporting range.)
------------------------------------------------------------------------------------
create table #ApplicationRanges (ApplicationRowId int, startDT datetime, endDT datetime, actualStartDT datetime, UserId int, ClientHostnameId int, AppKey uniqueidentifier)
if @UserNamesText is not null and LEN(@UserNamesText) > 0
begin
insert into #ApplicationRanges
select distinct ai.CTXAXDApplicationRowId,
case
when ai.StartTime <= @StartDateTime then @StartDateTime --app started before range, still running
when ai.StartTime > @StartDateTime then ai.StartTime --app started after range, still running
end as applStart,
case
when ai.EndTime > @EndDateTime then @EndDateTime --app ended after range, finished
when ai.EndTime is null and getutcdate() >= @EndDateTime then @EndDateTime --app still running, end date not in future
when ai.EndTime is null and getutcdate() < @EndDateTime then getutcdate() --app still running, end date in future
else ai.EndTime -- end within range
end as applEnd,
ai.StartTime,
c.UserRowId,
c.ClientHostnameRowId,
a.ApplicationKey
from [ComtradeMPXAXD].[vCTXAXDApplicationInstance] ai WITH (NOLOCK)
join [ComtradeMPXAXD].[vCTXAXDConnection] c WITH (NOLOCK) on c.ConnectionId=ai.ConnectionId and c.SessionKey=ai.SessionKey
join [ComtradeMPXAXD].[vCTXAXDApplication] a WITH (NOLOCK) on a.CTXAXDApplicationRowId = ai.CTXAXDApplicationRowId
join #ApplicationDG adg on a.ApplicationKey = adg.ApplicationKey
join #SiteAndDGs sdg on sdg.DG_RowId = adg.DeliveryGroupMERowId
where ai.StartTime <= @EndDateTime
and (ai.EndTime is null or ai.EndTime >= @StartDateTime)
and c.UserRowId in (select u.UserNameId from #Users u)
end
else
begin
insert into #ApplicationRanges
select distinct ai.CTXAXDApplicationRowId,
case
when ai.StartTime <= @StartDateTime then @StartDateTime --app started before range, still running
when ai.StartTime > @StartDateTime then ai.StartTime --app started after range, still running
end as applStart,
case
when ai.EndTime > @EndDateTime then @EndDateTime --app ended after range, finished
when ai.EndTime is null and getutcdate() >= @EndDateTime then @EndDateTime --app still running, end date not in future
when ai.EndTime is null and getutcdate() < @EndDateTime then getutcdate() --app still running, end date in future
else ai.EndTime -- end within range
end as applEnd,
ai.StartTime,
c.UserRowId,
c.ClientHostnameRowId,
a.ApplicationKey
from [ComtradeMPXAXD].[vCTXAXDApplicationInstance] ai WITH (NOLOCK)
join [ComtradeMPXAXD].[vCTXAXDConnection] c WITH (NOLOCK) on c.ConnectionId=ai.ConnectionId and c.SessionKey=ai.SessionKey
join [ComtradeMPXAXD].[vCTXAXDApplication] a WITH (NOLOCK) on a.CTXAXDApplicationRowId = ai.CTXAXDApplicationRowId
join #ApplicationDG adg on a.ApplicationKey = adg.ApplicationKey
join #SiteAndDGs sdg on sdg.DG_RowId = adg.DeliveryGroupMERowId
where ai.StartTime <= @EndDateTime
and (ai.EndTime is null or ai.EndTime >= @StartDateTime)
end
------------------------------------------------------------------------------------
-- range table hours - 15 min interval
------------------------------------------------------------------------------------
create table #IntervalsInRange (DateDay datetime)
while @StartDateTime < @EndDateTime
begin
insert into #IntervalsInRange
values (@StartDateTime)
set @StartDateTime = DATEADD(MINUTE, @ConcurrentTimeInterval, @StartDateTime)
end
DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT dr.DateDay
FROM #IntervalsInRange dr
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @h
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #ConcurrentUsage
select @h, ar.ApplicationRowId, count (ar.ApplicationRowId) as InstCount, count (distinct ar.UserId) as UserCount
from #ApplicationRanges ar
where @h between ar.startDT and ar.endDT
group by ar.ApplicationRowId
PRINT @h
FETCH NEXT FROM MY_CURSOR INTO @h
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
CREATE TABLE #AppDGs ("ApplicationMERowID" int, "DeliveryGroup" nvarchar(256), "ApplicationFolder" nvarchar(256), "LastLaunch" datetime)
INSERT INTO #AppDGs
select distinct da.AppMERowId,
STUFF((SELECT ', ' + sdg1.DG_Name
from #SiteAndDGs sdg1
join #ApplicationDG adg1 on adg1.DeliveryGroupMERowId = sdg1.DG_RowId
where adg1.ApplicationMERowId = da.AppMERowId
FOR XML PATH('')),1,1,'') AS DeliveryGroup
,a.Folder
,max(ai.StartTime) as LastLaunch
from #SiteAndDGs sdg
join #ApplicationDG adg on adg.DeliveryGroupMERowId = sdg.DG_RowId
join #AllDiscoveredApplicationsFinal da on da.AppMERowId=adg.ApplicationMERowId
left join [ComtradeMPXAXD].[vCTXAXDApplication] a WITH (NOLOCK) on a.ApplicationKey = adg.ApplicationKey
left join [ComtradeMPXAXD].[vCTXAXDApplicationInstance] ai WITH (NOLOCK) on ai.CTXAXDApplicationRowId = a.CTXAXDApplicationRowId
group by da.AppMERowId, a.Folder
insert into #ReportMetrics
select distinct ar.ApplicationRowId, adg.ApplicationMERowId, sdg.Site_Name, adgs.DeliveryGroup, a.Name, a.Folder, dateadd(minute, @GMTDiff, a.CreatedDate),
ar.TotalSecondsGrouped, ar.DistinctUsers, ar.AppLaunches, ar.AppInstances, ci.MaxConcurrentInstance, dateadd(minute, @GMTDiff, adgs.LastLaunch),
ar.DistinctEndpoints, ci.MaxConcurrentUsers
from (
select ar.ApplicationRowId
,ar.AppKey
,sum(DateDiff(second, startDT, endDT)) as TotalSecondsGrouped
,count(case when ar.actualStartDT >= ar.startDT then 1 else NULL end) as AppLaunches
,count(ar.ApplicationRowId) as AppInstances
,count(distinct ar.UserId) as DistinctUsers
,count(distinct ar.ClientHostnameId) as DistinctEndpoints
from #ApplicationRanges ar
group by ar.ApplicationRowId, ar.AppKey
) ar
join [ComtradeMPXAXD].[vCTXAXDApplication] a WITH (NOLOCK) on a.CTXAXDApplicationRowId = ar.ApplicationRowId
join #ApplicationDG adg on adg.ApplicationKey = ar.AppKey
join #SiteAndDGs sdg on sdg.DG_RowId = adg.DeliveryGroupMERowId
join #AppDGs adgs on adgs.ApplicationMERowID=adg.ApplicationMERowId
left join (
select aci.ApplicationRowId, max(aci.InstCount) as MaxConcurrentInstance, max(aci.UserCount) as MaxConcurrentUsers
from #ConcurrentUsage aci
group by aci.ApplicationRowId
) as ci on ci.ApplicationRowId = ar.ApplicationRowId
---------------------------------------------
-- select upon chosen scope
---------------------------------------------
if @Scope = 1 --all
begin
select * from (
select distinct rm.ApplicationRowId as ApplicationRowId, da.AppMERowId as ApplicationMERowId, da.SiteName as SiteName, adgs.DeliveryGroup, da.ApplicationName as AppName,
adgs.ApplicationFolder as AppFolder,
case when da.CreatedDate ='' then NULL when da.CreatedDate is null then NULL else dateadd(minute, @GMTDiff, cast(da.CreatedDate as datetime2)) end as CreatedDate,
rm.UsageDuration, rm.DistinctUsers, rm.AppLaunches, rm.AppInstances, rm.MaxConcurrentInstances,
case when adgs.LastLaunch is null then NULL when adgs.LastLaunch = '' then NULL else dateadd(minute, @GMTDiff, adgs.LastLaunch) end as LastUsage,
rm.DistinctEndpoints, rm.MaxConcurrentUsers
from #ReportMetrics rm
right join #AllDiscoveredApplicationsFinal da on da.AppMERowId=rm.ApplicationMERowId
join #AppDGs adgs on adgs.ApplicationMERowID=da.AppMERowId
) as finalMetrics
where
1 = case
when @AppCreatedDays is null or @AppCreatedDays < 1 then 1
when finalMetrics.CreatedDate is null then 0
when finalMetrics.CreatedDate = '' then 0
when finalMetrics.CreatedDate < dateadd(day, -@AppCreatedDays, SYSUTCDATETIME()) then 1
else 0
end
order by SiteName, UsageDuration desc, LastUsage desc
end
else if @Scope = 2 --bottom
begin
select * from (
select distinct top (@Count) rm.ApplicationRowId as ApplicationRowId, da.AppMERowId as ApplicationMERowId, da.SiteName as SiteName, adgs.DeliveryGroup, da.ApplicationName as AppName,
adgs.ApplicationFolder as AppFolder,
case when da.CreatedDate ='' then NULL when da.CreatedDate is null then NULL else dateadd(minute, @GMTDiff, cast(da.CreatedDate as datetime2)) end as CreatedDate,
rm.UsageDuration, rm.DistinctUsers, rm.AppLaunches, rm.AppInstances, rm.MaxConcurrentInstances,
case when adgs.LastLaunch is null then NULL when adgs.LastLaunch = '' then NULL else dateadd(minute, @GMTDiff, adgs.LastLaunch) end as LastUsage,
rm.DistinctEndpoints, rm.MaxConcurrentUsers
from #ReportMetrics rm
right join #AllDiscoveredApplicationsFinal da on da.AppMERowId=rm.ApplicationMERowId
join #AppDGs adgs on adgs.ApplicationMERowID=da.AppMERowId
) as finalMetrics
where
1 = case
when @AppCreatedDays is null or @AppCreatedDays < 1 then 1
when finalMetrics.CreatedDate is null then 0
when finalMetrics.CreatedDate = '' then 0
when finalMetrics.CreatedDate < dateadd(day, -@AppCreatedDays, SYSUTCDATETIME()) then 1
else 0
end
order by SiteName, UsageDuration
end
else if @Scope = 3 --top
begin
select top (@Count) * from
(select * from #ReportMetrics rm
where
1 = case
when @AppCreatedDays is null or @AppCreatedDays < 1 then 1
when rm.CreatedDate is null then 0
when rm.CreatedDate = '' then 0
when rm.CreatedDate < dateadd(day, -@AppCreatedDays, SYSUTCDATETIME()) then 1
else 0
end
) as finalMetrics
order by UsageDuration desc, SiteName
end
else -- 4, unused
begin
select * from (
select distinct rm.ApplicationRowId as ApplicationRowId, da.AppMERowId as ApplicationMERowId, da.SiteName as SiteName, adgs.DeliveryGroup, da.ApplicationName as AppName,
adgs.ApplicationFolder as AppFolder,
case when da.CreatedDate ='' then NULL when da.CreatedDate is null then NULL else dateadd(minute, @GMTDiff, cast(da.CreatedDate as datetime2)) end as CreatedDate,
rm.UsageDuration, rm.DistinctUsers, rm.AppLaunches, rm.AppInstances, rm.MaxConcurrentInstances,
case when adgs.LastLaunch is null then NULL when adgs.LastLaunch = '' then NULL else dateadd(minute, @GMTDiff, adgs.LastLaunch) end as LastUsage,
rm.DistinctEndpoints, rm.MaxConcurrentUsers
from #ReportMetrics rm
right join #AllDiscoveredApplicationsFinal da on da.AppMERowId=rm.ApplicationMERowId
join #AppDGs adgs on adgs.ApplicationMERowID=da.AppMERowId
where (rm.AppInstances = 0 or rm.AppInstances is null)
) as finalMetrics
where
1 = case
when @AppCreatedDays is null or @AppCreatedDays < 1 then 1
when finalMetrics.CreatedDate is null then 0
when finalMetrics.CreatedDate = '' then 0
when finalMetrics.CreatedDate < dateadd(day, -@AppCreatedDays, SYSUTCDATETIME()) then 1
else 0
end
order by SiteName, LastUsage
end
drop table #ManagedEntity
drop table #UserNames
drop table #Users
drop table #UserIds
drop table #SiteAndDGs
drop table #ApplicationDG
drop table #ApplicationRanges
drop table #IntervalsInRange
drop table #ConcurrentUsage
drop table #AllDiscoveredApplications
drop table #AllDiscoveredApplicationsFinal
drop table #AppDGs
drop table #ReportMetrics
Home