--Name:- LeastDedupedSubclients
--Description:- Returns top 100 least deduped subclients on the last full job
-- in last 30 days and on the non-full jobs completed in last 7 days. 
-- The dedupSavingPct is calculated from jobs on dedup primary copy.
-- Snap jobs are not included in calculation.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

DECLARE	@FromTime INT = DATEDIFF(SECOND, '01/01/1970', DATEADD(DAY, -30, GETUTCDATE()))

DECLARE	@MinJobSizeMB	INT = 10

SELECT	@MinJobSizeMB = CAST(CAST(value AS VARCHAR(20)) AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'Minimum Job Size MB for Least Deduped Subclients'


CREATE TABLE #ExcludedAppIds (appId INT)

INSERT INTO #ExcludedAppIds
SELECT componentNameId FROM APP_subclientProp WITH (NOLOCK) 
WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID') 
  AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'))
  AND attrVal <> '0' AND modified = 0
UNION
SELECT id FROM APP_Application WITH (NOLOCK) 
WHERE clientId IN (SELECT componentNameId FROM APP_ClientProp WITH (NOLOCK) WHERE attrName = 'Content Index Server' AND attrVal = '1' AND modified = 0)


CREATE	TABLE #DedupPrimaryCopy (archGrpId INT, archGrpName NVARCHAR(144), copyId INT)
CREATE	TABLE #SubclientDedup (appId INT, archGrpId INT, onFullJobs INT, 
		appSize BIGINT, sizeOnMedia BIGINT, dedupSavingPct DECIMAL(10,2))

INSERT INTO #DedupPrimaryCopy
SELECT	AG.id, AG.name, AGC.id
FROM	archGroup AG WITH (NOLOCK)
		INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AG.defaultCopy = AGC.id
WHERE	((AGC.dedupeFlags & 262144) > 0) AND (AGC.dedupeFlags & 268435456) = 0


CREATE TABLE #LastFullJob (appId INT, jobId INT, copyId INT)

INSERT	INTO #LastFullJob
SELECT	J.appId, MAX(J.jobId), A.copyId
FROM	JMBkpStats J WITH (NOLOCK)
		INNER JOIN #DedupPrimaryCopy A
		ON A.archGrpId IN (J.dataArchGrpId, J.logArchGrpId) 
WHERE	J.servEndDate > @FromTime AND J.status IN (1, 14)
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) 
	AND J.BkpLevel IN (1, 64, 128, 16384, 1024, 32768)
	AND J.totalUncompBytes > 0
GROUP BY J.appId, A.copyId

INSERT	INTO #SubclientDedup
SELECT	TOP 100 B.appId, J.archGrpId, 1, MAX(B.totalUncompBytes), SUM(J.sizeOnMedia),
		100.0*(MAX(B.totalUncompBytes) - SUM(J.sizeOnMedia))/MAX(B.totalUncompBytes) AS DedupSavingPct
FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #LastFullJob L
			ON J.jobId = L.jobId AND J.commCellId = 2 AND J.archGrpCopyId = L.copyId AND J.AuxCopyJobId = 0
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON B.JobId = L.jobId AND B.commCellId = 2
GROUP BY B.appId, J.archGrpId
HAVING MAX(B.totalUncompBytes) > @MinJobSizeMB*1024*1024
ORDER BY DedupSavingPct

DROP TABLE #LastFullJob


SET @FromTime = DATEDIFF(SECOND, '01/01/1970', DATEADD(DAY, -7, GETUTCDATE()))

CREATE TABLE #IncrJobs (appId INT, jobId INT, archGrpId INT, copyId INT, appSize BIGINT)

INSERT	INTO #IncrJobs
SELECT	J.appId, J.jobId, A.archGrpId, A.copyId, J.totalUncompBytes
FROM	JMBkpStats J WITH (NOLOCK)
		INNER JOIN #DedupPrimaryCopy A
		ON A.archGrpId IN (J.dataArchGrpId, J.logArchGrpId) 
WHERE	J.servEndDate > @FromTime AND J.status IN (1, 14)
	AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) 
	AND J.BkpLevel NOT IN (1, 64, 128, 16384, 1024, 32768)
	AND J.totalUncompBytes > @MinJobSizeMB*1024*1024

INSERT	INTO #SubclientDedup
SELECT	TOP 100 A.appId, A.archGrpId, 0, A.appSize, B.sizeOnMedia, 
		100.0*(A.appSize - B.sizeOnMedia)/A.appSize AS DedupSavingPct
FROM	(SELECT	appId, archGrpId, SUM(appSize) AS appSize
	FROM	#IncrJobs
	GROUP BY appId, archGrpId
	) A 
	INNER JOIN
	(SELECT	J.appId, J.archGrpId, SUM(J.sizeOnMedia) AS sizeOnMedia
	FROM	JMJobDataStats J WITH (NOLOCK)
		INNER JOIN #IncrJobs I 
			ON J.jobId = I.jobId AND J.commCellId = 2 AND J.archGrpCopyId = I.copyId AND J.AuxCopyJobId = 0
	GROUP BY J.appId, J.archGrpId
	) B ON A.appId = B.appId AND A.archGrpId = B.archGrpId
ORDER BY DedupSavingPct

DROP TABLE #IncrJobs


SET @surveyXML = (
	SELECT	S.appId AS '@AppId', 
			dbo.NormalizeForXML(C.name) AS '@Client', 
			I.name AS '@Agent',
			dbo.FixInstanceName(INS.name, A.appTypeId) AS '@Instance',
			dbo.NormalizeForXML(BS.name) AS '@Backupset',
			dbo.NormalizeForXML(A.subclientName) AS '@Subclient',
			dbo.NormalizeForXML(P.archGrpName) AS '@StoragePolicy',
			S.onFullJobs AS '@OnFullJobs',
			S.appSize AS '@AppSize',
			S.sizeOnMedia AS '@DataWritten', 
			CASE WHEN S.dedupSavingPct > 0 THEN S.dedupSavingPct ELSE 0 END AS '@DedupSavingPct'
	FROM	#SubclientDedup S
			INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = S.appId
			INNER JOIN APP_Client C WITH (NOLOCK) ON C.id = A.clientId
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON I.type = A.appTypeId
			INNER JOIN APP_InstanceName INS WITH (NOLOCK) ON INS.id = A.instance
			INNER JOIN APP_Backupsetname BS WITH (NOLOCK) ON BS.id = A.backupset
			INNER JOIN #DedupPrimaryCopy P WITH (NOLOCK) ON P.archGrpId = S.archGrpId
			LEFT OUTER JOIN #ExcludedAppIds E ON S.appId = E.appId
	WHERE	E.appId IS NULL
	ORDER BY S.onFullJobs DESC, S.dedupSavingPct

	FOR XML PATH ('LeastDedupedSubclients')
)

DROP TABLE #DedupPrimaryCopy
DROP TABLE #SubclientDedup

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