--Name:- Scale Report CommCell Status 
--Description:- QS_scalereport_commcell_status

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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



IF OBJECT_ID('tempdb.dbo.#unitRowTable') IS NOT NULL
	DROP TABLE #unitRowTable

CREATE TABLE #unitRowTable ( Query VARCHAR(255), LastHour decimal(13,2) null , Last24Hours decimal(13,2) DEFAULT 0, Unit VARCHAR(max))
DECLARE @VAR_HR INT
DECLARE @VAR_24HR INT
DECLARE @VARF_HR FLOAT
DECLARE @VARF_24HR FLOAT
DECLARE @AVGF_HR FLOAT
DECLARE @AVGF_24HR FLOAT
DECLARE @TEMP1 INT
DECLARE @TEMP2 INT
DECLARE @TEMPF1 FLOAT
DECLARE @TEMPF2 FLOAT
DECLARE @JOBS_COMPLETED_LAST_HR INT
DECLARE @JOBS_COMPLETED_LAST_24HR INT
DECLARE @JOBS_SINGLE_ATTEMPT_LAST_HR INT
DECLARE @JOBS_SINGLE_ATTEMPT_LAST_24HR INT
DECLARE @TOTAL_ATTEMPTS_LAST_HR INT
DECLARE @TOTAL_ATTEMPTS_LAST_24HR INT
DECLARE @TOTAL_APP_SIZE_LAST_HR BIGINT
DECLARE @TOTAL_APP_SIZE_LAST_24HR BIGINT

DECLARE @CUR_TIME INT = dbo.GetUnixTime(GetUTCDate())
DECLARE @1HourAgo INT = @CUR_TIME - 60*60
DECLARE @24HrsAgo INT = @CUR_TIME - 60*60*24

DECLARE @1HourAgoDT DATETIME = DATEADD(HOUR, -1, GetUTCDate())
DECLARE @24HrsAgoDT DATETIME = DATEADD(HOUR, -24, GetUTCDate())
DECLARE @csVersion INT = 15
--print 'Query'+char(9)+char(9)+char(9)+char(9)+'LastHour'+char(9)+'Last24Hours'+char(9)+'Units';
--print ' ';
/*
  Report Format:
  ---------------------------------------------------------------------------------------
		Query, LastHour, Last24Hours, Unit
  ---------------------------------------------------------------------------------------
*/
/************* 1.	Number of chunks closed per hour ************************************
*		A chunk is the smallest unit of backup data kept in the commcell.When a 
*		chunk is created, it is said to be opened and when the writing is done
*		on the storage it is closed. A chunk opening time is specified by the
*		ctime. How long it took between open and close is specified by the writetime. 
*		So we take the sum of create time and the write time to obtain the chunk 
*		closing time.
*
******************* 12. Size of total Chunks Written perh hour**************************
*
*		This query gives the total size of chunks written per hour on to the storage in 
*		last one and twenty four hours respectively.
*
****************************************************************************************/

SELECT @csVersion = MAX(id) FROM simAllGalaxyRel

SELECT	@VAR_HR = COUNT(*), @VARF_HR = SUM(logicalSize)/(1024.0*1024.0*1024.0)
FROM	ArchChunk WHERE	createTime > @1HourAgo

SELECT	@VAR_24HR = COUNT(*), @VARF_24HR = SUM(logicalSize)/(1024.0*1024.0*1024.0)/24

FROM	ArchChunk WHERE	createTime > @24HrsAgo
SET @TEMPF1 = ISNULL(@VAR_HR,0) 
SET @TEMPF2 = ISNULL(@VAR_24HR/24.0,0) 

INSERT INTO #unitRowTable VALUES('CHUNKS CLOSED', convert(float,round(@TEMPF1,0)), convert(float,round(@TEMPF2,2)), 'CNT/HR');

SET @TEMPF1 = ISNULL(@VARF_HR,0) 
SET @TEMPF2 = ISNULL(@VARF_24HR,0) 

INSERT INTO #unitRowTable VALUES('SIZE OF CHUNKS' ,convert(float,round(@TEMPF1,2)), convert(float,round(@TEMPF2,2)), 'GB/HR');

/************* 2.   Number of Reservations per hour *************************************
*
*		It gives the number of reservations made.
* 
****************************************************************************************/
SET @VAR_HR =  (select count(distinct reservationid) from jmjobresourcehistory WHERE ReservationTime > @1HourAgo)
SET @VARF_24HR = (select count(distinct reservationid) from jmjobresourcehistory WHERE ReservationTime > @24HrsAgo)

INSERT INTO #unitRowTable VALUES('NO. OF RESERVATIONS',@VAR_HR,@VARF_24HR,'CNT/HR');

/*************** 3.	Total number of jobs ************************************************ 
*
*		It gives the count of number of jobs completed in the last one hour
*		and the last twenty four hours.
*
****************************************************************************************/
SELECT	@JOBS_COMPLETED_LAST_HR = COUNT(jobId), 
		@TOTAL_ATTEMPTS_LAST_HR = SUM(numOfAttempts) - SUM(numOfPhases), 
		@JOBS_SINGLE_ATTEMPT_LAST_HR = SUM(CASE WHEN numOfPhases = numOfAttempts THEN 1 ELSE 0 END),
		@TOTAL_APP_SIZE_LAST_HR = SUM(totalUncompBytes)
FROM	(SELECT	B.jobId, B.totalUncompBytes, COUNT(DISTINCT A.phase) AS numOfPhases, COUNT(*) AS numOfAttempts
		FROM	JMBkpStats B INNER JOIN JMBkpAtmptStats A 
			 ON B.jobId = A.jobId AND B.commCellId = A.commCellId AND B.commCellId = 2 
			AND B.status IN (1, 3, 14) AND B.servEndDate > @1HourAgo
		GROUP BY B.jobId, B.totalUncompBytes) T

SELECT	@JOBS_COMPLETED_LAST_24HR = COUNT(jobId), 
		@TOTAL_ATTEMPTS_LAST_24HR = SUM(numOfAttempts) - SUM(numOfPhases), 
		@JOBS_SINGLE_ATTEMPT_LAST_24HR = SUM(CASE WHEN numOfPhases = numOfAttempts THEN 1 ELSE 0 END),
		@TOTAL_APP_SIZE_LAST_24HR = SUM(totalUncompBytes)
FROM	(SELECT	B.jobId, B.totalUncompBytes, COUNT(DISTINCT A.phase) AS numOfPhases, COUNT(*) AS numOfAttempts
		FROM	JMBkpStats B INNER JOIN JMBkpAtmptStats A 
			 ON B.jobId = A.jobId AND B.commCellId = A.commCellId AND B.commCellId = 2 
			AND B.status IN (1, 3, 14) AND B.servEndDate > @24HrsAgo
		GROUP BY B.jobId, B.totalUncompBytes) T

INSERT INTO #unitRowTable VALUES('JOBS COMPLETED'  ,@JOBS_COMPLETED_LAST_HR,@JOBS_COMPLETED_LAST_24HR,'CNT');

/*************** 4.  Number of jobs completing in a single attempt **********************
*
*		A job can have different phases depending on its nature. Each phase 
*		may take more than one attempt based on the available resources, network 
*		connections and many others reasons. Now this record gives the percentage 
*		of jobs which were successful in their first attempt in all the phases.
*
****************************************************************************************/
SET @TEMPF1 = ISNULL(100.0*@JOBS_SINGLE_ATTEMPT_LAST_HR/NULLIF(@JOBS_COMPLETED_LAST_HR, 0), 0)
SET @TEMPF2 = ISNULL(100.0*@JOBS_SINGLE_ATTEMPT_LAST_24HR/NULLIF(@JOBS_COMPLETED_LAST_24HR, 0), 0)
INSERT INTO #unitRowTable VALUES('JOBS COMPLETED IN ONE ATMPT',@TEMPF1,@TEMPF2,'% OF JOBS')

/**************** 5. Average number of attempts for a job to complete *******************
*
*		It gives the average number of attempts a job has taken to complete.
*
****************************************************************************************/
SET @TEMPF1 = ISNULL(1.0*(@TOTAL_ATTEMPTS_LAST_HR+@JOBS_COMPLETED_LAST_HR)/NULLIF(@JOBS_COMPLETED_LAST_HR, 0), 0) 
SET @TEMPF2 = ISNULL(1.0*(@TOTAL_ATTEMPTS_LAST_24HR+@JOBS_COMPLETED_LAST_24HR)/NULLIF(@JOBS_COMPLETED_LAST_24HR, 0), 0) 
INSERT INTO #unitRowTable VALUES('AVG NO. OF ATMPTS PER JOB',@TEMPF1,@TEMPF2,'ATMPTS/JOB');

SET @TEMPF1 = ISNULL(@TOTAL_APP_SIZE_LAST_HR,0)/(1024.0*1024.0*1024.0)
SET @TEMPF2 = ISNULL(@TOTAL_APP_SIZE_LAST_24HR,0)/(1024.0*1024.0*1024.0*24)
INSERT INTO #unitRowTable VALUES('AVG JOB THROUGHPUT',convert(float,round(@TEMPF1,2)),convert(float,round(@TEMPF2,2)),'GB/HR/JOB');

/******************  6.	Number of jobs in queue *********************************
*
*		Jobs present in the queue for varied reasons have their states as following
*		which can be obtained from the JMJobInfo table.
*
****************************************************************************************/
/*** STATE 15 INDICATES QUEUED ****/
SET @VAR_HR = (SELECT COUNT(*) 
			   FROM jmjobinfo WITH (READUNCOMMITTED)
			   WHERE state = 15)
--print 'JOBS IN QUEUE(QUEUED)'+char(9)+char(9)+CAST(@VAR_HR AS varchar)+char(9)+char(9)+'N/A'+char(9)+char(9)+'CNT OF JOBS ';
INSERT INTO #unitRowTable VALUES('JOBS IN QUEUE(QUEUED)',@VAR_HR,-1,'CNT OF JOBS');
/*** STATE 2 INDICATES PENDING ****/
SET @VAR_HR = (SELECT COUNT(*) 
			   FROM jmjobinfo WITH (READUNCOMMITTED)
			   WHERE state =2 )
--print 'JOBS IN QUEUE(PENDING)'+char(9)+char(9)+CAST(@VAR_HR AS varchar)+char(9)+char(9)+'N/A'+char(9)+char(9)+'CNT OF JOBS ';
INSERT INTO #unitRowTable VALUES('JOBS IN QUEUE(PENDING)',@VAR_HR,-1,'CNT OF JOBS');
/*** STATE 3 INDICATES WAITING ****/
SET @VAR_HR = (SELECT COUNT(*) 
			   FROM jmjobinfo WITH (READUNCOMMITTED)
			   WHERE state = 3)
--print 'JOBS IN QUEUE(WAITING)'+char(9)+char(9)+CAST(@VAR_HR AS varchar)+char(9)+char(9)+'N/A'+char(9)+char(9)+'CNT OF JOBS ';
INSERT INTO #unitRowTable VALUES('JOBS IN QUEUE(WAITING)',@VAR_HR,-1,'CNT OF JOBS');
/*** STATE 5 INDICATES SUSPENDED ****/
SET @VAR_HR = (SELECT COUNT(*) 
			   FROM jmjobinfo WITH (READUNCOMMITTED)
			   WHERE state = 5)
INSERT INTO #unitRowTable VALUES('JOBS IN QUEUE(SUSPENDED)',@VAR_HR,-1,'CNT OF JOBS');

/*******************  7. Number of Jobs currently running *******************************  
*
*		The jobs currently running have their entries in JMJobInfo table with state =1.
*
****************************************************************************************/
SET @VAR_HR = (SELECT COUNT(*) FROM jmjobinfo WITH (READUNCOMMITTED) WHERE state = 1 )
INSERT INTO #unitRowTable VALUES('JOBS IN QUEUE(RUNNING)',@VAR_HR,-1,'CNT OF JOBS');

/*****************  8.  No of mounts/hour per commcell **********************************
*
*		The table MMDriveHistory contains the number of times a drive has been mounted.
*		Using the above table this query gives the overall count of mounts  per hour 
*		extended over a period of the last one hour and twenty four hours.
*
****************************************************************************************/
SET @VARF_24HR = (SELECT SUM(NumberOfMounts)/24.0 FROM MMDriveHistory WHERE TimeStart > @24HrsAgoDT)
SET @VAR_HR = (SELECT SUM(NumberOfMounts) FROM MMDriveHistory WHERE TimeStart > @1HourAgoDT)
SET @TEMP1 = ISNULL(@VAR_HR,0) 
SET @TEMP2 = ISNULL(@VARF_24HR,0) 
INSERT INTO #unitRowTable VALUES('NO. OF MOUNTS/HOUR',@TEMP1,convert(float,round(@TEMP2,2)),'MNTS/HOUR');

/**************   9.	Highest number of streams transferring data *********************
*
*		A data stream can be defined  as a data channel that connects the client file 
*		system or database to the storage media. Multiple streams provide for multiple 
*		channels through which data can flow.
*
*		In this query first we identify the count of distinct stream ids in an interval 
*		of five minutes extending over a period of one hour. Then we indetify the maximum 
*		of those counts to get the desired output.
*
*		The same procedure is repeated to find the maximum during the last twenty four 
*		hours with the same interval of five minutes.
*
****************************************************************************************/
 SELECT @VAR_HR = MAX(query.counthr_ids),@AVGF_HR =(AVG(query.counthr_ids)*1.0)
 FROM
(
	SELECT RMTimeStamp/300 AS times,COUNT(distinct h.StreamId) AS counthr_ids
	FROM jmjobresourcehistory AS h
	WHERE RMTimeStamp > @1HourAgo
	GROUP BY RMTimeStamp/300
) query
SELECT @VAR_24HR = MAX(query.count24hr_ids),@AVGF_24HR =(AVG(query.count24hr_ids)*1.0)
FROM 
(
	SELECT RMTimeStamp/300 AS times,COUNT(DISTINCT h.StreamId) AS count24hr_ids
	FROM jmjobresourcehistory AS h
	WHERE RMTimeStamp > @24HrsAgo
	GROUP BY RMTimeStamp/300
)query
SET @TEMP1 = ISNULL(@VAR_HR,0) 
SET @TEMP2 = ISNULL(@VAR_24HR,0) 
INSERT INTO #unitRowTable VALUES('MAX NO. OF STRMS TRANSF. DATA',@TEMP1,@TEMP2,'MAX CNT');

/*************** 10.	Average number of streams for the past 24 hours *****************
*
*		This query does the same as above but obtains the Average of the count instead 
*		of the maximum of StreamId counts.
*
****************************************************************************************/
SET @TEMPF1 = ISNULL(@AVGF_HR,0) 
SET @TEMPF2 = ISNULL(@AVGF_24HR,0) 
INSERT INTO #unitRowTable VALUES('AVG NO. OF STRMS TRANSF. DATA', convert(float,round(@TEMPF1,2)), convert(float,round(@TEMPF2,2)), 'AVG CNT');

/*************** 11.        Last Full DBMaintenance Performed          *****************
*
****************************************************************************************/
DECLARE @DbmaintenanceTime INT;
SET @DbmaintenanceTime = (SELECT CAST(CAST(value AS VARCHAR(32)) AS INT) FROM GXGlobalParam WHERE name = 'FullDBMaintenanceTime');
	 
INSERT INTO #unitRowTable VALUES('LAST DBMAINTAINENCE DT',@DbmaintenanceTime,-1,'');

/***************************************************************************************/

/*************** 12.        Databases Size          *****************
*
****************************************************************************************/

INSERT INTO #unitRowTable
SELECT CASE WHEN db.name = 'CommServ' THEN 'Commserv Database Size'
			WHEN db.name = 'WFEngine' THEN 'WFEngine Database Size'
			WHEN db.name = 'DM2' THEN 'DM2 Database Size'
			WHEN db.name = 'CVCloud' THEN 'CVCloud Database Size'
			WHEN db.name = 'HistoryDB' THEN 'History Database Size'
			WHEN db.name = 'AuditDB' THEN 'Audit Database Size'
			WHEN db.name = 'CacheDB' THEN 'Cache Database Size'
			WHEN db.name = 'ResourceMgrDB' THEN 'ResourceMgr Database Size'
			WHEN db.name = 'TemplateDB' THEN 'Template Database Size'
			WHEN db.name = 'TroubleShooting' THEN 'TroubleShooting Database Size'			
		END, 
	ISNULL(CAST(SUM(fs.size)*8.0/1024.0/1024.0 as decimal(10,2)),0), -1, 'GB'
	FROM sys.master_files fs INNER JOIN sys.databases db 
		ON fs.database_id = db.database_id AND db.name IN ('CommServ','WFEngine','DM2','CVCloud','HistoryDB','AuditDB', 'CacheDB','ResourceMgrDB','TemplateDB','TroubleShooting')
	GROUP BY db.database_id, db.name

DECLARE @CommservDatabaseSize XML

IF @csVersion >=16
BEGIN
	SET @CommservDatabaseSize = (
	SELECT mf.name AS '@name',
	   (mf.size*8) AS '@size',
       mf.physical_name AS '@Physical_Path',
       mf.type AS '@fileType',
	   CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT)*8 AS '@usedMB',
	   (CAST(mf.size AS BIGINT) - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT))*8 AS '@freeMB',
	   CAST ((( CAST(mf.size AS FLOAT) - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS FLOAT)) / CAST(mf.size AS FLOAT)) * 100 AS DECIMAL(10,2) ) AS '@pctFree',
	   CASE mf.max_size
			WHEN -1 THEN 'Unlimited'
			WHEN 0 THEN 'No Growth'
			ELSE 'Limited to '+ dbo.FormatBytes(mf.max_size/128, 'MB')
       END AS '@maxSize',
	   mf.is_percent_growth AS '@isPercentGrowth',
	   CASE WHEN mf.is_percent_growth = 1 THEN mf.growth
            ELSE mf.growth/128
       END AS '@growthSize'
       FROM sys . master_files AS mf WITH ( NOLOCK )
	   INNER JOIN sys . databases db WITH ( NOLOCK ) ON mf . database_id = db . database_id       
	WHERE db . name = 'commserv'
	FOR XML PATH('CommservDatabaseSize')
)
END
ELSE
BEGIN
	SET @CommservDatabaseSize = (
	SELECT mf.name AS '@name',
	   (mf.size*8) AS '@size',
       mf.physical_name AS '@Physical_Path',
       mf.type AS '@fileType',
	   CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT)*8 AS '@usedMB',
	   (CAST(mf.size AS BIGINT) - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS BIGINT))*8 AS '@freeMB',
	   CAST ((( CAST(mf.size AS FLOAT) - CAST(FILEPROPERTY(mf.name, 'SpaceUsed') AS FLOAT)) / CAST(mf.size AS FLOAT)) * 100 AS DECIMAL(10,2) ) AS '@pctFree',
	   CASE mf.max_size
			WHEN -1 THEN 'Unlimited'
			WHEN 0 THEN 'No Growth'
			ELSE 'Limited to '+ CONVERT(NVARCHAR(100), mf.max_size/(128*1024)) + ' GB'
       END AS '@maxSize',
	   mf.is_percent_growth AS '@isPercentGrowth',
	   CASE WHEN mf.is_percent_growth = 1 THEN mf.growth
            ELSE mf.growth/128
       END AS '@growthSize'
       FROM sys . master_files AS mf WITH ( NOLOCK )
	   INNER JOIN sys . databases db WITH ( NOLOCK ) ON mf . database_id = db . database_id       
	WHERE db . name = 'commserv'
	FOR XML PATH('CommservDatabaseSize')
)
END

UPDATE #unitRowTable
SET Unit = CAST(@CommservDatabaseSize AS nvarchar(max))
WHERE Query = 'Commserv Database Size'

-- SQL Server Version and Edition
INSERT INTO #unitRowTable 
SELECT 'SQL Server Version', CAST(SERVERPROPERTY('EngineEdition') AS FLOAT), -1, @@VERSION;

-- TempDb Data File Total Size
SELECT @VARF_HR = 
    CAST((SUM(((f.size * 8) / 1024.0)) / 1024.0) AS DECIMAL(10,2))
FROM tempdb.sys.database_files f
WHERE f.type = 0

SET @TEMPF1 = ISNULL(@VARF_HR,0) 
--	  print 'Current tempDB Size : '+ char(9)+char(9)+char(9)+CAST( convert(float,round(@TEMPF1,2)) AS VARCHAR);
INSERT INTO #unitRowTable VALUES('TempDB Size',@TEMPF1,-1,'GB');

-- TempDb Log File Total Size
SELECT @VARF_HR = 
    CAST((SUM(((f.size * 8) / 1024.0)) / 1024.0) AS DECIMAL(10,2))
FROM tempdb.sys.database_files f
WHERE f.type = 1

SET @TEMPF1 = ISNULL(@VARF_HR,0) 

INSERT INTO #unitRowTable VALUES('TempDB Log Size',@TEMPF1,-1,'GB');

--TempDB Driver Size 

IF  OBJECT_ID(N'sys.dm_os_volume_stats') IS NOT NULL  --Function dm_os_volume_stats does not exist in SQL Server 2008 R2 RTM
SET @VARF_HR= ( SELECT top 1 
        CONVERT(DECIMAL(11,2),dovs.total_bytes/1048576.0)/1024
    FROM sys.master_files mf
        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
    WHERE DB_NAME(dovs.database_id) IN ('TempDb'))

SET @TEMPF1 = ISNULL(@VARF_HR,0) 
INSERT INTO #unitRowTable VALUES('Disk Size of TempDB',@TEMPF1,-1,'GB');

-- TempDb And CommservDB on same drive?

DECLARE  @CommServDBDisk  NVARCHAR(8) = NULL
DECLARE  @TempDBDisk  NVARCHAR(8) = NULL

DECLARE @physical_name_commserv NVARCHAR(260) = (select TOP 1 physical_name from sys.master_files WHERE database_id = DB_ID(N'commserv'))
DECLARE @physical_name_temp NVARCHAR(260) = (select TOP 1 physical_name from sys.master_files WHERE database_id = DB_ID(N'tempdb'))

IF ((select TOP 1 SUBSTRING(@physical_name_commserv, 1, 1)) = '/')
BEGIN
	--on linux...
	SET  @CommServDBDisk = (SELECT top 1 LEFT(@physical_name_commserv, CHARINDEX('/',RIGHT(@physical_name_commserv, LEN(@physical_name_commserv)-1))) STRIPPED_STRING)	

	SET @TempDBDisk = (SELECT top 1 LEFT(@physical_name_temp, CHARINDEX('/',RIGHT(@physical_name_temp, LEN(@physical_name_temp)-1))) STRIPPED_STRING) 

END

ELSE
BEGIN
	SET  @CommServDBDisk = (SELECT top 1 LEFT(@physical_name_commserv, CHARINDEX('\',@physical_name_commserv)-1) STRIPPED_STRING)

	SET @TempDBDisk = (SELECT top 1 LEFT(@physical_name_temp, CHARINDEX('\',@physical_name_temp)-1) STRIPPED_STRING)

END



INSERT INTO #unitRowTable VALUES('Volume of CommServ DB',-1,-1,@CommServDBDisk);

INSERT INTO #unitRowTable VALUES('Volume of Temp DB',-1,-1,@TempDBDisk);


/*************** 13.        Top 10 Tables ordered by total size and row count          *****************
*
********************************************************************************************************/


BEGIN TRY

-- top 10 tables ordered by total size 
-- top 10 tables ordered by row count

IF OBJECT_ID('tempdb..#TableSize') IS NOT NULL
      DROP TABLE #TableSize

CREATE TABLE #TableSize
      (
	  objectId BIGINT,
      name nvarchar(128), 
      rows INT, 
      reserved BIGINT, 
      data BIGINT, 
      index_size BIGINT, 
      unused BIGINT
      )

INSERT INTO #TableSize
SELECT SO.object_id,
	   OBJECT_NAME(SO.object_id), 
	   (SUM (
                     CASE
                           WHEN (DDPS.index_id < 2) THEN DDPS.row_count
                           ELSE 0
                     END
                     )
		),
		(SUM (DDPS.reserved_page_count) * 8),
		(SUM (
                     CASE
                           WHEN (DDPS.index_id < 2) THEN (DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count)
                           ELSE 0
                     END
                   )* 8),
		((CASE WHEN SUM (DDPS.used_page_count) > SUM (
                     CASE
                           WHEN (DDPS.index_id < 2) THEN (DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count)
                           ELSE 0
                     END
                     ) THEN (SUM (DDPS.used_page_count) - SUM (
                     CASE
                           WHEN (DDPS.index_id < 2) THEN (DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count)
                           ELSE 0
                     END
                     )) ELSE 0 END) * 8),
		((CASE WHEN SUM (DDPS.reserved_page_count) > SUM (DDPS.used_page_count) THEN (SUM (DDPS.reserved_page_count) - SUM (DDPS.used_page_count)) ELSE 0 END) * 8)
FROM sys.objects SO
INNER JOIN sys.dm_db_partition_stats DDPS ON DDPS.object_id = SO.object_id 
WHERE type = 'U'
GROUP BY SO.object_id

DECLARE @tableInfo xml
DECLARE @ScaleTableInfo XML 
DECLARE @excludeTables TABLE
(
	TableName				varchar(100)

)

SET @ScaleTableInfo = '<ScaleTableInfo>	
  <Table Name="APP_GUID" />
  <Table Name="APP_VMProp" />
  <Table Name="archChunk" />
  <Table Name="archChunkMapping" />
  <Table Name="archChunkMappingPruningLogs" />
  <Table Name="archChunkPruningLogs" />
  <Table Name="archChunkToCopyHistory" />
  <Table Name="ArchChunkToReplicateHistory" />
  <Table Name="archChunkToSilo" />
  <Table Name="archChunkToSyncDDBHistory" />
  <Table Name="archChunkToVerifyHistory" />
  <Table Name="archFile" />
  <Table Name="archFileCopy" />
  <Table Name="archfileCopyDedup" />
  <Table Name="archFileCopyPruningLogs" />
  <Table Name="archFileOracle" />
  <Table Name="archFilePruningLogs" />
  <Table Name="archFileSIDBKeysPruningLogs" />
  <Table Name="Archfilesubstore" />
  <Table Name="archFileSIDBKeys" />
  <Table Name="ArchSQLJobCopyToBeRetained" />
  <Table Name="EvLocaleMsgs" />
  <Table Name="EvLocaleMsgArgInfo" />
  <Table Name="evmsg" />
  <Table Name="evmsgcache" />
  <Table Name="evParam" />
  <Table Name="JMBkpAtmptStats" />
  <Table Name="JMBkpStats" />
  <Table Name="JMFailureReasonLocaleMsgCache" />
  <Table Name="JMJobDataStats" />
  <Table Name="JMJobOptions" />    
  <Table Name="JMJobStats" />    
  <Table Name="JMMisc" />    
  <Table Name="MMMediaGroupHistory" />  
  <Table Name="MMVolume" />
  <Table Name="sqlArchiveInfo" />
  <Table Name="sqlDbBackupInfo" />
  <Table Name="sqlFileBackupInfo" />  
  <Table Name="sqlLogMarks" />
  <Table Name="RptStorageUsage" />  
  <Table Name="UMUserCredentials" />
  <Table Name="TM_JobOptions" />
  <Table Name="APP_ClientProp" />
  <Table Name="APP_SubClientProp" />
  <Table Name="RptSLA" />
  <Table Name="RptCapacityUsage" />
  <Table Name="GRCJMJobDataStatsTracking" />  
  <Table Name="sqlAnalysisFileBackupInfo" />  
  <Table Name="Lic_PeakUsage" />
  <Table Name="SMMetaData" /> 
</ScaleTableInfo>'

	INSERT INTO @excludeTables(TableName)
	SELECT
		T.n.value('@Name', 'varchar(100)') 
		FROM @ScaleTableInfo.nodes('/ScaleTableInfo/Table') AS T(n)

set @tableInfo = (select T.name as '@TableName',
		T.rows as '@TotalRows',
	   T.TotalSizeinKB as '@TotalSizeinKB',
	   T.DataSizeinKB as '@DataSizeinKB',
	   T.IndexSizeinKB as '@IndexSizeinKB',
	   T.ReservedSizeinKB as '@ReservedSizeinKB',
	   T.UnusedSizeinKB as '@UnusedSizeinKB'
	   FROM (
SELECT TOP(10) name  , rows, B.data+B.index_size+B.reserved+B.unused AS 'TotalSizeinKB',
					B.data as DataSizeinKB,
					B.index_size as IndexSizeinKB,
					B.reserved as ReservedSizeinKB,
					B.unused as UnusedSizeinKB
      FROM #TableSize B LEFT OUTER JOIN @excludeTables ON TableName=name
	  WHERE TableName IS NULL
      ORDER BY TotalSizeInKB DESC ) T
	  FOR xml path('TableData')
	  )

INSERT INTO #unitRowTable VALUES('Largest Tables by Size ',-1,-1,cast(@tableInfo as varchar(max)));



set @tableInfo = (select T.name as '@TableName',
		T.rowsCount as '@TotalRows',
	   T.TotalSizeinKB as '@TotalSizeinKB',
	   T.DataSizeinKB as '@DataSizeinKB',
	   T.IndexSizeinKB as '@IndexSizeinKB',
	   T.ReservedSizeinKB as '@ReservedSizeinKB',
	   T.UnusedSizeinKB as '@UnusedSizeinKB'
	   FROM (
SELECT TOP(10) name  , convert(bigint, rows) as rowsCount,B.data+B.index_size+B.reserved+B.unused AS 'TotalSizeinKB',
					B.data as DataSizeinKB,
					B.index_size as IndexSizeinKB,
					B.reserved as ReservedSizeinKB,
					B.unused as UnusedSizeinKB
      FROM #TableSize B LEFT OUTER JOIN @excludeTables ON TableName=name
	  WHERE TableName IS NULL
      ORDER BY rowsCount DESC ) T
	  FOR xml path('TableData')
	  )

INSERT INTO #unitRowTable VALUES('Largest Tables by Rows ',-1,-1,cast(@tableInfo as varchar(max)));

set @tableInfo = (select T.name as '@TableName',
		T.rowsCount as '@TotalRows',
	   T.TotalSizeinKB as '@TotalSizeinKB',
	   T.DataSizeinKB as '@DataSizeinKB',
	   T.IndexSizeinKB as '@IndexSizeinKB',
	   T.ReservedSizeinKB as '@ReservedSizeinKB',
	   T.UnusedSizeinKB as '@UnusedSizeinKB'
	   FROM (
SELECT name  , convert(bigint, rows) as rowsCount, B.data+B.index_size+B.reserved+B.unused AS 'TotalSizeinKB',
					B.data as DataSizeinKB,
					B.index_size as IndexSizeinKB,
					B.reserved as ReservedSizeinKB,
					B.unused as UnusedSizeinKB
      FROM #TableSize B INNER JOIN @excludeTables ON TableName = name) T
      ORDER BY T.name
	  FOR xml path('TableData')
	  )
  
INSERT INTO #unitRowTable VALUES('Excluded Tables Rows and Size info',-1,-1,cast(@tableInfo as varchar(max)));

--hourly job details
IF OBJECT_ID('tempdb..#TableSize') IS NOT NULL
      DROP TABLE #TableSize

DECLARE @csTZName    NVARCHAR(1024) = ''
SELECT  @csTZName = dbo.GetClientTimeZone(2)

IF ISNULL(@csTZName, '') = ''
BEGIN
    SELECT @csTZName = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTZName = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTZName, CHARINDEX(':', @csTZName, CHARINDEX(':', @csTZName, 0) + 1) + 1, 255)
END


DECLARE	@dtBegin	DATETIME 
DECLARE	@dtEnd		DATETIME = dbo.LocalToUTCTime(getdate(),@csTZName)
DECLARE	@utBegin	INT = dbo.GetUnixTime(@dtBegin)
DECLARE	@utEnd		INT = dbo.GetUnixTime(@dtEnd)
DECLARE	@i			INT = 0

SET @dtBegin = DATEADD(HH, DATEDIFF(HH, 0, @dtEnd), 0)

DECLARE @HourTbl TABLE (HourComp INT, HourBeginDT DATETIME, HourEndDT DATETIME, HourBegin INT, HourEnd INT, Modified datetime)

declare  @stepCounter int = 24
WHILE (@stepCounter > 0)
BEGIN

    
	INSERT	INTO @HourTbl
	SELECT	 DATEPART(hour,dbo.utctolocaltime(@dtBegin,@csTZName)), @dtBegin, @dtEnd, 0, 0,NULL

    SET @dtEnd    = @dtBegin
	SET @stepCounter = (@stepCounter - 1)
	

	SET @dtBegin = DATEADD(HH, -1, @dtBegin)
END

UPDATE	@HourTbl SET HourBegin = dbo.GetUnixTime(HourBeginDT), HourEnd = dbo.GetUnixTime(HourEndDT)

declare @hourlyjobdata xml
IF  @csVersion >=16 --if CSVERSION >=11 USE JMJOBSTATEHISTORY, ELSE USE CNJobStateHistory tbl
BEGIN
	update @HourTbl set modified= T.modified from 
	@HourTbl Htbl inner join (
		SELECT	HourComp, MAX(B.modified) as modified
		FROM	@HourTbl H 
				INNER  JOIN JMJOBSTATEHISTORY B WITH (NOLOCK)
					on B.Modified <= HourBeginDT  and B.Modified <= HourEndDT
		group by HourComp
	)  T 
	ON Htbl.HourComp = T.HourComp
	set @hourlyjobdata = (select  case when HourComp =0 then '12:00 AM'
					  when HourComp =12 then '12:00 PM'
					  when (HourComp) < 12 then cast(HourComp as varchar(5)) +  ':00 AM'
					  else cast(HourComp-12 as varchar(5)) +  ':00 PM'
					  end as '@Hour', Running as '@Running', Pending as '@Pending', Waiting as '@Waiting', Suspended as '@Suspended' 
					  from @HourTbl Htbl
	inner join JMJOBSTATEHISTORY B WITH (NOLOCK)
		on Htbl.modified = B.Modified
	FOR XML PATH ('HourlyJobData')
)
END
ELSE
BEGIN 
	update @HourTbl set modified= T.modified from 
	@HourTbl Htbl inner join (
		SELECT	HourComp, MAX(B.modified) as modified
		FROM	@HourTbl H 
				INNER  JOIN CNJobStateHistory B WITH (NOLOCK)
					on B.Modified <= HourBeginDT  and B.Modified <= HourEndDT
		group by HourComp
	)  T 
	ON Htbl.HourComp = T.HourComp
	set @hourlyjobdata = (select  case when HourComp =0 then '12:00 AM'
					  when HourComp =12 then '12:00 PM'
					  when (HourComp) < 12 then cast(HourComp as varchar(5)) +  ':00 AM'
					  else cast(HourComp-12 as varchar(5)) +  ':00 PM'
					  end as '@Hour', Running as '@Running', Pending as '@Pending', Waiting as '@Waiting', Suspended as '@Suspended' 
					  from @HourTbl Htbl
	inner join CNJobStateHistory B WITH (NOLOCK)
		on Htbl.modified = B.Modified
	FOR XML PATH ('HourlyJobData')
	)
END


INSERT INTO #unitRowTable VALUES('Hourly Job Load',-1,-1,cast(@hourlyjobdata as varchar(max)));

/*** ****/

DECLARE @CSNode INT
DECLARE @CSClientId INT

SET @CSNode = ISNULL((select TOP 1 clientid from APP_Platform WITH (NOLOCK) where platformType = 1 order by clientid ASC), 0)

-- Find out if this is a cluster CS.


DECLARE @CSSPInfo NVARCHAR(MAX) = ''
DECLARE @isClusterCS INT = 0
SELECT @isClusterCS = attrVal FROM App_ClientProp WITH (NOLOCK) 
WHERE componentNameId = @CSNode
	AND attrName LIKE 'Virtual Client' 
	AND modified = 0

-- If it is a cluster CS, we need to consider the active physical node of CommServ as CommServ client id
	
IF @isClusterCS = 0
	SET @CSClientId = @CSNode
ELSE
	BEGIN 
		--Use currently active physical node		
		SELECT @CSClientId = attrVal
		FROM App_ClientProp WITH (NOLOCK) 
		WHERE componentNameId = @CSNode AND attrName like 'CS Active Physical Node' AND modified = 0
	END

DECLARE @SPVer FLOAT
SELECT  @SPVer=MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = @CSClientId  


IF(@Spver<22)
BEGIN
	DECLARE @scalemeasure VARCHAR(MAX) = '5000'
	IF ((SELECT releaseId FROM APP_Client WITH (READUNCOMMITTED) WHERE id = @CSNode)< 16)
		 SET @scalemeasure='4000'


	DECLARE @scaleValue VARCHAR(MAX) = ISNULL((SELECT CONVERT(VARCHAR(MAX), value)
					FROM GXGlobalParam  WITH (READUNCOMMITTED)
					WHERE name  = '16777216'),@scalemeasure)

	IF(ISNUMERIC(@scaleValue)=0)
	BEGIN
		DECLARE @OUT NVARCHAR(MAX)=NULL
		DECLARE @IN NVARCHAR(MAX)=@scaleValue
		EXEC miscProc2 @IN,@OUT OUTPUT
		SET @scaleValue =  @OUT
		SET @scaleValue =  REPLACE(@scaleValue,nchar(3),'')	
	END
					
	IF(ISNUMERIC(@scaleValue)=0)	
	SET @scaleValue =  @scalemeasure

					
	INSERT INTO #unitRowTable VALUES('Client Scale Limit',-1,-1, @scaleValue);
END
/****Drive Usage*****/

IF OBJECT_ID('tempdb..#DatabaseDriveMap') IS NOT NULL DROP TABLE #DatabaseDriveMap
CREATE TABLE #DatabaseDriveMap(LogicalDriveName NVARCHAR(MAX), Drive NVARCHAR(MAX), TotalSpaceMB BIGINT, FreeSpaceMB BIGINT, DbName NVARCHAR(MAX))

IF  OBJECT_ID(N'sys.dm_os_volume_stats') IS NOT NULL  --Function dm_os_volume_stats does not exist in SQL Server 2008 R2 RTM and below

	INSERT INTO #DatabaseDriveMap
	SELECT DISTINCT 
		dovs.logical_volume_name,
		dovs.volume_mount_point,
		CONVERT(INT,dovs.total_bytes/1048576.0),
		CONVERT(INT,dovs.available_bytes/1048576.0),
		DB_NAME(dovs.database_id)
	FROM sys.master_files mf
		CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
	WHERE DB_NAME(dovs.database_id) IN ('TempDb', 'Commserv', 'WFEngine', 'DM2', 'CVCloud','AuditDB','HistoryDB','CacheDB','ResourceMgrDB','TemplateDB','TroubleShootingDB')


DECLARE @DriveUsageXml XML 
SET @DriveUsageXml = (
SELECT
	LogicalDriveName AS '@LogicalDriveName',
	Drive AS '@Drive',
	TotalSpaceMB AS '@TotalSpaceMB',
	FreeSpaceMB AS '@FreeSpaceMB',
	STUFF((
			SELECT ', ' + DbName
			FROM #DatabaseDriveMap
			WHERE (Drive = D.Drive) 
			FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
		,1,2,'') AS '@Databases'
FROM #DatabaseDriveMap D 
GROUP BY LogicalDriveName, Drive, TotalSpaceMB, FreeSpaceMB
FOR XML PATH('Drive')
)

INSERT INTO #unitRowTable VALUES('Drive Usage',-1,-1, CAST(@DriveUsageXml AS NVARCHAR(MAX)));

IF OBJECT_ID('tempdb..#DatabaseDriveMap') IS NOT NULL DROP TABLE #DatabaseDriveMap

END TRY
BEGIN CATCH
	SELECT
		ERROR_NUMBER() as [TableName],
		ERROR_SEVERITY() as [RowCount],
		ERROR_STATE() as [Reserved(KB)],
		ERROR_MESSAGE() as [Data(KB)],
		1 as [Index(KB)],
		1 as [Unused(KB)]
END CATCH
	
SET @surveyXML = (
SELECT
	(SELECT 
		Query AS '@Query', 
		LastHour AS '@LastHour', 
		Last24Hours AS '@Last24Hours', 
		Unit AS '@Unit'
	FROM #unitRowTable FOR XML PATH ('ScaleReportCommcellStatusXML'), TYPE)
	

FOR XML PATH('ScaleReport')
)	

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
--select cast(@surveyXML as XML) 
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

DROP TABLE #unitRowTable

SET NOCOUNT OFF