--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

--------- END - GENERATED CODE ---------


--------- BEGIN SURVEY QUERY ---------

--------- Insert your SQL statements here 
DECLARE @csReleaseId INT
SELECT	@csReleaseId = releaseId FROM APP_Client WITH(NOLOCK) WHERE id = 2

DECLARE @SQLString NVARCHAR(500)

IF OBJECT_ID('tempdb..#Result') IS NOT NULL DROP TABLE #Result
CREATE TABLE #Result (name NVARCHAR(255), value BIGINT, size BIGINT)

IF OBJECT_ID('tempdb..#InstanceType') IS NOT NULL DROP TABLE #InstanceType
CREATE TABLE #InstanceType (InstanceID INT,InstanceTypeID INT)

IF OBJECT_ID('tempdb..#VMClient') IS NOT NULL DROP TABLE #VMClient
CREATE TABLE #VMClient (clientId INT, name NVARCHAR(1024), appId INT, lastJobId INT)

INSERT	INTO #VMClient (clientId, name, appId, lastJobId)
SELECT	DISTINCT C.id, C.name, 0, J.lastJobId
FROM	APP_Client C WITH (NOLOCK)
		INNER JOIN (
		SELECT	Q.clientId, MAX(Q.jobId) AS lastJobId, 
				MAX(CASE WHEN Q.status IN (0, 3) AND B.dataStatus = 0 THEN Q.jobId ELSE 0 END) AS lastSuccJobId
		FROM	JMQinetixUpdateStatus Q WITH (NOLOCK)
				INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.jobId = Q.jobId AND B.commCellId = Q.commCellId
					AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND B.bkpLevel IN (1, 2, 4, 256, 1024, 32768)
				GROUP BY Q.clientId
		) J ON C.id = J.clientId
		INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON C.id = CP.componentNameId
		AND	CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified =0
		LEFT OUTER JOIN (
		SELECT	DISTINCT componentNameId
		FROM	APP_ClientProp WITH (NOLOCK)
		WHERE	attrName = 'Virtual Machine Deletion Time' AND ISNULL(attrVal, '0') <> '0' AND modified = 0
		) T ON C.id = T.componentNameId
WHERE	J.lastSuccJobId > 0 AND T.componentNameId IS NULL

UPDATE	V SET appId = B.appId
FROM	#VMClient V 
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.jobId = V.lastJobId AND B.appType = 106


--Kubernetes containers 
INSERT	INTO #VMClient (clientId, name, appId, lastJobId)
SELECT	DISTINCT C.id, C.name, A.id, J.jobid
FROM	APP_Client C WITH (NOLOCK)
		INNER JOIN APP_ClientProp CP WITH (NOLOCK) ON C.id = CP.componentNameId
		AND	CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified =0
		inner join app_clientprop cp2 with (nolock) on cp2.componentNameId = c.id
		and cp2.attrname ='Last Backup JobID'
		inner join JMBkpStats J
		on j.jobid = cp2.attrval
		inner join app_application A
		on J.appid = A.id
		inner join app_instanceprop I
		on I.componentNameId = A.instance
		and I.attrName ='Virtual Server Instance Type'
		and I.attrVal ='1501'

-- Check if VMs are currently protected by Virtual Server			
CREATE TABLE #VSASubclient (appId INT, active INT)

INSERT	INTO #VSASubclient
SELECT	DISTINCT appId, 1
FROM	#VMClient

-- Remove Deleted and deconfigured subclients
UPDATE S SET active = 0 
FROM	#VSASubclient S
		INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id AND A.subclientStatus&(2|4) > 0

-- Remove activity disabled subclients from the resultset
UPDATE S SET active = 0 
FROM	#VSASubclient S
		INNER JOIN JMJobAction J WITH (NOLOCK) ON S.appId = J.appId AND J.opType = 4 AND J.action = 1

UPDATE S SET active = 0 
FROM	#VSASubclient S
		INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id
		INNER JOIN APP_iDAName B WITH (NOLOCK) ON B.clientId = A.clientId AND B.appTypeId = A.appTypeId AND B.Status&6 > 0

DELETE	#VMClient
WHERE	appId IN (SELECT appId FROM #VSASubclient WHERE active = 0)

DROP TABLE #VSASubclient


INSERT	INTO #Result
SELECT	'Total VM Count', COUNT(DISTINCT name),  COUNT(DISTINCT name) 
FROM	#VMClient

INSERT	INTO #Result
SELECT	'HyperVisorGroup', I.attrVal, COUNT(DISTINCT V.clientId) 
FROM	#VMClient V
		INNER JOIN APP_Application A with (nolock) on A.id = V.appId
		INNER JOIN APP_InstanceProp I on I.componentNameId = A.instance
			AND I.attrName = 'Virtual Server Instance Type' AND I.modified =0
GROUP BY I.attrVal


INSERT INTO #Result
SELECT 'Total Snap VM Count', COUNT(DISTINCT V.name),  COUNT(DISTINCT V.name)
FROM #VMClient V
inner join APP_SubClientProp prop with (nolock)
on v.appId = prop.componentNameId
and prop.attrName ='Enable Snap Backups'
and prop.cs_attrName = CHECKSUM(N'Enable Snap Backups')
and prop.attrval ='1'
and prop.modified =0

INSERT INTO #Result
SELECT 'Total Indexing V2 VM Count', COUNT(DISTINCT V.name),  COUNT(DISTINCT V.name)
FROM #VMClient V
inner join APP_ClientProp prop with (nolock)
on v.clientid = prop.componentNameId
and prop.attrName ='IndexingV2_VSA'
and prop.attrval ='1'
and prop.modified =0

INSERT	INTO #Result
SELECT	'HyperVisorGroupV2', I.attrVal, COUNT(DISTINCT V.clientId) 
FROM	#VMClient V
		INNER JOIN APP_Application A with (nolock) on A.id = V.appId
		INNER JOIN APP_InstanceProp I on I.componentNameId = A.instance
			AND I.attrName = 'Virtual Server Instance Type' AND I.modified =0
		inner join APP_ClientProp prop with (nolock)
		on v.clientid = prop.componentNameId
		and prop.attrName ='IndexingV2_VSA'
		and prop.attrval ='1'
		and prop.modified =0
GROUP BY I.attrVal

DECLARE @SpVersion BIGINT = 0

SELECT  @SpVersion = CAST(revision AS BIGINT) FROM GxDBVersions WITH(NOLOCK) where name = 'QS_CommCellUsageReport' AND ISNUMERIC(revision) = 1
IF OBJECT_ID('tempdb.dbo.#CapacityUsage') is not null drop table #CapacityUsage
CREATE TABLE #CapacityUsage (
    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, bEnterprise INT
)

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

IF OBJECT_ID('RptCapacityUsage', 'U') IS NOT NULL
BEGIN
	SET @SQLString = N'
		DECLARE @fromDT DATETIME = (SELECT MAX(startDT) FROM RptCapacityUsage WITH (NOLOCK) WHERE type = 1)

		INSERT	INTO #CapacityUsage (nJobType, appId, ClientId, AppTypeId, UncompBytes)
		SELECT	U.jobType, U.appId, A.clientId, A.appTypeId, U.frontendSize
		FROM	RptCapacityUsage U WITH (NOLOCK)
				INNER JOIN APP_Application A WITH (NOLOCK) ON U.appId = A.id AND A.appTypeId <> 106
		WHERE	U.startDT = @fromDT AND U.type = 1'
			
	EXEC sp_executesql @SQLString
END

IF NOT EXISTS (SELECT * FROM #CapacityUsage)
BEGIN
	INSERT INTO #CapacityUsage
	EXEC QS_CommCellUsageReport '', 0, 1
END

--Installed Total Size Count
INSERT INTO #Result
select	LTRIM(atg.osGroup + ' ' + CASE WHEN atg.App <>'' THEN atg.App ELSE atg.appGroup END ) as aggroup,
		count(distinct cu.ClientId),
		sum(cu.UncompBytes) 
from	#CapacityUsage cu
		INNER JOIN App_AppTypeGroups atg WITH (NOLOCK) ON atg.appTypeId = cu.AppTypeId AND cu.AppTypeId != 106 -- IGNORE Virtual Server
group by  atg.osGroup, atg.App, atg.appGroup


--IBM-ISeries Agent Count.
DECLARE @uncompSize BIGINT = 0
DECLARE @iCount BIGINT = 0
SELECT @iCount = Count (DISTINCT c.id) from APP_Client c WITH (NOLOCK) 
		INNER JOIN  APP_Application  a WITH (NOLOCK) ON c.id = a.clientId 
where appTypeId = 38 and simOperatingSystemId = 118 AND(a.subclientStatus & (2|4)) = 0 

SELECT @uncompSize = ISNULL(SUM(UncompBytes),0) FROM #CapacityUsage  
WHERE ClientId IN (SELECT DISTINCT (c.id) from APP_Client c WITH (NOLOCK) INNER JOIN  APP_Application a WITH (NOLOCK) ON c.id = a.clientId where appTypeId = 38 and simOperatingSystemId = 118 AND  (a.subclientStatus & (2|4)) = 0) -- Get the IBM ISeries agent total backup size.
INSERT INTO #Result (name,value,size) VALUES ('IBM-ISeries',@iCount,@uncompSize)

--OpenVMS agent count.
SELECT @iCount = Count (DISTINCT c.id) 
from APP_Client c WITH (NOLOCK) 
	INNER JOIN  APP_Application a WITH (NOLOCK) ON c.id = a.clientId 
where appTypeId = 38 and simOperatingSystemId = 49 AND(a.subclientStatus & (2|4)) = 0
SET @uncompSize  = 0
SELECT @uncompSize = ISNULL(SUM(UncompBytes),0) FROM #CapacityUsage  
WHERE ClientId IN (SELECT DISTINCT (c.id) from APP_Client c WITH (NOLOCK) INNER JOIN  APP_Application a WITH (NOLOCK) ON c.id = a.clientId where appTypeId = 38 and simOperatingSystemId = 49 AND(a.subclientStatus & (2|4)) = 0)  -- Get the Open VMS agent total backup size.
INSERT INTO #Result (name,value,size) VALUES ('OpenVMS',@iCount,@uncompSize)

-- file server app type
INSERT INTO #Result
select	'FileServersAppTypeSize',
		prop.attrval, 
		sum(cu.UncompBytes) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId IN (29, 33)
inner join App_ClientProp prop
on prop.componentNameId = app.clientId
and prop.attrName = 'Client App Type'
and prop.modified = 0
group by prop.attrVal


INSERT INTO #Result
select	'FileServersAppTypeClients',
		prop.attrval, 
		 Count (DISTINCT cu.ClientId) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId IN (29, 33)
inner join App_ClientProp prop
on prop.componentNameId = app.clientId
and prop.attrName = 'Client App Type'
and prop.modified = 0
group by prop.attrVal

--distribute app type

INSERT INTO #Result
select	'DistributeAppTypeSize',
		prop.attrval, 
		sum(cu.UncompBytes) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 64
inner join APP_InstanceProp prop
on prop.componentNameId = app.instance
and prop.attrName = 'Distributed Cluster Type'
group by prop.attrVal


INSERT INTO #Result
select	'DistributeAppTypeClients',
		prop.attrval, 
		 Count (DISTINCT cu.ClientId) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 64
inner join APP_InstanceProp prop with (nolock)
on prop.componentNameId = app.instance
and prop.attrName = 'Distributed Cluster Type'
group by prop.attrVal

--cloud app type
INSERT INTO #Result
select	'CloudAppTypeSize',
		prop.attrval, 
		sum(cu.UncompBytes) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 134
inner join APP_InstanceProp prop
on prop.componentNameId = app.instance
and prop.attrName = 'Cloud Apps Instance Type'
group by prop.attrVal


INSERT INTO #Result
select	'CloudAppTypeClients',
		prop.attrval, 
		 Count (DISTINCT cu.ClientId) 
from	#CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 134
inner join APP_InstanceProp prop with (nolock)
on prop.componentNameId = app.instance
and prop.attrName = 'Cloud Apps Instance Type'
group by prop.attrVal
 
INSERT INTO #Result
select  'HBaseAppTypeSize',
        prop.attrval, 
        sum(cu.UncompBytes) 
from    #CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 64
inner join APP_BackupSetProp prop WITH (NOLOCK)
on prop.componentNameId = app.backupSet
and prop.attrName = 'Hadoop App Type'
and prop.attrVal = '1'
group by prop.attrVal

INSERT INTO #Result
select  'HBaseAppTypeClients',
        prop.attrval, 
         Count (DISTINCT cu.ClientId) 
from    #CapacityUsage cu
inner join app_application app with (nolock)
on cu.appId = app.id 
and app.appTypeId = 64
inner join APP_BackupSetProp prop WITH (NOLOCK)
on prop.componentNameId = app.backupSet
and prop.attrName = 'Hadoop App Type'
and prop.attrVal = '1'
group by prop.attrVal

INSERT INTO #Result
SELECT 'MSSQLCloudAppsTypeClients',
		CASE WHEN prop.attrval = N'Aws DataBase Engine' THEN 1
			WHEN prop.attrVal = N'Azure DataBase Engine' THEN 2
			WHEN prop.attrVal = N'Azure Managed DataBase Engine' THEN 3
		END,
		Count (DISTINCT cu.ClientId)
FROM #CapacityUsage cu
INNER JOIN APP_Application app WITH (NOLOCK)
ON cu.appId = app.id AND app.appTypeId = 81
INNER JOIN APP_InstanceProp prop WITH (NOLOCK)
ON prop.componentNameId = app.instance 
AND prop.attrName = 'MSSQL Server Type' 
AND prop.attrVal IN (N'Aws DataBase Engine', N'Azure DataBase Engine', N'Azure Managed DataBase Engine') and prop.modified = 0 
group by prop.attrVal

INSERT INTO #Result
SELECT  'MySQLCloudAppTypesSize',
        paas.cloudType, 
        sum(cu.UncompBytes) 
FROM    #CapacityUsage cu
INNER JOIN (
SELECT componentNameId as clientId, [Client App Type] as clientAppType, [Cloud Hypervisor Type] as cloudType, appTypeId, id from
(
	SELECT cl.attrName, cl.attrVal, cl.componentNameId, app.appTypeId, app.id FROM APP_Application app WITH (NOLOCK)
	INNER JOIN APP_ClientProp cl WITH (NOLOCK) ON app.clientId = cl.componentNameId
	WHERE cl.attrName IN ('Client App Type', 'Cloud Hypervisor Type') and cl.modified = 0 and cl.attrVal IS NOT NULL and app.appTypeId = 104
) t
PIVOT (max(attrVal) for attrName in ( [Client App Type], [Cloud Hypervisor Type])) as p
) paas ON cu.appId = paas.id AND paas.cloudType IS NOT NULL AND paas.clientAppType = '1'
GROUP BY paas.appTypeId, paas.cloudType


INSERT INTO #Result
SELECT  'MySQLCloudAppTypesClients',
        paas.cloudType, 
         Count (DISTINCT cu.ClientId) 
FROM    #CapacityUsage cu
INNER JOIN (
SELECT componentNameId as clientId, [Client App Type] as clientAppType, [Cloud Hypervisor Type] as cloudType, appTypeId, id from
(
	SELECT cl.attrName, cl.attrVal, cl.componentNameId, app.appTypeId, app.id FROM APP_Application app WITH (NOLOCK)
	INNER JOIN APP_ClientProp cl WITH (NOLOCK) ON app.clientId = cl.componentNameId
	WHERE cl.attrName IN ('Client App Type', 'Cloud Hypervisor Type') and cl.modified = 0 and cl.attrVal IS NOT NULL and app.appTypeId = 104
) t
PIVOT (max(attrVal) for attrName in ( [Client App Type], [Cloud Hypervisor Type])) as p
) paas ON cu.appId = paas.id AND paas.cloudType IS NOT NULL AND paas.clientAppType = '1'
GROUP BY paas.appTypeId, paas.cloudType

INSERT INTO #Result
SELECT  'PostgreSQLCloudAppTypesSize',
        paas.cloudType, 
        sum(cu.UncompBytes) 
FROM    #CapacityUsage cu
INNER JOIN (
SELECT componentNameId as clientId, [Client App Type] as clientAppType, [Cloud Hypervisor Type] as cloudType, appTypeId, id from
(
	SELECT cl.attrName, cl.attrVal, cl.componentNameId, app.appTypeId, app.id FROM APP_Application app WITH (NOLOCK)
	INNER JOIN APP_ClientProp cl WITH (NOLOCK) ON app.clientId = cl.componentNameId
	WHERE cl.attrName IN ('Client App Type', 'Cloud Hypervisor Type') and cl.modified = 0 and cl.attrVal IS NOT NULL and app.appTypeId = 125
) t
PIVOT (max(attrVal) for attrName in ( [Client App Type], [Cloud Hypervisor Type])) as p
) paas ON cu.appId = paas.id AND paas.cloudType IS NOT NULL AND paas.clientAppType = '1'
GROUP BY paas.appTypeId, paas.cloudType


INSERT INTO #Result
SELECT  'PostgreSQLCloudAppTypesClients',
        paas.cloudType, 
         Count (DISTINCT cu.ClientId) 
FROM    #CapacityUsage cu
INNER JOIN (
SELECT componentNameId as clientId, [Client App Type] as clientAppType, [Cloud Hypervisor Type] as cloudType, appTypeId, id from
(
	SELECT cl.attrName, cl.attrVal, cl.componentNameId, app.appTypeId, app.id FROM APP_Application app WITH (NOLOCK)
	INNER JOIN APP_ClientProp cl WITH (NOLOCK) ON app.clientId = cl.componentNameId
	WHERE cl.attrName IN ('Client App Type', 'Cloud Hypervisor Type') and cl.modified = 0 and cl.attrVal IS NOT NULL and app.appTypeId = 125
) t
PIVOT (max(attrVal) for attrName in ( [Client App Type], [Cloud Hypervisor Type])) as p
) paas ON cu.appId = paas.id AND paas.cloudType IS NOT NULL AND paas.clientAppType = '1'
GROUP BY paas.appTypeId, paas.cloudType

INSERT INTO #Result
SELECT  'MSSQLCloudAppsTypeSize',
        CASE WHEN prop.attrval = N'Aws DataBase Engine' THEN 1
			WHEN prop.attrVal = N'Azure DataBase Engine' THEN 2
			WHEN prop.attrVal = N'Azure Managed DataBase Engine' THEN 3
		END, 
        sum(cu.UncompBytes) 
FROM    #CapacityUsage cu
INNER JOIN APP_Application app WITH (NOLOCK)
ON cu.appId = app.id 
AND app.appTypeId = 81
INNER JOIN APP_InstanceProp prop WITH (NOLOCK)
ON prop.componentNameId = app.instance
AND prop.attrName = 'MSSQL Server Type'
AND prop.attrVal IN (N'Aws DataBase Engine', N'Azure DataBase Engine', N'Azure Managed DataBase Engine') and prop.modified = 0 
GROUP BY prop.attrVal


---collect cloud APP detail Instance Type information------

INSERT INTO #InstanceType
SELECT	P.componentNameId, CAST(P.attrVal AS INT)
FROM	APP_InstanceProp P
		INNER JOIN (SELECT DISTINCT instance FROM APP_Application WHERE appTypeId = 106) A 
		ON P.componentNameId = A.instance
WHERE	P.attrName IN ('Virtual Server Instance Type') 
	AND P.modified = 0 AND ISNUMERIC(P.attrVal) = 1
UNION
SELECT	P.componentNameId, CAST(P.attrVal AS INT)
FROM	APP_InstanceProp P
		INNER JOIN (SELECT DISTINCT instance FROM APP_Application WHERE appTypeId = 134) A 
		ON P.componentNameId = A.instance
WHERE	P.attrName IN ('Cloud Apps Instance Type') 
	AND P.modified = 0 AND ISNUMERIC(P.attrVal) = 1

DROP TABLE #CapacityUsage
DROP TABLE #VMClient
----------------------------------

SET @surveyXML =(SELECT 
	(SELECT	name AS '@name', 
			value AS '@value',
			size AS '@size'
	FROM	 #Result
	FOR XML PATH ('VMWithAgentInstalled'),TYPE),
	(SELECT DISTINCT InstanceID AS '@InstanceID',
	        InstanceTypeID AS '@InstanceTypeID' 
	FROM #InstanceType 
	FOR XML PATH ('CloudAppInstanceType'),TYPE)
	FOR XML PATH ('VMCloudCollection')
	)
DROP TABLE #Result
 
------- END SURVEY QUERY ---------

------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

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

--SELECT cast (@surveyXML as XML)