Res.Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.ApplicationUserActivity.Install (Resource)

Element properties:

File Content: Comtrade.Citrix.XenDesktop.EndUser.Reports.ReportScript.ApplicationUserActivity.Install.sql

IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ComtradeMPXAXD_ApplicationUserActivity_DataGet')

BEGIN

EXECUTE ('CREATE PROCEDURE dbo.[ComtradeMPXAXD_ApplicationUserActivity_DataGet] AS RETURN 1')

END

GO



ALTER PROCEDURE [dbo].[ComtradeMPXAXD_ApplicationUserActivity_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))



INSERT INTO #ManagedEntity(ManagedEntityRowId)



EXECUTE @ExecError = [Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]

@ObjectList = @ObjectList,

@StartDate = @StartDate_BaseValue, 

@EndDate = @EndDate_BaseValue,

@ContainmentLevelCount = 0,

@ContainmentStartLevel = 0



------------------------------------

-- CREATE #USERS TABLE

------------------------------------

CREATE TABLE #UserNames("UserNameId" int, "UserName" nvarchar(255) COLLATE database_default)

INSERT INTO #UserNames

EXECUTE @ExecError = [ComtradeMPXAXD_SessionUserNames_DataGet]







--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_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)

insert into #DGs

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

       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')





--table of all selected applications names and managedentityrowids

CREATE TABLE #ApplicationNamesFinalTmp ("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

CREATE TABLE #ApplicationNamesFinal ("ManagedEntityRowId" int, "ApplicationName" nvarchar(max) COLLATE database_default, "SiteId" nvarchar(max) COLLATE database_default, "ApplicationKey" nvarchar(max) COLLATE database_default,"FromDateTime" datetime)

CREATE TABLE #ApplicationNames ("ManagedEntityRowId" int, "PropertyDefaultName" nvarchar(256) COLLATE database_default, "PropertyValue" nvarchar(max) COLLATE database_default, "FromDateTime" datetime)

INSERT INTO #ApplicationNames    

SELECT  me.ManagedEntityRowId, metp.PropertyDefaultName, meps.PropertyValue, meps.FromDateTime

FROM    vManagedEntityTypeProperty metp

        INNER JOIN vManagedEntityType met ON metp.ManagedEntityTypeRowId = met.ManagedEntityTypeRowId 

        INNER JOIN vManagedEntity me ON met.ManagedEntityTypeRowId = me.ManagedEntityTypeRowId 

        --filter only selected objects!

        INNER JOIN #ManagedEntity selectedApps on selectedApps.ManagedEntityRowId = me.ManagedEntityRowId

        INNER JOIN (select mpnoxml.ManagedEntityRowId, MAX(mpnoxml.ManagedEntityPropertyRowId) as MaxProperyId

                    from vManagedEntityProperty mpnoxml

                    group by mpnoxml.ManagedEntityRowId) LatestProperties on LatestProperties.ManagedEntityRowId = me.ManagedEntityRowId 

        INNER JOIN vManagedEntityPropertySet meps ON metp.PropertyGuid = meps.PropertyGuid AND LatestProperties.MaxProperyId = meps.ManagedEntityPropertyRowId

WHERE   --met.ManagedEntityTypeSystemName IN ('Comtrade.Citrix.XenApp.Application')  AND 

             metp.PropertySystemName IN ('Name', 'ApplicationKey', 'SiteId') 





INSERT INTO #ApplicationNamesFinalTmp                                            

SELECT adn.ManagedEntityRowId, adn.PropertyDefaultName, adn.PropertyValue, adn.FromDateTime

FROM (

       SELECT PropertyDefaultName, ManagedEntityRowId, MAX(FromDateTime) as MaxFromDateTime

       FROM #ApplicationNames GROUP BY PropertyDefaultName, ManagedEntityRowId

) AS x INNER JOIN #ApplicationNames AS adn ON adn.ManagedEntityRowId = x.ManagedEntityRowId AND adn.PropertyDefaultName = x.PropertyDefaultName AND adn.FromDateTime = x.MaxFromDateTime





INSERT INTO #ApplicationNamesFinal

SELECT  AppNames.ManagedEntityRowId, AppNames.PropertyValue, FarmNames.PropertyValue, TempNames.PropertyValue, AppNames.FromDateTime

FROM   #ApplicationNamesFinalTmp AS AppNames 

          INNER JOIN  #ApplicationNamesFinalTmp AS FarmNames ON AppNames.ManagedEntityRowId = FarmNames.ManagedEntityRowId 

       AND FarmNames.PropertyDefaultName = 'Site Id' AND AppNames.PropertyDefaultName = 'Name'

          INNER JOIN #ApplicationNamesFinalTmp as TempNames on TempNames.ManagedEntityRowId = AppNames.ManagedEntityRowId

          AND TempNames.PropertyDefaultName = 'Application Key'

             



DROP TABLE #ApplicationNamesFinalTmp    

DROP TABLE #ApplicationNames







CREATE TABLE #ApplicationDG1 ("ApplicationName" nvarchar(256), "ApplicationID" int, "DeliveryGroup" nvarchar(256))

INSERT INTO #ApplicationDG1



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

       

--table that contains all DG where application is published

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







CREATE TABLE #FinalUsages ("ApplicationName" nvarchar(256), "ApplicationKey" UNIQUEIDENTIFIER, "DeliveryGroup" nvarchar(256),

"Site_Name" nvarchar(256), "Site_id" int, "UserName" nvarchar(256), "UserId" int, "Application_StartDate" datetime, 

"Application_EndDate" datetime, "Application_Duration" int, "Application_IsActive" int, "ClientIp" nvarchar(256),

"DNSName" nvarchar(256))





IF @ReportType = 1

       

       INSERT INTO #FinalUsages

	   SELECT DISTINCT

	   vApp.Name as ApplicationName,

                vApp.ApplicationKey,

                appdg.DeliveryGroup,

                DGs.Site_Name,

                DGs.Site_Id,

                ltUser.UserName,

                ltUser.LTUserRowId as UserId,

                CASE

                    WHEN vAppInst.StartTime > vConn.StartDate

                    THEN DATEADD(minute, @GMTDiff, vAppInst.StartTime) 

                    ELSE DATEADD(minute, @GMTDiff, vConn.StartDate) 

                    END as Application_StartDate,

                CASE 

                    WHEN vConn.EndDate IS NULL AND vConn.StartDate IS NOT NULL 

                    THEN  

                         CASE

                              WHEN vAppInst.EndTime IS NOT NULL

                              THEN DATEADD(minute, @GMTDiff, vAppInst.EndTime) 

                              ELSE DATEADD(minute, @GMTDiff, GETUTCDATE()) 

                         END

                         ELSE 

                              DATEADD(minute, @GMTDiff, vConn.EndDate)

                     END as Application_EndDate,

                CASE 

                     WHEN vConn.EndDate IS NULL AND vConn.StartDate IS NOT NULL 

                     THEN  

                          CASE

                              WHEN vAppInst.EndTime IS NOT NULL

                              THEN DATEDIFF(second, vConn.StartDate, vAppInst.EndTime) 

                              ELSE 

                                   CASE 

                                        WHEN vAppInst.StartTime > vConn.StartDate

                                        THEN DATEDIFF(second, vAppInst.StartTime, GETUTCDATE()) 

                                        ELSE DATEDIFF(second, vConn.StartDate, GETUTCDATE()) 

                                   END

                              END

                           ELSE 

                               DATEDIFF(second, vConn.StartDate, vConn.EndDate) 

                     END as Application_Duration,

                CASE WHEN vAppInst.EndTime IS NULL AND vConn.EndDate IS NULL

                     THEN 1 ELSE 0 END as Application_IsActive,

                client.ClientIp,

                vMac.DNSName

                    

       FROM ComtradeMPXAXD.vCTXAXDConnection vConn

             inner join ComtradeMPXAXD.vCTXAXDSession vSess on vSess.SessionKey = vConn.SessionKey and vSess.IsApplication = 1

             inner join ComtradeMPXAXD.vCTXAXDMachine vMac on vMac.CTXAXDMachineRowId = vSess.CTXAXDMachineRowId

             inner join ComtradeMPXAXD.vCTXAXDApplicationInstance vAppInst on vAppInst.SessionKey= vConn.SessionKey and (vAppInst.StartTime < vConn.EndDate or vConn.EndDate is null) and (vAppInst.EndTime > vConn.StartDate or vAppInst.EndTime is null)

             inner join ComtradeMPXAXD.vCTXAXDApplication vApp on vApp.CTXAXDApplicationRowId = vAppInst.CTXAXDApplicationRowId

             inner join ComtradeMPXAXD.LTClientIp client on client.LTClientIpRowId = vConn.ClientIpRowId

             inner join ComtradeMPXAXD.LTUser ltUser on ltUser.LTUserRowId = vConn.UserRowId

             inner join #ApplicationNamesFinal appnames on appnames.ApplicationKey = vApp.ApplicationKey

             inner join #ApplicationDG appdg on appdg.ApplicationID = appnames.ManagedEntityRowId

             INNER JOIN #DGs DGs on vMac.ME_DeliveryGroupRowId = DGs.DG_RowID



       WHERE ((vAppInst.StartTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vAppInst.StartTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)) 

             OR (vAppInst.StartTime <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vAppInst.EndTime >=  DATEADD(minute, -@GMTDiff, @StartDate_BaseValue))

             or (vAppInst.EndTime IS NULL AND vConn.EndDate IS NULL AND vSess.EndDate = '1901-01-01 00:00:00.000' and vAppInst.StartTime <= DATEADD(minute, -@GMTDiff, @EndDate_BaseValue))

             or (vAppInst.StartTime <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vAppInst.EndTime >=  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 vApp.Name as ApplicationName,

       vApp.ApplicationKey,

          appdg.DeliveryGroup,

          DGs.Site_Name,

          DGs.Site_Id,

          ltUser.UserName,

          ltUser.LTUserRowId as UserId,

          MIN (DATEADD(minute, @GMTDiff, vAppInst.StartTime)) as Application_StartDate,

          CASE WHEN max(vAppInst.EndTime) IS NULL AND min(vAppInst.StartTime) IS NOT NULL THEN DATEADD(minute, @GMTDiff, GETUTCDATE()) ELSE DATEADD(minute, @GMTDiff, MAX(vAppInst.EndTime) ) END as Application_EndDate,

          CASE WHEN max(vAppInst.EndTime) IS NULL AND min(vAppInst.StartTime) IS NOT NULL THEN DATEDIFF(second, MIN(vAppInst.StartTime), GETUTCDATE()) ELSE DATEDIFF(second, Min(vAppInst.StartTime), max(vAppInst.EndTime) ) END as Application_Duration,

          CASE WHEN max(vAppInst.EndTime) IS NULL THEN 1 ELSE 0 END as Application_IsActive,

          client.ClientIp,

          vMac.DNSName

                    

       FROM ComtradeMPXAXD.vCTXAXDConnection vConn

             inner join ComtradeMPXAXD.vCTXAXDSession vSess on vSess.SessionKey = vConn.SessionKey and vSess.IsApplication = 1

             inner join ComtradeMPXAXD.vCTXAXDMachine vMac on vMac.CTXAXDMachineRowId = vSess.CTXAXDMachineRowId

             inner join ComtradeMPXAXD.vCTXAXDApplicationInstance vAppInst on vAppInst.ConnectionId = vConn.ConnectionId and vAppInst.SessionKey= vConn.SessionKey

             inner join ComtradeMPXAXD.vCTXAXDApplication vApp on vApp.CTXAXDApplicationRowId = vAppInst.CTXAXDApplicationRowId

             inner join ComtradeMPXAXD.LTClientIp client on client.LTClientIpRowId = vConn.ClientIpRowId

             inner join ComtradeMPXAXD.LTUser ltUser on ltUser.LTUserRowId = vConn.UserRowId

             inner join #ApplicationNamesFinal appnames on appnames.ApplicationKey = vApp.ApplicationKey

             inner join #ApplicationDG appdg on appdg.ApplicationID = appnames.ManagedEntityRowId

             INNER JOIN #DGs DGs on vMac.ME_DeliveryGroupRowId = DGs.DG_RowID



       WHERE  ((vAppInst.StartTime >= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vAppInst.StartTime < DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)) 

              OR (vAppInst.StartTime <= DATEADD(minute, -@GMTDiff, @StartDate_BaseValue) AND vAppInst.EndTime >=  DATEADD(minute, -@GMTDiff, @StartDate_BaseValue))

              OR (vAppInst.EndTime IS NULL AND vSess.EndDate = '1901-01-01 00:00:00.000' and vAppInst.StartTime <= DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)))

              and vConn.StartDate <= DATEADD(minute, -@GMTDiff, @EndDate_BaseValue)

			  and vConn.Failure = 0



	    GROUP BY vSess.SessionKey, vApp.Name, vApp.ApplicationKey, appdg.DeliveryGroup, DGs.Site_Name, DGs.Site_Id, ltUser.UserName, ltUser.LTUserRowId,

		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 #ApplicationNamesFinal

DROP TABLE #ApplicationDG1

DROP TABLE #ApplicationDG

DROP TABLE #DGs



END



GO



/* 	=============================================================== 

	PERMISSIONS 

	===============================================================  */

	

GRANT EXECUTE ON dbo.[ComtradeMPXAXD_ApplicationUserActivity_DataGet] TO OpsMgrReader 



GO