

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Sp/RptSurveyCopyFreeSpace.sp] ---------- 

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/CloudDB/Sp/RptSurveyCopyFreeSpace.sp,v $ $Id: RptSurveyCopyFreeSpace.sp,v 1.8.14.4.20.1 2021/06/24 16:33:05 dkraplanee Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='RptSurveyCopyFreeSpace')
BEGIN
	print '>>> Drop Stored Procedure: RptSurveyCopyFreeSpace <<<'
	drop procedure RptSurveyCopyFreeSpace
END
IF EXISTS (select * from GxQscripts where name='RptSurveyCopyFreeSpace')
	delete from GxQscripts where name = 'RptSurveyCopyFreeSpace'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptSurveyCopyFreeSpace')
	delete from GXDBVersions where aliasname = 'RptSurveyCopyFreeSpace'
GO
print '... Creating Procedure: RptSurveyCopyFreeSpace'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptSurveyCopyFreeSpace
  @i_UserId INT = NULL,
  @i_GroupId INT = NULL,
  @blaggregate INT = 1,
  @blLatestSurvey INT = 1,
  @i_CommServUniqueID INT = 0,
  @i_LogDateStart DATETIME = NULL,
  @i_LogDateEND DATETIME = NUll
AS
SET NOCOUNT ON
DECLARE @CopyQueryId BIGINT = 70
DECLARE @LibQueryId  BIGINT = 96
SET @blLatestSurvey = 1 --Always pickup the latest
IF(@blaggregate=1)
BEGIN
	RETURN
END
DECLARE @strGood VARCHAR(10) = '1_Good'
DECLARE @strInfo VARCHAR(10) = '2_Info'
DECLARE @strwarning VARCHAR(10) = '3_Warning'
DECLARE @strCritical VARCHAR(10) = '4_Critical'
DECLARE @criticalVal int
DECLARE @warningVal int
SELECT	@warningVal = WarningThreshold,
		@criticalVal =CriticalThreshold
FROM	cf_SurveyHealthParam where queryId = @CopyQueryId
IF object_id('tempdb.dbo.#DiskLib') is not null DROP TABLE #DiskLib
CREATE TABLE #DiskLib(CommServUniqueId INT, LibraryId INT, LibraryName NVARCHAR(MAX), CapacityMB BIGINT, FreeSpaceMB BIGINT)
IF object_id('tempdb.dbo.#CopyDiskLib') is not null DROP TABLE #CopyDiskLib
CREATE TABLE #CopyDiskLib(CommServUniqueId INT, CommCellID VARCHAR(32), DisplayName NVARCHAR(256),
	AgId INT, AgName NVARCHAR(255), CopyId INT, CopyName NVARCHAR(255),
	LibraryId INT, LibraryName NVARCHAR(MAX), CapacityMB BIGINT, FreeSpaceMB BIGINT)
IF object_id('tempdb.dbo.#TmpResults') is not null DROP TABLE #TmpResults
CREATE TABLE #TmpResults(
	CommServUniqueId INT,
	CommCellID VARCHAR(32),
	DisplayName NVARCHAR(256),
	AgId INT,
	AgName NVARCHAR(255),
	CopyId INT,
	CopyName NVARCHAR(255),
	LibraryName NVARCHAR(MAX),
	CapacityMB BIGINT,
	FreeSpaceMB BIGINT
	)
INSERT INTO #DiskLib(CommServUniqueId, LibraryId, LibraryName, CapacityMB, FreeSpaceMB)
SELECT
	L.CommServUniqueId,
	L.LibraryId,
	L.LibraryName,
	CASE WHEN D.TotalSpaceGB > 0 THEN D.TotalSpaceGB*1024 ELSE -1 END,
	CASE WHEN D.TotalFreeSpaceGB > 0 THEN D.TotalFreeSpaceGB*1024 ELSE -1 END
FROM cf_Survey96DiskLibDetails AS D WITH (NOLOCK)
	INNER JOIN dbo.cf_SurveyResultFuncV1(@LibQueryId, @i_CommServUniqueID, @blLatestSurvey, @i_LogDateStart, @i_LogDateEND, @i_UserId, @i_GroupId) AS R
		ON R.SurverResultId = D.SurveyResultId
	INNER JOIN cf_SurveyLibrary L WITH (NOLOCK) ON D.CommServUniqueId = L.CommServUniqueId AND D.LibraryId = L.LibraryId
	where L.LibraryTypeId IN (3 , -3)
INSERT INTO #CopyDiskLib(CommServUniqueId, CommCellID, DisplayName, AgId, AgName, CopyId, CopyName, LibraryId, LibraryName, CapacityMB, FreeSpaceMB)
SELECT
	R.CommServUniqueId,
	R.CommCellID,
	R.DisplayName,
	C.AgId,
	C.AgName,
	C.CopyId,
	C.CopyName,
	C.LibraryId,
	D.LibraryName,
	D.CapacityMB,
	D.FreeSpaceMB
FROM cf_SurveyCopyFreeSpace AS C WITH (NOLOCK)
	INNER JOIN dbo.cf_SurveyResultFuncV1(@CopyQueryId, @i_CommServUniqueID, @blLatestSurvey, @i_LogDateStart, @i_LogDateEND, @i_UserId, @i_GroupId) AS R
		ON R.SurverResultId = C.SurveyResultId
	INNER JOIN #DiskLib D ON C.CommServUniqueId = D.CommServUniqueId AND C.LibraryId = D.LibraryId
INSERT INTO #TmpResults(CommServUniqueId, CommCellID, DisplayName, AgId, AgName, CopyId, CopyName, CapacityMB, FreeSpaceMB)
SELECT
	C.CommServUniqueId,
	C.CommCellID,
	C.DisplayName,
	C.AgId,
	C.AgName,
	C.CopyId,
	C.CopyName,
	SUM(D.CapacityMB),
	SUM(D.FreeSpaceMB)
FROM #CopyDiskLib C INNER JOIN #DiskLib D ON C.CommServUniqueId = D.CommServUniqueId AND C.LibraryId = D.LibraryId
GROUP BY C.CommServUniqueId, C.CommServUniqueId, C.CommCellID, C.DisplayName, C.AgId, C.AgName, C.CopyId, C.CopyName
IF @i_CommServUniqueID = 0
BEGIN
	SELECT
		CommServUniqueId  AS 'CommServ UniqueId',
		CommCellID AS 'CommCell ID',
		DisplayName AS 'CommCell Name',
		AgName AS 'Storage Policy',
		CopyName as 'Storage Policy Copy',
		STUFF((SELECT ', ' + B.LibraryName FROM #CopyDiskLib B WHERE B.CommServUniqueId = C.CommServUniqueId AND B.CopyId = C.CopyId
			FOR XML PATH('')), 1, 2, '') AS 'Library',
		CASE WHEN CapacityMB > 0 THEN CapacityMB ELSE -1 END as 'Capacity <SIZEMB>',
		CASE WHEN FreeSpaceMB > 0 THEN FreeSpaceMB ELSE -1 END as 'Free Disk Space <SIZEMB>',
		CASE WHEN CapacityMB > 0 THEN cast(100.0*FreeSpaceMB/CapacityMB as decimal(10,2)) ELSE '0' END AS [Free (%)] ,
		CASE WHEN CapacityMB <=0 THEN @strGood
			 ELSE (
				case when 100.0*FreeSpaceMB/CapacityMB < @criticalVal then @strCritical
				when 100.0*FreeSpaceMB/CapacityMB <= @warningVal and 100.0*FreeSpaceMB/CapacityMB >= @criticalVal then @strWarning
				else @strGood end)
		END AS 'Status<HEALTH_STATUS>'
	FROM #TmpResults C
	ORDER BY 'Status<HEALTH_STATUS>' DESC, [Free (%)], 'CommCell Name' ASC
END
ELSE
BEGIN
	SELECT
		AgName AS 'Storage Policy',
		CopyName as 'Storage Policy Copy',
		STUFF((SELECT ', ' + B.LibraryName FROM #CopyDiskLib B WHERE B.CommServUniqueId = C.CommServUniqueId AND B.CopyId = C.CopyId
			FOR XML PATH('')), 1, 2, '') AS 'Library',
		CASE WHEN CapacityMB > 0 THEN CapacityMB ELSE -1 END as 'Capacity <SIZEMB>',
		CASE WHEN FreeSpaceMB > 0 THEN FreeSpaceMB ELSE -1 END as 'Free Disk Space <SIZEMB>',
		CASE WHEN CapacityMB > 0 THEN cast(100.0*FreeSpaceMB/CapacityMB as decimal(10,2)) ELSE '0' END AS [Free (%)] ,
		CASE WHEN CapacityMB <=0 THEN @strGood
			 ELSE (
				case when 100.0*FreeSpaceMB/CapacityMB < @criticalVal then @strCritical
				when 100.0*FreeSpaceMB/CapacityMB <= @warningVal and 100.0*FreeSpaceMB/CapacityMB >= @criticalVal then @strWarning
				else @strGood end)
		END AS 'Status<HEALTH_STATUS>'
	FROM #TmpResults C
	ORDER BY [Free (%)]
END
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'RptSurveyCopyFreeSpace')
	delete from GxQscripts where name = 'RptSurveyCopyFreeSpace'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptSurveyCopyFreeSpace')
	delete from GXDBVersions where aliasname = 'RptSurveyCopyFreeSpace'
GO

insert into GXDBVersions values(2, 'RptSurveyCopyFreeSpace',  'v1.8.14.4.20.1', 'RptSurveyCopyFreeSpace', 'v1.8.14.4.20.1')
GO

