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

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

SET NOCOUNT ON

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

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



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

--------- Insert your SQL statements here 
-- Query Name: Protected data per agent for last 7 days
-- Description: How much data was protected by ida per week
-- Gets the Protected data size per agent and per Backup level (FULL/INCR/DIFF/OTHER)

DECLARE	@lastDays	INT = 7
DECLARE	@fromTime	INT = dbo.GetUnixTime(DATEADD(DAY, -@lastDays, GETUTCDATE()))

SET @surveyXML = (
	SELECT  AgentType AS '@AgentType',
		AgentTypeId AS '@AgentTypeId',
		BackupLevel AS '@BackupLevel',
		SUM(ApplicationSize)/1024.00/1024.0 AS '@ApplicationSizeMB',
		COUNT(JobCount) AS '@JobCount'
		FROM (SELECT	CASE AgentType
					WHEN '' THEN I.name				
					ELSE AgentType END AS AgentType,
				I.type AS AgentTypeId,				
				J.bkpLevel AS BackupLevel,
				J.totalUncompBytes AS ApplicationSize,
				J.jobId AS JobCount
			FROM JMBkpStats J WITH (NOLOCK) 
				INNER JOIN APP_iDAType I WITH (NOLOCK) 
					ON I.type = J.appType 
				INNER JOIN (select appTypeId, CASE appTypeId WHEN 64 THEN 'Big Data Apps' ELSE LTRIM(RTRIM(MAX(osGroup+' '+App))) END AS AgentType from App_AppTypeGroups GROUP BY appTypeId) AAT 
					ON J.appType=AAT.appTypeId	
	WHERE J.servEndDate > @fromTime AND 
		  J.status IN (1, 3, 14) AND (J.status <> 3 OR I.isCWEjobValid = 1) AND J.commCellId = 2 
		  AND J.appType NOT IN (600, 601, 602, 603, 604, 605, 606, 607, 650, 651, 652)) A
	GROUP BY AgentType, AgentTypeId, BackupLevel 
	FOR XML PATH('ProtectedDataPerAgentInfo')
)

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