IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ApplicationUserSession_DataGet')
BEGIN
EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ApplicationUserSession_DataGet] AS RETURN 1')
END
GO
ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationUserSession_DataGet]
@StartDate_BaseValue datetime,
@EndDate_BaseValue datetime,
@StartDate_BaseValueGMT datetime,
@DataAggregation int, -- 0-hourly, 1-daily
@ObjectList xml,
@ObjectType varchar(max),
@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))
IF @ObjectType = 'ComTrade.Citrix.XenDesktop.Application'
BEGIN
---- applications
CREATE TABLE #ApplicationDistinguishedNamesFinal
("ManagedEntityRowId" int PRIMARY KEY, "ApplicationName" nvarchar(max) COLLATE database_default, "ApplicationId" 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 ('Id', '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 = 'Id' 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
inner join vManagedEntityType met2 on met2.ManagedEntityTypeRowId = me1.ManagedEntityTypeRowId AND met2.ManagedEntityTypeSystemName = 'ComTrade.Citrix.XenDesktop.Application'
inner join vRelationship re3 on re3.SourceManagedEntityRowId = me1.ManagedEntityRowId
inner join vRelationshipType ret3 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 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
--select * from #ApplicationDG
IF @DataAggregation = 0 --HOURLY
BEGIN
SELECT Distinct(DATEADD(minute, @GMTDiff, vPerf.DateTime)) AS DateTime, vPerf.MaxValue AS MaxValue,
CONCAT(#ApplicationDistinguishedNamesFinal.ApplicationId, '-', #ApplicationDistinguishedNamesFinal.SiteId) AS InstanceId, #ApplicationDistinguishedNamesFinal.ApplicationName,
#SDCNamesFinal.SiteName AS SiteName, #ApplicationDG.DeliveryGroup
FROM Perf.vPerfHourly as vPerf
INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN vRule on vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN #SDCNamesFinal on #SDCNamesFinal.ManagedEntityRowId = vPerf.ManagedEntityRowId
INNER JOIN #ApplicationDistinguishedNamesFinal on #ApplicationDistinguishedNamesFinal.ApplicationId = vPerformanceRuleInstance.InstanceName
and #ApplicationDistinguishedNamesFinal.SiteId = #SDCNamesFinal.SiteId
INNER JOIN #ApplicationDG on #ApplicationDG.ApplicationID= #ApplicationDistinguishedNamesFinal.ManagedEntityRowId
WHERE vPerf.DateTime >= @StartDate_BaseValue
AND vPerf.DateTime < @EndDate_BaseValue
AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.Application.ConcurrentSessions.PerfDW'
ORDER BY DateTime
END
ELSE
BEGIN
SELECT Distinct(DATEADD(minute, @GMTDiff, vPerf.DateTime)) AS DateTime, vPerf.MaxValue AS MaxValue,
CONCAT(#ApplicationDistinguishedNamesFinal.ApplicationId, '-', #ApplicationDistinguishedNamesFinal.SiteId) AS InstanceId, #ApplicationDistinguishedNamesFinal.ApplicationName,
#SDCNamesFinal.SiteName AS SiteName, #ApplicationDG.DeliveryGroup
FROM Perf.vPerfDaily as vPerf
INNER JOIN vPerformanceRuleInstance ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN vRule on vRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN #SDCNamesFinal on #SDCNamesFinal.ManagedEntityRowId = vPerf.ManagedEntityRowId
INNER JOIN #ApplicationDistinguishedNamesFinal on #ApplicationDistinguishedNamesFinal.ApplicationId = vPerformanceRuleInstance.InstanceName
and #ApplicationDistinguishedNamesFinal.SiteId = #SDCNamesFinal.SiteId
INNER JOIN #ApplicationDG on #ApplicationDG.ApplicationID= #ApplicationDistinguishedNamesFinal.ManagedEntityRowId
WHERE vPerf.DateTime >= @StartDate_BaseValue
AND vPerf.DateTime < @EndDate_BaseValue
AND vRule.RuleSystemName = 'ComTrade.Citrix.XenDesktop.Application.ConcurrentSessions.PerfDW'
ORDER BY DateTime
END
END
DROP TABLE #ManagedEntity
DROP TABLE #ApplicationDistinguishedNamesFinal
DROP TABLE #SDCNamesFinal
DROP TABLE #ApplicationDG
DROP TABLE #ApplicationDG1
Home