--- Please follow the below comments to insert SQL statements.
-- Client version query
--------- 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 = 145
DECLARE @surveyXML NVARCHAR(MAX)

--------- BEGIN SURVEY QUERY ---------
DECLARE	@csReleaseId INT = 0
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

DECLARE @csSPVersion INT = 0
IF @csReleaseId = 15
	SELECT	@csSPVersion = ISNULL(MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END), 0) FROM simInstalledPackages WHERE ClientId = 2

IF @csReleaseId > 15 OR @csReleaseId = 15 AND @csSPVersion > 6
BEGIN
	RETURN
END

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

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

CREATE TABLE #BackupDisabledTemp (appId INT)

INSERT INTO #BackupDisabledTemp
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.clientId = A.ClientId AND J.appType IN (A.AppTypeId, 0)
UNION
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.appId = A.id
UNION
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN (
		SELECT	componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) T ON A.id = T.appId 
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND J.appId = T.subclientPolicyAppId
UNION
SELECT	DISTINCT A.id
FROM	APP_Application A
	INNER JOIN APP_ClientGroupAssoc CGA ON A.clientId = CGA.clientId
	INNER JOIN JMJobAction J
		ON J.opType = 4 AND J.action = 1
		AND CGA.clientGroupId = J.clientGroupId
		AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1

CREATE TABLE #RestoreDisabledTemp (appId INT)

INSERT INTO #RestoreDisabledTemp
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --Restore Activity
	    AND J.clientId = A.ClientId AND J.appType IN (A.AppTypeId, 0)
UNION
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --Restore Activity
	    AND J.appId = A.id
UNION
SELECT  DISTINCT A.id
FROM    APP_Application A
	INNER JOIN APP_ClientGroupAssoc CGA ON A.clientId = CGA.clientId
	INNER JOIN JMJobAction J
	    ON J.opType = 5 AND J.action = 1 --restore activity 
	    AND CGA.clientGroupId = J.clientGroupId
	    AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1

CREATE TABLE #Archive1Pass (appId INT, flags INT)

INSERT	INTO #Archive1Pass
SELECT	id, 1
FROM	APP_Application WITH(NOLOCK)
WHERE	appTypeId IN (SELECT componentNameId FROM APP_IDAProp WITH(NOLOCK) WHERE attrName = 'Enable Archive' AND attrVal = '1' AND modified = 0)

INSERT	INTO #Archive1Pass
SELECT	tur.componentNameId, 2
FROM	APP_SubClientProp tur WITH(NOLOCK) INNER JOIN APP_SubClientProp job WITH(NOLOCK) 
	ON tur.componentNameId = job.componentNameId
		AND tur.attrName = 'Is Turbo Subclient' AND tur.cs_attrName = CHECKSUM(N'Is Turbo Subclient') AND tur.attrVal = '1' AND tur.modified = 0
		AND job.attrName = 'First Turbo Job Id' AND job.cs_attrName = CHECKSUM(N'First Turbo Job Id') AND job.attrval != '0' AND job.modified = 0

CREATE TABLE #SubclientPolicyAssoc (appId INT, subclientPolicyAppId INT)
INSERT	INTO #SubclientPolicyAssoc
SELECT	componentNameId, CAST(attrVal AS INT)
FROM	(SELECT	componentNameId, attrVal, ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS RowId
	FROM	APP_SubClientProp SCP 
	WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) S
WHERE	S.RowId = 1


CREATE TABLE #DataReaderInfo (appId INT, ReaderInfo  NVARCHAR(100))
INSERT	INTO #DataReaderInfo
SELECT	DISTINCT ComponentNameID,CASE WHEN Mdr.MultipleDR is NULL THEN  attrval ELSE attrval +' (M)' END 
FROM app_subclientprop a OUTER APPLY 
  (SELECT 1 MultipleDR FROM app_subclientprop b
   WHERE a.componentNameId =b.componentNameId  and b.modified =0 
     AND Attrname='sys:UseMultipleStreamsWithinDrive' AND cs_attrName=CHECKSUM(N'sys:UseMultipleStreamsWithinDrive')) MDR
WHERE Attrname='use SCP filters' and cs_attrName=CHECKSUM(N'use SCP filters') and Modified=0 

CREATE TABLE #Encryption (appId INT, EncryptionInfo  NVARCHAR(100))
INSERT	INTO #Encryption
SELECT	S.componentNameId, 
		CASE WHEN attrval = 0 THEN 'None' WHEN Attrval = 1 THEN 'Media Only'
		     WHEN Attrval = 2 THEN 'Network and Media' WHEN Attrval = 3 THEN 'Network Only'
		END 
FROM	(SELECT	id, componentNameId, attrVal, 
				ROW_NUMBER() OVER (PARTITION BY componentNameId ORDER BY id DESC) AS rowId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Encrypt: encryption' AND cs_attrName = CHECKSUM(N'Encrypt: encryption') AND Modified = 0) S
WHERE	S.rowId = 1

CREATE TABLE #AllowGlobalFilter (appId INT,GlobalFilter INT)
INSERT	INTO #AllowGlobalFilter
SELECT DISTINCT	ComponentNameID,1
FROM app_subclientprop  
WHERE Attrname= 'use cs setting for global filters' and cs_attrname = CHECKSUM(N'use cs setting for global filters') and Modified=0 and Attrval='1'

CREATE TABLE #SpecialSubclientTypeFlags (appId INT, flags INT)
INSERT	INTO #SpecialSubclientTypeFlags
SELECT	componentNameId, 
		SUM(CASE WHEN attrName = 'System State on Full Backup Only' THEN 1 WHEN attrName = 'DDB Backup' THEN 2
				 WHEN attrName = 'Index SubClient' THEN 4 WHEN attrName = 'SILO Copy ID' THEN 8 END)
FROM	APP_subclientProp WITH (NOLOCK) 
WHERE	attrName IN ('System State on Full Backup Only', 'DDB Backup', 'Index SubClient', 'SILO Copy ID') 
	AND cs_attrName IN (CHECKSUM(N'System State on Full Backup Only'), CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID')) 
	AND attrVal = '1' AND modified = 0
GROUP BY componentNameId

SET @surveyXML = (SELECT 
	(SELECT
		CL.id AS '@clientId',
		dbo.NormalizeForXML(CL.name) AS '@clientName',
		CASE 
			WHEN CL.id = ISNULL(rac.ClusterClientId, 0 ) THEN dbo.NormalizeForXML(rac.PhysicalClientName)
			WHEN CL.id = ISNULL(dag.ClusterClientId, 0 ) THEN dbo.NormalizeForXML(dag.PhysicalClientName)
			ELSE (SELECT dbo.NormalizeForXML(name) FROM APP_Client WHERE ID = AC.attrVal) 
		END AS '@physicalClientName',		
		IDA.type AS '@appTypeId',
		IDA.name AS '@agentName',
		I.id AS '@instanceId',
		dbo.FixInstanceName(I.name, A.appTypeId) AS '@instanceName',
		BS.id AS '@backupsetId',
		dbo.NormalizeForXML(BS.name) AS '@backupsetName',
		A.id AS '@appId',
		dbo.NormalizeForXML(A.subclientName) AS '@subclientName',
		A.subclientStatus AS '@subclientStatus',
		dbo.UTCToLocalStringTime(dbo.GetDateTime(A.refTime), @timzonename) AS '@creationTime',
		CASE WHEN A.subclientStatus&(2|4) > 0 THEN dbo.UTCToLocalStringTime(dbo.GetDateTime(A.modified), @timzonename) ELSE NULL END AS '@deconfiguredTime',
		dataArchGrpId '@dataPolicyId', 
		logArchGrpId '@logPolicyId', 
		ISNULL(SCP.subclientPolicyAppId, 0) AS '@subclientPolicyAppId',
		CASE WHEN D.appId IS NOT NULL THEN 1 ELSE 0 END AS '@backupDisabled',
		ISNULL(FF.filterFlags, 0) AS '@filterFlags',
		ISNULL(AP.flags, 0) AS '@archive1PassFlags',
		CASE WHEN R.appId IS NOT NULL THEN 1 ELSE 0 END AS '@restoredisabled',
		ISNULL(Dri.ReaderInfo,'') AS '@readerInfo',
		ISNULL(Enc.EncryptionInfo,'') AS '@encryptionInfo',
		ISNULL(GF.GlobalFilter,0) AS '@globalfilter',
		ISNULL(S.flags,0) as '@systemstateval'
	FROM 
		App_Application A
		INNER JOIN APP_Client CL ON A.clientId = CL.id
		INNER JOIN APP_iDAType IDA ON A.appTypeId = IDA.type
		INNER JOIN APP_InstanceName I ON A.instance = I.id
		INNER JOIN APP_BackupSetName BS ON A.backupSet = BS.id
		LEFT OUTER JOIN #SubclientPolicyAssoc SCP ON SCP.appId = A.id
		LEFT OUTER JOIN APP_VMToPMMap AVM ON AVM.PMClientId = CL.id AND AVM.VMClientId = 2 -- To remove physical machines on the cluster CS as client detials (script 77) is excluding the same in getclientlistAndInitialProps.sp. We have to keep the number of cleints and subclients same in both scripts 
		LEFT OUTER JOIN #BackupDisabledTemp D ON A.id = D.appId
		LEFT OUTER JOIN #RestoreDisabledTemp R ON A.id = R.appId
		LEFT JOIN APP_ClientProp AC ON CL.Id = AC.componentNameId AND AC.attrName = 'Active Physical Node' AND AC.attrVal <> CL.id AND AC.modified = 0
		LEFT OUTER JOIN
		(
			SELECT c.id AS ClusterClientId, app.id AS scId, c1.id AS PhysicalClientId, c1.name AS PhysicalClientName
			FROM APP_Client AS c WITH (NOLOCK)
				INNER JOIN APP_Application AS app WITH (NOLOCK)
					ON c.simOperatingSystemId = 56 --Oracle RAC
					AND c.id = app.clientId
				INNER JOIN (SELECT instaceId, MIN(clientId) AS clientId FROM APP_OracleRacInstance AS ori WITH (NOLOCK) GROUP BY instaceId) AS i
					ON app.instance = i.instaceId 
				INNER JOIN APP_Client AS c1 WITH (NOLOCK)
					ON c1.id = i.clientId
		) AS rac
			ON rac.ClusterClientId = CL.id 
			AND rac.scId = A.id
		LEFT OUTER JOIN
		(
			SELECT cp.componentNameId AS ClusterClientId, MIN(cp.attrName) AS PhysicalClientName
			FROM APP_ClientProp AS cp WITH (NOLOCK)
					WHERE cp.attrType = 125 --PROPERTY_DAG_MEMBERSERVER for Exchange DAG
					AND cp.modified = 0
			GROUP BY cp.componentNameId		
		) AS dag
			ON 	dag.ClusterClientId = CL.id			
		LEFT OUTER JOIN
		(
			SELECT componentNameId, 
				SUM(CASE WHEN attrName = 'use global filters' THEN 1 
					WHEN attrName = 'use cs setting for global filters' THEN 2 
					WHEN attrName = 'use SCP filters' THEN 4 ELSE 0 END) AS filterFlags
			FROM APP_SubClientProp 
			WHERE attrName IN ('use global filters', 'use cs setting for global filters', 'use SCP filters')
				AND cs_attrName IN (CHECKSUM(N'use global filters'), CHECKSUM(N'use cs setting for global filters'), CHECKSUM(N'use SCP filters'))
				AND attrVal = '1' AND modified = 0
			GROUP BY componentNameId
		) AS FF ON FF.componentNameId = A.id
		LEFT OUTER JOIN
		(
			SELECT	appId, SUM(flags) AS flags
			FROM	#Archive1Pass
			GROUP BY appId
		) AS AP ON AP.appId = A.id
		LEFT OUTER JOIN #DataReaderInfo DRI ON DRI.appId = A.id  
		LEFT OUTER JOIN #Encryption Enc ON Enc.appId = A.id 
		LEFT OUTER JOIN #AllowGlobalFilter GF ON Gf.appId = A.id 
		LEFT OUTER JOIN #SpecialSubclientTypeFlags S ON S.appId = A.ID 		
	WHERE 
		(A.appTypeId < 600 OR A.appTypeId = 1000)
		AND A.appTypeId NOT IN (72, 85, 127, 84, 107, 121, 122)
		AND A.subclientStatus&(16) = 0		
		AND AVM.PMClientId IS NULL
	FOR XML PATH('Subclient'), TYPE),
	
	(SELECT
		CL.id AS '@clientId',
		CL.name AS '@clientName',
		106 AS '@appTypeId',
		'Virtual Server' AS '@agentName'
	FROM 
		APP_Client CL
		INNER JOIN APP_ClientProp CP ON CP.componentNameId = CL.id 
			AND CP.attrName = 'Virtual Server Discovered Clients' AND CP.attrVal = '1' AND CP.modified = 0
	FOR XML PATH('Subclient'), TYPE)

FOR XML PATH ('')
-- Replace <<RESULTS_NAME>> with specific name
)

DROP TABLE #SubclientPolicyAssoc 
DROP TABLE #Archive1Pass
DROP TABLE #BackupDisabledTemp
DROP TABLE #RestoreDisabledTemp
--------- 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')
)
SET NOCOUNT OFF

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