DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10271
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[totalOnePassSubs] int,[totalObjectBasedSubs] int,[totalJobBasedSubs] int,[TotalNoInfiSubs] int,[TotalObjectBasedNoInfiSubs] int,[ObjectBaseBothEnabledWithArchiveInfi] int,[ObjectBaseBothEnabledWithBackupInfi] int,[ObjectBaseBackupOnlyEnableWithBackupInfi] int,[TotalJobBasedNoInfiSubs] int,[jobBaseRetentionInfinityly] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; create table #clientInfo(clientId int, IndexVersion int);
create table #retentionStatus(clientId int, subclientId int,flag int,Archivedexpirationdays int,Userdeletedstubsexpirationdays int,Daystokeepitemspasttheirexpiration int,BackupRetention int,archiveRention int);
--select BackupRetention  from #retentionStatus
insert into #clientInfo(clientId, IndexVersion)
--for client running in v11 or upgrade client after v11 sp7
select client.id,
       (case when clientprop.attrVal = 1 then 2 else 1 end) as indexstatus
from app_client client WITH (NOLOCK),
	 app_clientprop clientProp WITH (NOLOCK)
where   clientprop.componentNameId =client.id
		and clientprop.attrName = ''IndexingV2''
		and clientprop.modified = 0
		and client.status&6 = 0
		and client.status != 4096 --4096 is laptop client
union  
-- for v1 client before v11 sp7 doesn''t have IndexV2 entry
select client.id,
       1 as indexstatus
from app_client client WITH (NOLOCK)
where client.status&6 = 0 
and client.status != 4096 --4096 is laptop client
and ''IndexingV2'' not in (
						select attrName 
						from  app_clientprop clientProp WITH (NOLOCK)
						where client.id = clientProp.componentNameId
						);
		
		
insert into #retentionStatus(clientId,subclientId,flag,Archivedexpirationdays,Userdeletedstubsexpirationdays,Daystokeepitemspasttheirexpiration,BackupRetention,archiveRention) 
select clientId,subclientid,
			[Data Migrator Rule Flag],
			[Archived expiration days] ,
			[User deleted stubs expiration days] ,
			[Days to keep items past their expiration],
			[Is Backup Retention Mode Enabled],
			[Is Archive Retention Mode Enabled]
from
    (select client.id as clientid, application.id as subclientid,subclientprop2.attrName as attrName,subclientprop2.attrVal as attrVal			
		from app_client client WITH (NOLOCK),
		app_subclientprop subclientprop WITH (NOLOCK), 
		app_application application WITH (NOLOCK),
		app_subclientprop subclientprop2 WITH (NOLOCK)
		where client.status &6 = 0 
		 and client.status != 4096 --4096 is laptop client
		 and client.id = application.clientid
		 and application.id = subclientprop.componentNameId 
		 and subclientprop.attrname=''First Turbo Job Id'' 
		 and subclientprop.modified=0 
		 and subclientprop2.componentnameid = application.id 
		 and subclientprop2.attrName in (''Data Migrator Rule Flag'',
										''Archived expiration days'',
										''User deleted stubs expiration days'',
										''Days to keep items past their expiration'',
										''Is Backup Retention Mode Enabled'',
										''Is Archive Retention Mode Enabled''
										 )
		 and subclientprop2.modified =0 		 
		 )temp
	pivot(max(attrVal) for attrName in ([Data Migrator Rule Flag],
										[Archived expiration days],
										[User deleted stubs expiration days],
										[Days to keep items past their expiration],
										[Is Backup Retention Mode Enabled],
										[Is Archive Retention Mode Enabled])
										)as maxattr 
union	
select clientId,subclientid,
			[Data Migrator Rule Flag],
			[Archived expiration days] ,
			[User deleted stubs expiration days] ,
			[Days to keep items past their expiration],
			[Is Backup Retention Mode Enabled],
			[Is Archive Retention Mode Enabled]
from
    (select client.id as clientid, application2.id as subclientid,subclientprop.attrName as attrName,subclientprop.attrVal as attrVal			
		from app_client client WITH (NOLOCK),
		     app_subclientprop subclientprop WITH (NOLOCK),
			 app_application application WITH (NOLOCK),
			 app_application application2 WITH (NOLOCK),
			 app_subclientprop subclientprop2 WITH (NOLOCK),
			 app_subclientProp subclientprop3 WITH (NOLOCK) 
		 where client.status &6 = 0 
		 and client.status != 4096 --4096 is laptop client
		 and application.clientid =2
		 and application.id = subclientprop.componentNameId  -- for subclient policy associate, the retention rules applied on subclient under subclient policy
		 and subclientprop.attrName in (''Data Migrator Rule Flag'',
										''Archived expiration days'',
										''User deleted stubs expiration days'',
										''Days to keep items past their expiration'',
										''Is Backup Retention Mode Enabled'',
										''Is Archive Retention Mode Enabled''
										 )
		 and subclientprop.modified =0 
		 and application.backupset in (select id from APP_BackupSetName where status = 128)		 
		 and subclientprop2.attrName =''Associated subclient Policy''   -- identify subclient policy associated subclient
		 and subclientprop2.attrVal = application.id
		 and subclientprop2.modified = 0
		 and subclientprop2.componentNameId = application2.id
		 and application2.clientid = client.id
		 and subclientprop3.componentNameId = application2.id    --First turbo job info will save at be associated subclient
		 and subclientprop3.attrname=''First Turbo Job Id'' 
		 and subclientprop3.modified=0
		 )temp
	pivot(max(attrVal) for attrName in ([Data Migrator Rule Flag],
										[Archived expiration days],
										[User deleted stubs expiration days],
										[Days to keep items past their expiration],
										[Is Backup Retention Mode Enabled],
										[Is Archive Retention Mode Enabled])
										) as maxattr; 

----final layout-------
select sum(temp.ObjectBasedSubclient + temp.jobBasedSubclinet) as totalOnePassSubs,
	 sum(temp.ObjectBasedSubclient) as totalObjectBasedSubs,
	 sum(temp.jobBasedSubclinet) as totalJobBasedSubs,
	 sum(1-ObjectBaseBothEnabledWithArchiveInfinitly-ObjectBaseBothEnabledWithBackupInfinitly-ObjectBaseBackupEnableWithBackupInfinitly-jobBaseRetentionInfinityly) as TotalNoInfiSubs,
	 sum(temp.ObjectBasedSubclient - temp.ObjectBaseBothEnabledWithArchiveInfinitly -temp.ObjectBaseBothEnabledWithBackupInfinitly) as TotalObjectBasedNoInfiSubs,
     sum(temp.ObjectBaseBothEnabledWithArchiveInfinitly)as ObjectBaseBothEnabledWithArchiveInfi,
	 sum(temp.ObjectBaseBothEnabledWithBackupInfinitly) as ObjectBaseBothEnabledWithBackupInfi,	 	 
	 sum(temp.ObjectBaseBackupEnableWithBackupInfinitly) ObjectBaseBackupOnlyEnableWithBackupInfi,
	 sum(temp.jobBasedSubclinet - temp.jobBaseRetentionInfinityly ) as TotalJobBasedNoInfiSubs,	
	 sum(temp.jobBaseRetentionInfinityly) jobBaseRetentionInfinityly     
from
(select b.clientid clientid, 
(case  when  (b.BackupRetention = 1 or b.BackupRetention is null)  then 1 else 0 end) as ObjectBasedSubclient,
(case  when b.ArchiveRention =1 and b.BackupRetention = 0  then 1 else 0 end) as jobBasedSubclinet,
(case  when ((b.ArchiveRention =1 and b.BackupRetention = 1) or b.ArchiveRention is null) and (flag =''262150'' and Archivedexpirationdays = -1) or (flag !=''262150''and  Userdeletedstubsexpirationdays = -1) then 1 else 0 end )as ObjectBaseBothEnabledWithArchiveInfinitly,
(case  when ((b.ArchiveRention =1 and b.BackupRetention = 1) or b.ArchiveRention is null) and (Daystokeepitemspasttheirexpiration = -1)  then 1 else 0 end ) as ObjectBaseBothEnabledWithBackupInfinitly,
(case  when b.ArchiveRention =0 and b.BackupRetention = 1 and  (Daystokeepitemspasttheirexpiration = -1 ) then 1 else 0 end ) as ObjectBaseBackupEnableWithBackupInfinitly,
(case  when b.ArchiveRention =1 and b.BackupRetention = 0 and ((flag =''262150'' and Archivedexpirationdays = -1) or (flag !=''262150''and  Userdeletedstubsexpirationdays = -1))  then 1 else 0 end ) as jobBaseRetentionInfinityly
from #retentionStatus  b where b.flag is not null )temp, 
#clientInfo a
where a.clientid = temp.clientid;

drop table #clientInfo;
drop table #retentionStatus; ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [totalOnePassSubs] AS '@totalOnePassSubs',[totalObjectBasedSubs] AS '@totalObjectBasedSubs',[totalJobBasedSubs] AS '@totalJobBasedSubs',[TotalNoInfiSubs] AS '@TotalNoInfiSubs',[TotalObjectBasedNoInfiSubs] AS '@TotalObjectBasedNoInfiSubs',[ObjectBaseBothEnabledWithArchiveInfi] AS '@ObjectBaseBothEnabledWithArchiveInfi',[ObjectBaseBothEnabledWithBackupInfi] AS '@ObjectBaseBothEnabledWithBackupInfi',[ObjectBaseBackupOnlyEnableWithBackupInfi] AS '@ObjectBaseBackupOnlyEnableWithBackupInfi',[TotalJobBasedNoInfiSubs] AS '@TotalJobBasedNoInfiSubs',[jobBaseRetentionInfinityly] AS '@jobBaseRetentionInfinityly' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10271'), type
)
  FOR XML PATH ('SurveyResults') 
)

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