--------- 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 = 4
DECLARE @surveyXML NVARCHAR(MAX)

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


--------- BEGIN SURVEY QUERY ---------
DECLARE @csReleaseId int
SELECT	@csReleaseId = releaseId FROM APP_Client WITH (NOLOCK) WHERE id = 2

DECLARE @csHighestSP INT
SELECT 	@csHighestSP = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2

    DECLARE     @tSnapFSAppTypes TABLE (AppTypeId   INTEGER)
    INSERT      INTO @tSnapFSAppTypes VALUES (13 /*CV_APPTYPE_NAS_FS*/)
    INSERT      INTO @tSnapFSAppTypes VALUES (17 /*CV_APPTYPE_HPUX32_FS*/)
    INSERT      INTO @tSnapFSAppTypes VALUES (19 /*CV_APPTYPE_SOLARIS32_FS*/)
    INSERT      INTO @tSnapFSAppTypes VALUES (21 /*CV_APPTYPE_AIX_FS */)
    INSERT      INTO @tSnapFSAppTypes VALUES (29 /*CV_APPTYPE_LINUX_FS*/)
    INSERT      INTO @tSnapFSAppTypes VALUES (33 /*CV_APPTYPE_WINDOWS_FILESYSTEM*/)

    IF object_id('tempdb.dbo.#SnapSubclient') IS NOT null DROP TABLE #SnapSubclient
	CREATE TABLE #SnapSubclient (appId INT, clientId INT, appTypeId INT, instanceId INT, archGrpId INT, primarySnapCopyId INT)

    IF object_id('tempdb.dbo.#jmbkpSnapJobsSurvey') IS NOT null DROP TABLE #jmbkpSnapJobsSurvey
    CREATE TABLE #jmbkpSnapJobsSurvey ( 
            AppId                   INTEGER, 
            JobId                   INTEGER,
            CommcellId              INTEGER,
            CopyId                  INTEGER,
            AppType                 INTEGER,
            totalUnCompBytes		BIGINT,
            jobStartTime            INTEGER,
            isPrimary               INTEGER
    )

	INSERT	INTO #SnapSubclient
	SELECT	A.id, A.clientId, A.appTypeId, A.instance, AG.id, AG.defaultSnapCopy
	FROM	APP_Application A WITH (NOLOCK)
			INNER JOIN archGroup AG WITH (NOLOCK) ON A.dataArchGrpID = AG.id AND AG.defaultSnapCopy > 1
	WHERE	A.subclientStatus&(2|4) = 0

	DELETE	S
	FROM	#SnapSubclient S INNER JOIN JMJobAction J WITH (NOLOCK)
		ON	J.opType IN (4, 76) AND J.action = 1
		AND J.clientId = S.ClientId AND J.appType IN (S.AppTypeId, 0)

	-- instanceID column was added to JMJobAction table in V11 SP6
	IF @csReleaseId = 16 AND @csHighestSP >= 6 OR @csReleaseId > 16
	EXEC sp_executesql N'DELETE S FROM #SnapSubclient S INNER JOIN JMJobAction J WITH (NOLOCK) ON J.opType IN (4, 76) AND J.action = 1 AND J.instanceID = S.InstanceId AND J.instanceID > 1'

	DELETE	S
	FROM	#SnapSubclient S INNER JOIN JMJobAction J WITH (NOLOCK)
		ON	J.opType IN (4, 76) AND J.action = 1
		AND J.appId = S.AppId

	DELETE	S
	FROM	#SnapSubclient S 
		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 S.appId = T.appId 
		INNER JOIN JMJobAction J WITH (NOLOCK)
		ON	J.opType IN (4, 76) AND J.action = 1
		AND J.appId = T.subclientPolicyAppId

	DELETE	S
	FROM	#SnapSubclient S
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId
			INNER JOIN JMJobAction J WITH (NOLOCK) ON CGA.clientGroupId = J.clientGroupId
				AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
				AND J.opType IN (4, 76) AND J.action = 1

    INSERT  INTO #jmbkpSnapJobsSurvey    
    SELECT  S.appId, JDS.jobId, jds.commCellId, JDS.archGrpCopyId, JBKP.appType, JBKP.totalUncompBytes, JBKP.servStartDate,
            CASE WHEN AGC.id = S.primarySnapCopyId THEN 1 ELSE 0 END
    FROM    JMBkpStats (NOLOCK) JBKP
            INNER JOIN #SnapSubclient S
				ON JBKP.appId = S.appId 
            INNER JOIN JMJobDataStats (NOLOCK) JDS 
                ON JDS.jobId = JBKP.jobId 
                AND JDS.commCellId = JBKP.commCellId
                AND JBKP.bkpLevel IN (1, 64, 128, 16384, 1024, 32768)
                AND JBKP.opType IN (59, 65)
            INNER JOIN archGroupCopy (NOLOCK) AGC 
				ON JDS.archGrpCopyId = AGC.id 
				AND AGC.isSnapCopy = 1
				AND AGC.archGroupId = S.archGrpId
    GROUP BY S.appId, JBKP.appType, jds.jobId, jds.commCellId, jds.archGrpCopyId, JBKP.appType, jbkp.servStartDate, jbkp.totalUncompBytes, AGC.id, S.primarySnapCopyId
    HAVING MAX(jds.status) = 100 AND MIN(jds.status) = 100 AND MAX(disabled & 256) = 0

    DECLARE     @appJobSnapSize  TABLE (
                CopyId				INTEGER,
                AppId		        INTEGER,
                JobId				INTEGER,
                CommCellId			INTEGER,
                IsReplication		INTEGER,                
                SnapSize			BIGINT,
                SnapCount			INTEGER,
                AppTypeId			INTEGER,
                AppTypeName			NVARCHAR(256),
                SnapEngineId			INTEGER,
                SnapEngineName			NVARCHAR(256),
                OriginalSnapEngineId		INTEGER,
                OriginalSnapEngineName		NVARCHAR(256)
            )

    INSERT INTO @appJobSnapSize
    SELECT  CopyId, AppId, JobID, CommCellId, 0, totalUnCompBytes, 0, AppType, '', 0, '', 0, ''
    FROM 
    (
        SELECT  CopyId, AppId, JobID, CommCellId, totalUnCompBytes, ROW_NUMBER() OVER(partition by AppId order by jobStartTime desc) as rn, AppType
        FROM    #jmbkpSnapJobsSurvey 
        WHERE   isPrimary = 1
    ) T 
    WHERE rn = 1

    INSERT INTO @appJobSnapSize
    SELECT  CopyId, AppId, JobID, CommCellId, 1, totalUnCompBytes, 0, AppType, '', 0, '', 0, ''
    FROM 
    (
        SELECT	CopyId, AppId, JobID, CommCellId, totalUnCompBytes, ROW_NUMBER() OVER(partition by AppId order by jobStartTime desc) as rn, AppType
        FROM    #jmbkpSnapJobsSurvey 
        WHERE   isPrimary = 0
    ) T 
    WHERE rn = 1

    -- Snap Size for FS subclients
    UPDATE  @appJobSnapSize
    SET     SnapSize = T_SUM.size
    FROM    @appJobSnapSize AJS
    INNER JOIN 
            (   SELECT  VOL.AppId, VOL.CopyId, VOL.JobId,
                        SUM(CONVERT(BIGINT, SUBSTRING(CONVERT(NVARCHAR(128), META.MetaData), (CHARINDEX('|', CONVERT(NVARCHAR(128), META.MetaData))+1), 32))) AS size
				FROM	@appJobSnapSize AJS 
						INNER JOIN SMVolume (NOLOCK) VOL 
							ON VOL.JobId = AJS.JobId AND VOL.CommCellId = AJS.CommCellId AND VOL.CopyId = AJS.CopyId 
							AND VOL.AppId = AJS.AppId
						INNER JOIN @tSnapFSAppTypes FSAT ON FSAT.AppTypeId = VOL.AppTypeId
						INNER JOIN archFileCopy (NOLOCK) AFC 
							ON VOL.ArchFileId = AFC.ArchFileId AND AFC.archCopyId = AJS.CopyId AND AFC.isValid = 1
						INNER JOIN archFile (NOLOCK) AF
							ON AF.jobId = AJS.JobId AND AF.commCellId = AJS.CommCellId 
                            AND AF.id = VOL.ArchFileId
							AND AJS.AppId = AF.appId AND AF.isValid = 1
						INNER JOIN SMMetaData (NOLOCK) META 
							ON META.RefType = 1 /*SM_MRT_VOLUME*/ 
							AND META.MetaDataType = 47 /* SM_MDT_VOLUME_SIZE */ 
							AND VOL.SMVolumeId = META.RefId
				GROUP BY VOL.AppId, VOL.CopyId, VOL.JobId ) T_SUM
	ON AJS.copyId = T_SUM.copyId AND AJS.AppId = T_SUM.appId AND AJS.JobId = T_SUM.JobId

	UPDATE	@appJobSnapSize
	SET		AppTypeName = APPTYPE.name
	FROM	@appJobSnapSize AJS
			INNER JOIN APP_iDAType (NOLOCK) APPTYPE 
			ON AJS.AppTypeId = APPTYPE.type

	DECLARE @SnapEngineNewNamesMap TABLE
    (
        EngineId 			INT PRIMARY KEY,
        SnapEngineNewName	NVARCHAR(256)
    )
 
	INSERT INTO @SnapEngineNewNamesMap
	VALUES 
		(2, 'Hitachi Shadow Image'),								-- SM_SNAPSHOT_ENGINE_HDS
		(4, 'Dell EMC TimeFinder BCV'),								-- SM_SNAPSHOT_ENGINE_SYMMETRIX
		(5, 'Dell EMC TimeFinder Snap'),							-- SM_SNAPSHOT_ENGINE_SYMMETRIX_SNAP
		(6, 'Dell EMC VNX / CLARiiON SnapView / VNX Snap'),			-- SM_SNAPSHOT_ENGINE_CLARIION_SNAP
		(7, 'Dell EMC VNX / CLARiiON SnapView Clone'),				-- SM_SNAPSHOT_ENGINE_CLARIION_CLONE
		(8, 'Hitachi COW Snap'),									-- SM_SNAPSHOT_ENGINE_HDS_SNAP
		(9, 'Dell EqualLogic Snap'),								-- SM_SNAPSHOT_ENGINE_EQUALLOGIC
		(12, 'HPE EVA Snap'),										-- SM_SNAPSHOT_ENGINE_HPEVA_SNAP
		(13, 'HPE EVA Clone'),										-- SM_SNAPSHOT_ENGINE_HPEVA_CLONE
		(16, 'NetApp E-series / LSI Snap'),							-- SM_SNAPSHOT_ENGINE_LSI_SNAP
		(18, 'Dell EqualLogic Clone'),								-- SM_SNAPSHOT_ENGINE_EQUALLOGIC_CLONE
		(19, 'Dell EMC VNX / Celerra SnapSure Snap'),				-- SM_SNAPSHOT_ENGINE_CELERRA
		(22, 'Dell Storage Center Snap (Deprecated)'),				-- SM_SNAPSHOT_ENGINE_COMPELLENT
		(25, 'HPE 3PAR StoreServ Snap'),							-- SM_SNAPSHOT_ENGINE_3PAR_SNAP
		(26, 'HPE 3PAR StoreServ Clone'),							-- SM_SNAPSHOT_ENGINE_3PAR_CLONE
		(27, 'Dell EMC Isilon Snap'),								-- SM_SNAPSHOT_ENGINE_ISILON_SNAP
		(28, 'Dell EMC TimeFinder Clone'),							-- SM_SNAPSHOT_ENGINE_SYMMETRIX_CLONE
		(29, 'Fujitsu ETERNUS AF / DX Snap'),						-- SM_SNAPSHOT_ENGINE_ETERNUS_SNAP
		(30, 'Fujitsu ETERNUS AF / DX Clone'),						-- SM_SNAPSHOT_ENGINE_ETERNUS_CLONE
		(31, 'Nimble Storage Snap (Deprecated)'),					-- SM_SNAPSHOT_ENGINE_NIMBLE_SNAP
		(33, 'Hitachi Shadow Image (CCI)'),							-- SM_SNAPSHOT_ENGINE_HDS_CCI_CLONE
		(34, 'Hitachi Thin Image (CCI)'),							-- SM_SNAPSHOT_ENGINE_HDS_CCI_SNAP
		(41, 'Fujitsu ETERNUS AF / DX SnapOPC+'),					-- SM_SNAPSHOT_ENGINE_ETERNUS_SNAPOPCPLUS
		(44, 'HPE Nimble Storage Snap'), 							-- SM_SNAPSHOT_ENGINE_NIMBLE_SNAP_REPLICA
		(45, 'Dell EMC VPLEX Snap'),								-- SM_SNAPSHOT_ENGINE_VPLEX_SNAP
		(46, 'Dell EMC VPLEX Clone'),								-- SM_SNAPSHOT_ENGINE_VPLEX_CLONE
		(47, 'Dell EMC TimeFinder VP Snap'),						-- SM_SNAPSHOT_ENGINE_SYMMETRIX_VP_SNAP
		(52, 'Pure Storage FlashArray Snap'),						-- SM_SNAPSHOT_ENGINE_PURE_STORAGE_SNAP
		(61, 'NetApp SolidFire Snap'),								-- SM_SNAPSHOT_ENGINE_SOLIDFIRE_SNAP
		(62, 'Dell EMC XtremIO Snap'),								-- SM_SNAPSHOT_ENGINE_XTREMIO_SNAP
		(63, 'Dell EMC TimeFinder SnapVX'),							-- SM_SNAPSHOT_ENGINE_VMAX_SNAPVX
		(65, 'Dell EMC Unity Snap'),								-- SM_SNAPSHOT_ENGINE_UNITY_SNAP
		(67, 'Hitachi NAS Snap'), 									-- SM_SNAPSHOT_ENGINE_HNAS_SNAP
		(68, 'Western Digital IntelliFlash Snap'),					-- SM_SNAPSHOT_ENGINE_TEGILE_SNAP
		(73, 'Hitachi Targetless Snap (CCI)'),						-- SM_SNAPSHOT_ENGINE_HDS_CCI_TARGETLESS_SNAP
		(74, 'Dell EMC Storage Center Snap (DSM)')					-- SM_SNAPSHOT_ENGINE_COMPELLENT_DSM_SNAP

    UPDATE	@appJobSnapSize
    SET		SnapCount = SC.SnapCount,
    		SnapEngineId = SC.SnapShotEngineId,
    		SnapEngineName = ISNULL(newNameMap.SnapEngineNewName, SC.SnapEngineName),
    		OriginalSnapEngineId = SC.OriginalSnapEngineId,
    		OriginalSnapEngineName = ISNULL(newNameMapOrig.SnapEngineNewName, SC.OrigSnapEngineName)
	FROM	@appJobSnapSize	AJS
			INNER JOIN 
            (   SELECT  VOL.jobId, VOL.CopyId, SNAP.SnapShotEngineId, SNAP.OriginalSnapEngineId , OriENG.SnapEngineName as OrigSnapEngineName, ENG.SnapEngineName, COUNT(CHECKSUM(SNAP.GroupId, SNAP.UniqueIdentifier)) AS SnapCount
				FROM	@appJobSnapSize AJS
						INNER JOIN SMVolume (NOLOCK) VOL
							ON VOL.JobId = AJS.JobId AND VOL.CommCellId = AJS.CommCellId AND VOL.CopyId = AJS.CopyId
						INNER JOIN SMVolSnapMap (NOLOCK) MAP ON VOL.SMVolumeId = MAP.SMVolumeId
						INNER JOIN SMSnap (NOLOCK) SNAP ON MAP.SMSnapId = SNAP.SMSnapId
						INNER JOIN SMSnapShotEngine (NOLOCK) ENG ON ENG.SnapShotEngineId = SNAP.SnapShotEngineId
						INNER JOIN SMSnapShotEngine (NOLOCK) OriENG ON OriENG.SnapShotEngineId = SNAP.OriginalSnapEngineId
                GROUP BY VOL.jobId, VOL.CopyId, SNAP.SnapShotEngineId, ENG.SnapEngineName , SNAP.OriginalSnapEngineId, OriENG.SnapEngineName  ) SC
			ON AJS.JobId = SC.JobId AND AJS.CopyId = SC.CopyId
			LEFT JOIN @SnapEngineNewNamesMap newNameMap ON newNameMap.EngineId = SC.SnapShotEngineId
			LEFT JOIN @SnapEngineNewNamesMap newNameMapOrig ON newNameMapOrig.EngineId = SC.OriginalSnapEngineId

DECLARE  @Usage XML
set @Usage =
      (SELECT snapSize.AppTypeId			AS '@appTypeId',
			snapSize.AppTypeName			AS '@appTypeName',
			CASE AG.appGroup 	  		
				 WHEN 'Granular Agents' THEN 'File System'
				 ELSE AG.AppGroup 
			END	 AS '@appGroup',		
			CASE 
				WHEN snapSize.OriginalSnapEngineId IN (45,46) THEN snapSize.OriginalSnapEngineId  -- SM_SNAPSHOT_ENGINE_VPLEX_SNAP, SM_SNAPSHOT_ENGINE_VPLEX_CLONE
				ELSE  snapSize.SnapEngineId
			END AS '@snapEngineId',
			CASE 
				WHEN snapSize.OriginalSnapEngineId IN (45,46) THEN dbo.NormalizeForXML(snapSize.OriginalSnapEngineName) -- SM_SNAPSHOT_ENGINE_VPLEX_SNAP, SM_SNAPSHOT_ENGINE_VPLEX_CLONE
				ELSE  dbo.NormalizeForXML(snapSize.SnapEngineName)
			END  AS '@snapEngineName',
			snapSize.CommCellId			    AS '@commCellId',
			cell.aliasName				    AS '@commCellName',
			CASE 
				WHEN snapSize.IsReplication = 0 THEN 'Data Protection Snapshot' 
				ELSE 'Data Protection Replication' 
			END								AS '@LicCapacityType',
			COUNT(snapSize.AppId)			AS '@totalSubClients',
			SUM(snapSize.SnapCount)			AS '@totalSnapCount',
			SUM(snapSize.SnapSize)/1048576	AS '@totalSnapSize'  --1024*1024 =1048576  size in MB
	FROM	@appJobSnapSize snapSize
	INNER JOIN APP_CommCell AS cell WITH(NOLOCK)
	ON 
	snapSize.CommCellId = cell.id
	INNER JOIN App_AppTypeGroups AG (NOLOCK) ON snapSize.AppTypeId = AG.AppTypeId	
	WHERE snapSize.SnapEngineName <> ''
	GROUP BY	snapSize.AppTypeId, snapSize.AppTypeName, AG.appGroup, 
				snapSize.SnapEngineId, snapSize.SnapEngineName,
				snapSize.OriginalSnapEngineId, snapSize.OriginalSnapEngineName,
				snapSize.CommCellId, snapSize.IsReplication,cell.aliasName

FOR XML PATH('SnapEngineUsage')
)    

    DROP TABLE #jmbkpSnapJobsSurvey

	
SET @surveyXML = (SELECT @Usage
FOR XML PATH('SnapEngineXML'))

--------- END SURVEY QUERY ---------

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

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())

--SELECT cast(@surveyXML as xml)
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 ---------