--Name:- Agent Capacity License Usage
--Description:- Capacity Usage and License Usage counts per IDA

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 3
DECLARE @surveyXML NVARCHAR(MAX)


DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END


-- Get license usage info except for capacity licenses. Total capacity licenses usage are collected from Query 1.
DECLARE @evalFld    varchar(1024)
DECLARE @permFld    varchar(1024)
DECLARE @licName    varchar(1024)
DECLARE @licTypeId  integer
DECLARE @licGroupId	integer
DECLARE @evalTotal  integer
DECLARE @evalUsed   integer
DECLARE @bEval      integer
DECLARE @evalExpiry integer
DECLARE @permTotal  integer
DECLARE @permUsed   integer

DECLARE @LicData TABLE ( 
    IsLicType					INTEGER,
	TypeId						INTEGER,
	TypeName					VARCHAR(1024),
	LicType						INTEGER,
	TotalPermLicensesIssued		INTEGER,
	TotalPermLicensesUsed		INTEGER,
	FreePermLicensesAvailable	INTEGER,
	TotalEvalLicensesIssued		INTEGER,
	TotalEvalLicensesUsed		INTEGER,
	FreeEvalLicensesAvailable	INTEGER,
	EvalLicenseExpiryDate		INTEGER,
	UsedBackupSize				BIGINT,    
	UsedArchiveSize				BIGINT,
	UsedSnapSize				BIGINT,    
	UsedReplicationSize			BIGINT,
	UsedAgents					INT,
	Flag					INT
)

DECLARE @CloudAppsCapacityUsage TABLE ( 
    	InstanceTypeId		INT,
	BackupSize		BIGINT,    
	ArchiveSize		BIGINT,
	SnapshotSize		BIGINT,    
	ReplicationSize		BIGINT,
	Agents			INT
)

CREATE TABLE #LicAppType (simLicAppTypeId INT, eval_fld1 CHAR(64), perm_fld1 CHAR(64))

IF	EXISTS (SELECT id FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId >= 15)
	EXEC sp_executesql N'INSERT INTO #LicAppType SELECT simLicAppTypeId, eval_fld1, perm_fld1 FROM licAAL A WITH(NOLOCK) WHERE simLicAppTypeId < 1000 AND commcellId = 2'
ELSE
	EXEC sp_executesql N'INSERT INTO #LicAppType SELECT simLicAppTypeId, eval_fld1, perm_fld1 FROM licAAL A WITH(NOLOCK) WHERE simLicAppTypeId < 1000'

DECLARE @SPNumber  INT = (SELECT MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) 
                                WHERE clientId = (select TOP 1 clientid from APP_Platform where platformType = 1))

DECLARE licList CURSOR FOR
    SELECT B.id, B.short_name, B.groupId, eval_fld1, perm_fld1 
    FROM #LicAppType A INNER JOIN simLicAppTypeV2 B WITH(NOLOCK) ON A.simLicAppTypeId = B.id
OPEN licList
FETCH NEXT FROM licList INTO @licTypeId, @licName, @licGroupId, @evalFld, @permFld
WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @evalTotal  = 0
    SET @evalUsed   = 0
    SET @bEval      = 0
    SET @evalExpiry = 0
    SET @permTotal  = 0
    SET @permUsed   = 0
    IF len(@evalFld) > 0
    begin
        IF  EXISTS (SELECT id FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId >= 16)
        BEGIN
            IF (@SPNumber >= 21)
            	EXEC xp_getAALInfo2 @evalFld, @evalTotal OUTPUT, @evalUsed OUTPUT, @bEval OUTPUT, @evalExpiry OUTPUT
            ELSE
            	EXEC MASTER.dbo.xp_getAALInfo2 @evalFld, @evalTotal OUTPUT, @evalUsed OUTPUT, @bEval OUTPUT, @evalExpiry OUTPUT
        END
        ELSE
            EXEC MASTER.dbo.xp_getAALInfo @evalFld, @evalTotal OUTPUT, @evalUsed OUTPUT
    end

   IF len(@permFld) > 0
	BEGIN
		IF (@SPNumber >= 21)
        	EXEC xp_getAALInfo @permFld, @permTotal OUTPUT, @permUsed OUTPUT
        ELSE
        	EXEC MASTER.dbo.xp_getAALInfo @permFld, @permTotal OUTPUT, @permUsed OUTPUT
	END
    INSERT INTO @LicData
    SELECT	1, @licTypeId, @licName, @licGroupId, 
			@permTotal, @permUsed, CASE WHEN @permTotal = -1 THEN -1 ELSE @permTotal - @permUsed END, 
			@evalTotal, @evalUsed, CASE WHEN @evalTotal = -1 THEN -1 ELSE @evalTotal - @evalUsed END, 
			@evalExpiry, 0, 0, 0, 0, 0, 0
    FETCH NEXT FROM licList INTO @licTypeId, @licName, @licGroupId, @evalFld, @permFld
END
CLOSE licList
DEALLOCATE licList

DROP TABLE #LicAppType

IF object_id('tempdb.dbo.#temp_lic1') IS NOT null DROP TABLE #temp_lic1
	CREATE TABLE #temp_lic1( t_cid int, t_lictype int, t_isVM INT	)
	
IF object_id('tempdb.dbo.#tempClientList') IS NOT null DROP TABLE #tempClientList
	CREATE TABLE #tempClientList( t_clientId INT, t_appName VARCHAR(64), t_lictype INT, t_appTypeId INT, t_jobid INT, t_vmclientId INT	)
DECLARE @FSOnlyServer INT = 0
DECLARE @LaptopDesktop INT = 0
DECLARE @ApplicationServer INT = 0
DECLARE @ACVOnly INT = 0
DECLARE @VMCount INT = 0
DECLARE @FsOnlyLicType INT = 100029
DECLARE @AppServerLicType INT = 100030
DECLARE @VMWithoutPkgLicType INT = 200001
DECLARE @LapDesktopLicType INT = 200002
DECLARE @AcvOnlyLicType INT = 200003
DECLARE @FSOnlyName VARCHAR(64) = 'File System Only Server'
DECLARE @AppServerName VARCHAR(64) = 'Application Server'
DECLARE @LapDeskTopName VARCHAR(64) = 'DLO Only'
DECLARE @VMPkgName VARCHAR(64) = 'VM Only'
DECLARE @ACVName VARCHAR(64) = 'VM With Application'

insert into #temp_lic1
SELECT cid, LicType, 0 FROM LicUsage WITH (NOLOCK) WHERE OpType = 'Install' AND LicType IN (1,2,3,16,17) AND (Eval = 0 OR ExpiryDateUTC > GETUTCDATE())

--Only count physical machine
UPDATE T SET t_isVM = 1
FROM #temp_lic1 T INNER JOIN APP_ClientProp CP ON T.t_cid = CP.componentNameId
WHERE CP.attrName ='Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0

SELECT @ApplicationServer = count(DISTINCT t_cid) FROM #temp_lic1 where t_lictype IN (3,16,17) AND t_isVM = 0
INSERT INTO #tempClientList
SELECT DISTINCT t_cid,@AppServerName, @AppServerLicType, 0, 0, 0 FROM #temp_lic1 where t_lictype IN (3,16,17) AND t_isVM = 0
--Remove all clients which are already counted towards application server
DELETE #temp_lic1
WHERE t_cid IN (SELECT DISTINCT t_cid FROM #temp_lic1 where t_lictype IN (3,16,17) AND t_isVM = 0) 

SELECT @LaptopDesktop = count(DISTINCT t_cid) FROM #temp_lic1 where t_lictype = 2
INSERT INTO #tempClientList
SELECT DISTINCT t_cid,@LapDeskTopName, @LapDesktopLicType, 0, 0, 0 FROM #temp_lic1 where t_lictype = 2 

--Remove all clients which are already counted towards LaptopDesktop
DELETE #temp_lic1
WHERE t_cid IN (SELECT DISTINCT t_cid FROM #temp_lic1 where t_lictype = 2 )

SELECT @FSOnlyServer = count(DISTINCT t_cid) FROM #temp_lic1 where t_lictype = 1
INSERT INTO #tempClientList
SELECT DISTINCT t_cid, @FSOnlyName, @FsOnlyLicType, 0, 0, 0 FROM #temp_lic1 where t_lictype = 1

DROP TABLE #temp_lic1

DECLARE @relId  INT = 0
SELECT @relId = MAX(id) FROM simAllGalaxyRel WITH(NOLOCK)

IF(@relId >= 16 )
BEGIN
	IF object_id('tempdb.dbo.#tempVMsWithIdAppType') IS NOT null DROP TABLE #tempVMsWithIdAppType
	CREATE TABLE #tempVMsWithIdAppType(t_Id INT, t_appTypeId INT)

	EXEC LicGetVMAppcalss1 '#tempVMsWithIdAppType'

	SET @ACVOnly =  (SELECT count(DISTINCT t_Id) FROM #tempVMsWithIdAppType)
	
	INSERT INTO #tempClientList
	SELECT DISTINCT t_Id, @ACVName, @AcvOnlyLicType, t_appTypeId, 0, 0 FROM #tempVMsWithIdAppType

END

IF OBJECT_ID('VMProtectionCoverageProc', 'P') IS NOT NULL
BEGIN
	IF object_id('tempdb.dbo.#tempVMDPJobs') IS NOT null DROP TABLE #tempVMDPJobs
		CREATE TABLE #tempVMDPJobs( VMName              nvarchar(1024),
						BackupStatus                    VARCHAR(256), -- 0 - Backed Up, 1 -  Did Not Backup, 2 - manually exlcuded,
						InstanceName                    nvarchar(1024),
						BackupSetName                   nvarchar(1024),
						SubClientName                   nvarchar(1024),
						VMSizeGB                        BIGINT,
						VMBackedupSizeGB                BIGINT,
						EstimatedVMDataWrittenSizeGB    BIGINT,
						LastSuccessfulBackupJobId       BIGINT,
						LastBackupTime                  datetime,
						LastBackupType                  VARCHAR(256),
						TotalTimeForLastBackup          BIGINT,
						clientId                        INT,
						CommCellId                      INT,
						CommCellName                    varchar(256),
						IsVMArchived                    INT)
						
	DECLARE @SpVersion BIGINT = 00010002003200030000
	SELECT  @SpVersion = CAST(revision AS BIGINT) FROM GxDBVersions WITH(NOLOCK) where name = 'VMProtectionCoverageProc' AND ISNUMERIC(revision) = 1;

	IF(@relId < 16 OR (@relId = 16 AND @SpVersion < 00010002003200030000))
		ALTER TABLE #tempVMDPJobs DROP COLUMN IsVMArchived

	INSERT INTO #tempVMDPJobs EXEC VMProtectionCoverageProc -1, 0

	IF(@relId >= 16 )
	BEGIN
		--Subtract those clients where Appaware backups are getting counted under ACV	
		UPDATE #tempClientList SET t_jobid = LastSuccessfulBackupJobId
		FROM #tempVMDPJobs WHERE clientId = t_clientId AND t_lictype = @AcvOnlyLicType
		DELETE #tempVMDPJobs FROM #tempVMsWithIdAppType WHERE clientId = t_Id
	END

	SELECT @VMCount = COUNT(distinct VMName) FROM #tempVMDPJobs WHERE BackupStatus = 'Currently protected'
	
	IF(@relId >= 16 )
	BEGIN
		--Subtract those jobs where Appaware backups are getting counted under ACV
		DELETE #tempVMDPJobs FROM #tempClientList WHERE LastSuccessfulBackupJobId = t_jobid
	END
	
	INSERT INTO #tempClientList
	SELECT DISTINCT C.clientId, @VMPkgName, @VMWithoutPkgLicType, 0, 0, A.clientId FROM #tempVMDPJobs A join JMBkpStats B ON A.LastSuccessfulBackupJobId = B.jobId
	JOIN APP_Application C ON B.appId = C.id
	WHERE BackupStatus = 'Currently protected'

END

INSERT INTO @LicData
VALUES	(1, @FsOnlyLicType, @FSOnlyName, @FsOnlyLicType, -1, @FSOnlyServer, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
        (1, @AppServerLicType, @AppServerName, @AppServerLicType, -1, @ApplicationServer, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
		(1, @VMWithoutPkgLicType, @VMPkgName, @VMWithoutPkgLicType, -1, @VMCount, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
		(1, @LapDesktopLicType, @LapDeskTopName, @LapDesktopLicType, -1, @LaptopDesktop, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0),
		(1, @AcvOnlyLicType, @ACVName, @AcvOnlyLicType, -1, @ACVOnly, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)


IF OBJECT_ID('tempdb.dbo.#tmpCommCellUsage') is not null drop table #tmpCommCellUsage
CREATE TABLE #tmpCommCellUsage (
    EnterpriseBackupSize BIGINT, CoreBackupSize BIGINT, EnterpriseArchiveSize BIGINT, CoreArchiveSize BIGINT, 
    SnapshotSize BIGINT, ReplicationSize BIGINT, nJobType INT, 
    appId INT, jobID INT, ClientName NVARCHAR(255), ClientId INT, AppTypeId INT, AppTypeName VARCHAR(255), 
    BackupSetName NVARCHAR(128), InstanceName NVARCHAR(512), SubclientName NVARCHAR(128), 
    SPId INT, SPName NVARCHAR(144), UncompBytes BIGINT, DedupEnabled INT, SecondaryEncryption INT, flag INT
)

DECLARE	@SpRevision VARCHAR(20)
SELECT  @SpRevision = revision FROM GxDBVersions WITH(NOLOCK) where name = 'QS_CommCellUsageReport'

SET @SpVersion = 0
IF ISNUMERIC(@SpRevision) = 1
	SELECT  @SpVersion = CAST(@SpRevision AS BIGINT)

IF @SpVersion > 0 
	BEGIN
	IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
	BEGIN
		ALTER TABLE #tmpCommCellUsage DROP COLUMN SnapshotSize
		ALTER TABLE #tmpCommCellUsage DROP COLUMN ReplicationSize
		ALTER TABLE #tmpCommCellUsage DROP COLUMN InstanceName	
	END
	ELSE
	IF @SpVersion <= 10031000800050000 --10.0 SP4 and Below
	BEGIN
		ALTER TABLE #tmpCommCellUsage DROP COLUMN SnapshotSize
		ALTER TABLE #tmpCommCellUsage DROP COLUMN ReplicationSize
	END
END

DECLARE @SpNodeNo INT = 0
IF @relId > 15 AND ISNUMERIC(@SpRevision) = 0
BEGIN
	DECLARE	@SpNode VARCHAR(20)
	SET @SpNode = SUBSTRING(@SpRevision, 9, 2)

	IF ISNUMERIC(@SpNode) = 1
		SET @SpNodeNo = CAST(@SpNode AS INT)
END

IF @relId > 15 AND (@SpVersion >= 10078000200200000 OR @SpNodeNo >= 20) -- from v11 SP8
BEGIN
	INSERT INTO #tmpCommCellUsage
	EXEC QS_CommCellUsageReport 'ByLastDay', 0, 4
END
ELSE
BEGIN
	INSERT INTO #tmpCommCellUsage
	EXEC QS_CommCellUsageReport 'ByLastDay', 0, 1

	UPDATE #tmpCommCellUsage SET flag = 0
END

      
; WITH AgentLicType (ClientId, AppTypeId, LicType, RowNo) AS
(
	SELECT	CId, AppType, LicType, ROW_NUMBER() OVER (PARTITION BY CId, AppType ORDER BY OpTimeUTC DESC) AS RowNo
	FROM	LicUsage WITH(NOLOCK)
	WHERE	OpType = 'Install'
)
INSERT INTO @LicData
SELECT 
    0, U.AppTypeId, U.AppTypeName, ISNULL(T.licType, 0), 0, 0, 0, 0, 0, 0, 0,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END), 0, ISNULL(U.Flag, 0)
FROM #tmpCommCellUsage U
	LEFT OUTER JOIN AgentLicType T ON U.ClientId = T.ClientId AND U.AppTypeId = T.AppTypeId AND T.RowNo = 1
WHERE U.AppTypeId <> 38
GROUP BY U.AppTypeId, U.AppTypeName, ISNULL(T.LicType, 0), ISNULL(U.Flag, 0)

UPDATE	@LicData SET UsedAgents = U.UsedAgents
FROM	@LicData L 
	INNER JOIN (SELECT AppTypeId, COUNT(DISTINCT ClientId) AS UsedAgents FROM #tmpCommCellUsage GROUP BY AppTypeId) U 
		ON L.TypeId = U.appTypeId
WHERE	IsLicType = 0

INSERT INTO @LicData
SELECT 
    0, 38, (CASE C.simOperatingSystemId WHEN 118 THEN 'IBM-ISeries' WHEN 49 THEN 'OpenVMS' ELSE U.AppTypeName END), 1, 0, 0, 0, 0, 0, 0, 0,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END), 
    COUNT(DISTINCT ClientId), ISNULL(U.Flag, 0)
FROM #tmpCommCellUsage U
	INNER JOIN APP_Client C ON U.ClientId = C.id
WHERE U.AppTypeId = 38
GROUP BY (CASE C.simOperatingSystemId WHEN 118 THEN 'IBM-ISeries' WHEN 49 THEN 'OpenVMS' ELSE U.AppTypeName END), ISNULL(U.Flag, 0)

--Insert rows for ACV. There will be only 2 rows
; WITH AcvLicType (AppTypeName, LicType, BackupSize, ArchiveSize, ReplicationSize, SnapSize) AS
(
SELECT 
    T.t_appName , T.t_lictype,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END)
FROM #tmpCommCellUsage U
	JOIN (SELECT distinct t_jobid, t_appName, t_lictype FROM #tempClientList WHERE t_lictype = @AcvOnlyLicType AND t_jobid > 0) T ON U.jobID = T.t_jobid 
GROUP BY t_appName, t_lictype
UNION
SELECT 
    T.t_appName , T.t_lictype, 
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END)
FROM #tmpCommCellUsage U
	JOIN #tempClientList T ON U.ClientId = T.t_clientId AND t_lictype = @AcvOnlyLicType AND AppTypeId = t_appTypeId AND t_jobid = 0
GROUP BY t_appName, t_lictype
)

INSERT INTO @LicData
SELECT 0,0,AppTypeName,LicType, 0, 0, 0, 0, 0, 0, 0, SUM(BackupSize),SUM(ArchiveSize),SUM(ReplicationSize),SUM(SnapSize), 0, 0 FROM AcvLicType
GROUP BY AppTypeName,LicType

DELETE #tmpCommCellUsage FROM #tempClientList WHERE  t_lictype = @AcvOnlyLicType AND (jobID = t_jobid AND t_jobid > 0 OR (ClientId = t_clientId AND AppTypeId = t_appTypeId ))

-- Insert rows for VM Only
INSERT INTO @LicData
SELECT 
    0, 0, T.t_appName , T.t_lictype, 0, 0, 0, 0, 0, 0, 0,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END), 0, 0
FROM #tmpCommCellUsage U
	JOIN (SELECT distinct t_clientId, t_appName, t_lictype FROM #tempClientList WHERE t_lictype = @VMWithoutPkgLicType) T ON U.ClientId = T.t_clientId AND AppTypeId = 106
GROUP BY t_appName, t_lictype

DELETE #tmpCommCellUsage WHERE AppTypeId = 106

-- Insert rows for FS server only, application server only & laptop Desktop
INSERT INTO @LicData
SELECT 
    0, 0, T.t_appName , T.t_lictype, 0, 0, 0, 0, 0, 0, 0,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END), 0, 0
FROM #tmpCommCellUsage U
	JOIN #tempClientList T ON U.ClientId = T.t_clientId 
	WHERE t_lictype not in (@AcvOnlyLicType, @VMWithoutPkgLicType)
GROUP BY t_appName, t_lictype


INSERT INTO @CloudAppsCapacityUsage
SELECT 
    InstanceTypeId,
    SUM(CASE WHEN nJobType = 0 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 1 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 2 THEN UncompBytes ELSE 0 END), 
    SUM(CASE WHEN nJobType = 3 THEN UncompBytes ELSE 0 END),
    COUNT(DISTINCT A.ClientId)
FROM 
    #tmpCommCellUsage U
    INNER JOIN APP_Application A ON U.AppId = A.id
    INNER JOIN (
	SELECT	componentNameId, CAST(attrVal AS INT) AS InstanceTypeId
	FROM	APP_InstanceProp
	WHERE	attrName = 'Cloud Apps Instance Type' AND modified = 0
	) I ON A.instance = I.componentNameId
WHERE U.AppTypeId = 134
GROUP BY InstanceTypeId


SET @surveyXML = (
SELECT (SELECT 
    IsLicType AS '@IsLicType',
    TypeId AS '@Type',
    TypeName AS '@Name',
    LicType AS '@LicType',
    TotalPermLicensesIssued AS '@PermLicensesIssued',   
    TotalPermLicensesUsed AS '@PermLicensesUsed',
    FreePermLicensesAvailable AS '@PermLicensesAvailable',
    TotalEvalLicensesIssued AS '@EvalLicensesIssued', 
    TotalEvalLicensesUsed AS '@EvalLicensesUsed',
    FreeEvalLicensesAvailable AS '@EvalLicensesAvailable',
    CASE WHEN EvalLicenseExpiryDate > 0 THEN (SELECT dbo.UTCToLocalTime(dbo.GetDateTime(EvalLicenseExpiryDate), @csTimeZone)) ELSE '1970-01-01' END AS '@EvalLicenseExpiryDate',
    UsedBackupSize AS '@UsedBackupSize',
    UsedArchiveSize AS '@UsedArchiveSize',
    UsedSnapSize AS '@UsedSnapSize',
    UsedReplicationSize AS '@UsedReplicationSize',
    UsedAgents AS '@UsedAgents',
    Flag AS '@Flag'
	FROM @LicData
	ORDER BY IsLicType, TypeId
	FOR XML PATH('AgentCapacityLicenseUsage'),TYPE),

    (SELECT 
	InstanceTypeId AS '@InstanceTypeId',
	BackupSize AS '@BackupSize',
	ArchiveSize AS '@ArchiveSize',
	SnapshotSize AS '@SnapSize',
	ReplicationSize AS '@ReplicationSize',
	Agents AS '@Agents'
    FROM @CloudAppsCapacityUsage
    FOR XML PATH('CloudAppsCapacityUsage'), TYPE)		
FOR XML PATH('')
)


DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
    SELECT
        @queryId AS '@QueryId',
        @EndTime AS '@LogDate',
        (@EndTime - @LogDate) AS '@QueryRunningTime',
        @surveyXML
    FOR XML PATH('Rpt_CSSXMLDATA')
)



IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmpCommCellUsage'))
    DROP TABLE #tmpCommCellUsage
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempClientList'))
    DROP TABLE #tempClientList	
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tempVMsWithIdAppType'))
    DROP TABLE #tempVMsWithIdAppType		
    
SET NOCOUNT OFF