DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10379
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[SnapVendorName] nvarchar(max),[ArrayId] int,[ArrayName] nvarchar(max),[AssocName] nvarchar(max),[AssocType] int,[ValueStr] nvarchar(max),[Created] int,[Modified] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; Begin

SET NOCOUNT ON

Declare @ConfigName varchar(20) = ''Remote Snap MA'';

IF object_id(''tempdb.dbo.#MasterConfigs'') is not null 
       DROP TABLE #MasterConfigs

select SMMasterConfigs.Id, SMVendor.Name as SnapVendorName into #MasterConfigs from SMMasterConfigs inner join SMVendor 
on SMMasterConfigs.VendorId = SMVendor.Id  where SMMasterConfigs.Name = @ConfigName;

select #MasterConfigs.SnapVendorName, RMAConfigs.ArrayId, RMAConfigs.ArrayName, RMAConfigs.AssocName, 
RMAConfigs.AssocType, RMAConfigs.ValueStr, RMAConfigs.Created, RMAConfigs.Modified from #MasterConfigs
inner join
(select SMConfigs.MasterConfigId, SMConfigs.ArrayId,
        SMControlHost.SMArrayId as ArrayName, 
        AssocName = Case 
						when AssocType = 2
						then (select Name from SMVendor where Id = AssocId)
                        when AssocType = 3
						then (select SMArrayId from SMControlHost where ControlHostId = AssocId) 
						when AssocType = 6
                        then (select archGroup.name from archGroupCopy inner join archgroup on archGroupId = archGroup.Id where archgroupcopy.id = AssocID)
                        when AssocType = 7
						then (select name from APP_ClientGroup where id = AssocId)
						when AssocType = 8
						then (select name from APP_Client where id = AssocId)
						when AssocType = 9
						then (select subclientName from APP_Application where id = AssocId)
			    END, 
						IsValid, AssocType, ValueStr, Created, Modified
                from 
                        SMConfigs inner join SMControlHost 
                on 
                        SMControlHost.ControlHostId = SMConfigs.ArrayId) as RMAConfigs

on 
     RMAConfigs.MasterConfigId = #MasterConfigs.Id  
where 
     IsValid = 1 

Drop table #MasterConfigs

SET NOCOUNT OFF

End ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [SnapVendorName] AS '@SnapVendorName',[ArrayId] AS '@ArrayId',[ArrayName] AS '@ArrayName',[AssocName] AS '@AssocName',[AssocType] AS '@AssocType',[ValueStr] AS '@ValueStr',[Created] AS '@Created',[Modified] AS '@Modified' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10379'), 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'))