DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10375
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @temptbl table 
(
[SourceClientId] int,[AppId] int,[AgentName] nvarchar(max),[SnapEngineName] nvarchar(max),[ControlHostId] int,[SMVolumeId] int,[SMSnapId] int,[MountJobId] int,[MountStatus] int,[CreationTime] int,[ExpireTime] int
)
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; Begin 
SET NOCOUNT ON

DECLARE @AppCloneFlag int = 2048;

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

select InterResult.SourceClientId, InterResult.AppId, App_iDAType.name as AgentName, InterResult.SMVolumeId, InterResult.MountJobId, InterResult.MountStatus, InterResult.CreationTime, InterResult.ExpireTime into #AppCloneVolumes
from App_iDAType inner join
(select SMVolume.SourceClientId, SMVolume.AppId, SMVolume.AppTypeId, AppClone.SMVolumeId, AppClone.MountJobId, AppClone.MountStatus, AppClone.CreationTime, AppClone.ExpireTime 
from (select * from SMMountVolume where Mountflags = @AppCloneFlag) as AppClone inner join SMVolume on SMVolume.SMVolumeId = AppClone.SMVolumeId) as InterResult
on App_iDAtype.type = InterResult.AppTypeId

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

select #AppCloneVolumes.*, SMVolSnapMap.SMSnapId into #AppCloneSnaps 
from #AppCloneVolumes inner join SMVolSnapMap on #AppCloneVolumes.SMVolumeId = SMVolSnapMap.SMVolumeId;

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

select #AppCloneSnaps.*, SMSnap.SnapShotEngineId, SMSnap.ControlHostId into #AppCloneResult
from SMSnap inner join #AppCloneSnaps on #AppCloneSnaps.SMSnapId = SMSnap.SMSnapId;

select #AppCloneResult.SourceClientId, #AppCloneResult.AppId, #AppCloneResult.AgentName, SMSnapShotEngine.SnapEngineName,
#AppCloneResult.ControlHostId, #AppCloneResult.SMVolumeId, #AppCloneResult.SMSnapId, 
#AppCloneResult.MountJobId, #AppCloneResult.MountStatus, #AppCloneResult.CreationTime, #AppCloneResult.ExpireTime
from #AppCloneResult inner join SMSnapShotEngine on SMSnapShotEngine.SnapShotEngineId = #AppCloneResult.SnapShotEngineId;

DROP TABLE #AppCloneVolumes
DROP TABLE #AppCloneSnaps
DROP TABLE #AppCloneResult

SET NOCOUNT OFF

End ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [SourceClientId] AS '@SourceClientId',[AppId] AS '@AppId',[AgentName] AS '@AgentName',[SnapEngineName] AS '@SnapEngineName',[ControlHostId] AS '@ControlHostId',[SMVolumeId] AS '@SMVolumeId',[SMSnapId] AS '@SMSnapId',[MountJobId] AS '@MountJobId',[MountStatus] AS '@MountStatus',[CreationTime] AS '@CreationTime',[ExpireTime] AS '@ExpireTime' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10375'), 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'))