DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE()) 
DECLARE @queryId AS INTEGER = 10380
DECLARE @surveyXML nvarchar(MAX) 
 
DECLARE @releaseId integer 
SET @releaseId = (select releaseId from APP_Client where id = 2) 
 
DECLARE @temptbl table 
(
[Virtualization Subclients] int,[BackupJobs] int,[SuccessfulRestoreJobs] int,[FailedRestoreJobs] int,[Oracle on Unix] int,[Oracle on Windows] int,[SQL Server] int,[Exchange] int,[SharePoint] int,[ActiveDirectory] int,[MySQL] int
)


IF @releaseId = 15 OR @releaseId = 16
BEGIN 
 insert into @temptbl 
EXEC('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if object_id(''tempdb.dbo.#appawarelist'') is not null drop table #appawarelist
create table #appawarelist
(  scid int,
   clientid int,
   backupjobs int,
   successrestorejobs int,
   failedrestorejobs int,
   oracle int,
   oracleWin int,
   oracleUnix int,
   sqlserver int,
   activedirectory int,
   mysql int,
   exchange int,
   sharepoint int
   )
   
insert into #appawarelist 
select sc.componentNameId, A.clientid, 0,0, 0, 0,0, 0,0, 0, 0,0,0from app_subclientprop SC 
inner join app_application A
on attrname =''VS Collect Vss Meta Data'' and attrval =''1'' and SC.modified =0
and SC.componentNameId = A.id
and A.subclientStatus & ( 2|4 ) =0


DECLARE @30DaysAgo  int = dbo.GetUnixTime(DATEADD(DAY, -30, GETUTCDATE()))
update A
set backupjobs = T.count 
from 
( select A.scid, count(*) as count
from #appawarelist A
inner join JMBkpStats j
on A.scid  = j.appid
and j.status in (1,13,14,16)
and opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)

WHERE   J.servEndDate >= @30DaysAgo
group by A.scid 
) T
inner join #appawarelist A
on A.scid = T.scid 

update #appawarelist
set successrestorejobs = T.scount,
    failedrestorejobs = T.fcount 
from 
( select  sum(case when j.status in (1,3,14,16) then 1 else 0 end) as scount, 
                 sum(case when j.status in (2) then 1 else 0 end) as fcount  
from #appawarelist A
           INNER JOIN ( SELECT componentNameId, appId 
            FROM
            (
                SELECT componentNameId, CAST(attrVal  AS INT) appId, ROW_NUMBER() OVER (PARTITION BY P.componentNameId ORDER BY P.created DESC) rowNum
                FROM APP_ClientProp P  
                WHERE P.attrName = ''VSA Discover Subclient ID''
                AND P.modified = 0
                AND ISNUMERIC(P.attrVal) = 1
                AND LEN(P.attrVal) <= 10
            ) CP WHERE CP.rowNum = 1
			) T
			on T.appid = A.scid 
inner join app_clientprop cprop
on cprop.attrName =''Virtual Server Discovered Clients''
and cprop.attrval =''1''
and cprop.modified =0
and cprop.componentNameId = T.componentNameId
inner join JMRestoreStats J
on j.srcClientId = T.componentNameId
AND J.opType IN (5, 40) AND (J.rstattributes & 256) = 0 
WHERE   J.servEndTime >= @30DaysAgo
) T



update #appawarelist
set oracle = P.[Oracle], sqlserver = P.[SQL Server], activedirectory =[Active Directory],
MySQL = P.MySQL ,Exchange = [Exchange Database], sharepoint =[SharePoint Document]

from (

select  [Oracle], [SQL Server],[Active Directory],MySQL,[Exchange Database],[SharePoint Document]
from 
(

select count(distinct c.id) count, ida.displayName  from app_client c
inner join app_clientprop cprop
on c.id = cprop.componentNameId
and cprop.attrName =''Virtual Server Discovered Clients''
and cprop.attrval =''1''
and cprop.modified =0
inner join app_clientprop cprop1
on cprop1.componentNameId = cprop.componentNameId
and cprop1.attrName =''VSA Discover Subclient ID''
and cprop1.modified=0
inner join #appawarelist A
on A.scid = cprop1.attrVal
inner join APP_Application App
on App.clientid = C.id
inner join APP_iDAType ida
on App.appTypeId = ida.type
group by ida.displayName
) T
pivot ( max(T.count) for T.displayName in ( [Exchange Database], [Oracle], [SQL Server], [Active Directory],[MySQL] , [SharePoint Document])) pvt ) P

-- find count of Oracle on Windows
DECLARE @OracleWinCnt integer = 0
DECLARE @ORacleTotCnt integer = 0
DECLARE @appName nvarchar(max)


select @OracleTotCnt=oracle from #appawarelist

select @OracleWinCnt = count(distinct c.id), @appName = ida.displayName  from app_client c
inner join app_clientprop cprop
on c.id = cprop.componentNameId
and cprop.attrName =''Virtual Server Discovered Clients''
and cprop.attrval =''1''
and cprop.modified =0
inner join app_clientprop cprop1
on cprop1.componentNameId = cprop.componentNameId
and cprop1.attrName =''VSA Discover Subclient ID''
and cprop1.modified=0
inner join #appawarelist A
on A.scid = cprop1.attrVal
inner join APP_Application App
on App.clientid = C.id
inner join APP_iDAType ida
on App.appTypeId = ida.type and  ida.displayName like ''Oracle''
inner join simOperatingSYstem OS
ON C.simOperatingSystemId = OS.id and OS.type like ''%Windows%''
group by ida.displayName

update #appawarelist
SET oracleWin = @OracleWinCnt, oracleUnix = @OracleTotCnt - @OracleWinCnt

select count(scid) as ''Virtualization Subclients'', sum(backupjobs) BackupJobs, max(isnull(successrestorejobs,0)) as SuccessfulRestoreJobs, max(isnull(failedrestorejobs,0)) as FailedRestoreJobs, max(oracleUnix) as ''Oracle on Unix'', max(oracleWin) as ''Oracle on Windows'', max(sqlserver) as [SQL Server],
max(exchange) as Exchange, max(sharepoint) as SharePoint, max(activedirectory) as ActiveDirectory, max(mysql) as MySQL

from #appawarelist ') 
 END
 
 SET @surveyXML = 
( SELECT 
(
SELECT [Virtualization Subclients] AS '@Col_912964431',[BackupJobs] AS '@BackupJobs',[SuccessfulRestoreJobs] AS '@SuccessfulRestoreJobs',[FailedRestoreJobs] AS '@FailedRestoreJobs',[Oracle on Unix] AS '@Col_-1608512825',[Oracle on Windows] AS '@Col_1380545764',[SQL Server] AS '@Col_612278805',[Exchange] AS '@Exchange',[SharePoint] AS '@SharePoint',[ActiveDirectory] AS '@ActiveDirectory',[MySQL] AS '@MySQL' FROM @temptbl
 FOR XML PATH ('cf_MetricsQuery10380'), 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'))