

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/MMGetCloudVMsToPowerOFF.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/MMGetCloudVMsToPowerOFF.sp,v $ $Id: MMGetCloudVMsToPowerOFF.sp,v 1.1.2.15.54.1 2021/06/18 18:37:11 ishaarora Exp $";
--  +-----------------------------------------------------------------------+
--  |       PROCEDURE	:	"MMGetCloudVMsToPowerOFF"               		|
--  |       Author	    :	prasanthm                            			|
--  | Execute the System stored procedure to fetch the physical files   	|
--  | .. for the (current) database.                    					|
--  +-----------------------------------------------------------------------+
/*
	This will return the list of VMs need to be power off.
	The Jobs in the table MMVMPowerMgmtReq should be completed, which are all used those VMs. And no other jobs should use the VMs.
*/
-- Following Line Indicates new Class.  It should be identical to filename!
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='MMGetCloudVMsToPowerOFF')
	delete from GXDBVersions where aliasname = 'MMGetCloudVMsToPowerOFF'
GO
print '... Creating Procedure: MMGetCloudVMsToPowerOFF'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure MMGetCloudVMsToPowerOFF
AS
  DECLARE @o_HostId Integer
  DECLARE @o_isRequestFromGUI Integer
DECLARE @tmpHostTable TABLE(HostId Integer, isRequestFromGUI Integer)
DECLARE @MaxRetrycount INTEGER = 3
-- Getting VMs to power off
INSERT INTO @tmpHostTable
SELECT MH.ClientId,0 FROM MMHost MH WITH (NOLOCK)
INNER JOIN MMPowerMgmtHost PH WITH (NOLOCK) ON PH.HostId = MH.ClientId AND PH.NoPowerOff = 0
WHERE (MH.Attribute & 32768) = 32768
AND PH.PowerStatus IN (2, 3)
-- If same VM is waiting for Power on request
DELETE TMP
FROM @tmpHostTable TMP
INNER JOIN MMVMPowerMgmtReq VM WITH (NOLOCK) ON TMP.HostId = VM.HostId
WHERE VM.RequestStatus = 1
DELETE TMP
FROM @tmpHostTable TMP
INNER JOIN MMPowerMgmtJobToVMMap VM WITH (NOLOCK) ON TMP.HostId = VM.HostId AND VM.EntityType = 1
INNER JOIN JMJobInfo JM  WITH (NOLOCK) ON VM.EntityId = JM.JobId AND VM.EntityCommCellId = JM.commCellId AND JM.state <> 5 --SUSPENDED
-- Check any the VM is got reserved for other jobs
DELETE TMP
FROM @tmpHostTable TMP, MMResource RM WITH (NOLOCK)
WHERE TMP.HostId = RM.ClientId
-- If restore jobs running then should not send power off
DELETE TMP
FROM @tmpHostTable TMP
INNER JOIN MMPowerMgmtJobToVMMap MAP ON TMP.HostId = MAP.HostId
INNER JOIN JMJobInfo JM  WITH (NOLOCK) ON MAP.EntityId = JM.JobId AND MAP.EntityType = 1 AND MAP.EntityCommCellId = JM.CommCellId AND JM.state = 1 --RUNNING
-- skip to power off for running jobs using Proxy machine
DECLARE @proxyList TABLE (AppId INTEGER, proxyList VARCHAR(MAX), isFoundAtSubCLLevel INTEGER, InstanceId INTEGER)
INSERT INTO @proxyList(AppId, isFoundAtSubCLLevel, InstanceId)
SELECT JM.applicationId, 0, APP.instance FROM JMBkpJobInfo JM WITH (NOLOCK)
INNER JOIN JMJobInfo JJ WITH (NOLOCK) ON JJ.jobId = JM.jobId AND JM.commCellId = JJ.commCellId AND JJ.state <> 5 -- SUSPENDED
INNER JOIN APP_Application APP WITH (NOLOCK) ON APP.id = JM.applicationId AND APP.appTypeId = 106
-- sub client level check
UPDATE PY
SET  PY.proxyList = SPROP.attrVal
FROM @proxyList PY
INNER JOIN APP_subClientProp SPROP WITH (NOLOCK) ON SPROP.componentNameId = PY.AppId AND SPROP.attrName = 'Virtual Server Proxy Client List' and SPROP.modified = 0
UPDATE PY
SET PY.isFoundAtSubCLLevel = 1
FROM @proxyList PY
WHERE PY.proxyList like '%clientId=%' OR PY.proxyList like '%clientGroupId=%'
DELETE FROM @tmpHostTable WHERE HostId in (
SELECT HostId FROM @tmpHostTable TMP
INNER JOIN @proxyList PLIST ON PLIST.proxyList like '%clientId="'+CAST(TMP.HostId AS VARCHAR(100))+'"%'
)
DELETE FROM @tmpHostTable WHERE HostId in (
SELECT HostId FROM @tmpHostTable TMP
INNER JOIN APP_ClientGroupAssoc CLA WITH (NOLOCK) ON TMP.HostId = CLA.clientId
INNER JOIN @proxyList PLIST ON PLIST.proxyList like '%clientGroupId="'+CAST(CLA.clientGroupId AS VARCHAR(100))+'"%'
)
-- Instance level check
UPDATE PY
SET PY.InstanceId = CONVERT(INTEGER,ISNULL(SPROP1.attrVal,PY.InstanceId))
FROM @proxyList PY
INNER JOIN APP_InstanceProp SPROP WITH (NOLOCK)  ON SPROP.componentNameId = PY.InstanceId AND SPROP.attrName = 'Amazon Enable Admin Account' and SPROP.modified = 0 AND SPROP.attrVal = '1'
INNER JOIN APP_InstanceProp SPROP1 WITH (NOLOCK) ON SPROP1.componentNameId = SPROP.componentNameId AND SPROP1.attrName = 'Amazon Admin Instance Id' and SPROP1.modified = 0
WHERE PY.isFoundAtSubCLLevel = 0
UPDATE PY
SET  PY.proxyList = SPROP.attrVal
FROM @proxyList PY
INNER JOIN APP_InstanceProp SPROP WITH (NOLOCK)  ON SPROP.attrName = 'Vs Member Servers' and SPROP.componentNameId = PY.InstanceId AND SPROP.modified = 0
WHERE PY.isFoundAtSubCLLevel = 0
DELETE FROM @tmpHostTable WHERE HostId in (
SELECT HostId FROM @tmpHostTable TMP
INNER JOIN @proxyList PLIST ON PLIST.proxyList like '%clientId="'+CAST(TMP.HostId AS VARCHAR(100))+'"%'
WHERE PLIST.isFoundAtSubCLLevel = 0
)
DELETE FROM @tmpHostTable WHERE HostId in (
SELECT HostId FROM @tmpHostTable TMP
INNER JOIN APP_ClientGroupAssoc CLA WITH (NOLOCK) ON TMP.HostId = CLA.clientId
INNER JOIN @proxyList PLIST ON PLIST.proxyList like '%clientGroupId="'+CAST(CLA.clientGroupId AS VARCHAR(100))+'"%'
WHERE PLIST.isFoundAtSubCLLevel = 0
)
-- skip to power off for running jobs using DDB MA
DELETE FROM @tmpHostTable WHERE HostId in (
	SELECT SUB.ClientId FROM JMBkpJobInfo JM WITH (NOLOCK)
	INNER JOIN JMJobInfo JJ WITH (NOLOCK) ON JJ.jobId = JM.jobId AND JM.commCellId = JJ.commCellId AND JJ.state <> 5 -- SUSPENDED
	INNER JOIN APP_Application APP WITH (NOLOCK) ON APP.id = JM.applicationId
	INNER JOIN archGroup AG WITH (NOLOCK) ON (AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID)
	INNER JOIN archSubclientCopyDDBMap CSD WITH (READUNCOMMITTED) ON CSD.copyID = AG.defaultCopy AND CSD.appId = APP.id
	INNER JOIN IdxSIDBSubStore SUB WITH (NOLOCK) ON CSD.SIDBStoreId = SUB.SIDBStoreId
	INNER JOIN MMHost MH WITH (NOLOCK) ON MH.ClientId = SUB.ClientId
WHERE (MH.Attribute & 32768) = 32768
)
-- skip to power off for running jobs using Datapath MA
-- MMResource table check take care of skipping power off for running jobs. Suspend, pending and waiting jobs MAs are eligible for power off.
/*
DELETE FROM @tmpHostTable WHERE HostId in (
	SELECT DISTINCT MH.clientId FROM JMBkpJobInfo JM WITH (NOLOCK)
					INNER JOIN APP_Application APP WITH (NOLOCK) ON JM.applicationId = APP.id
					INNER JOIN archGroup AG WITH (NOLOCK) ON (AG.id = APP.dataArchGrpID OR AG.id = APP.logArchGrpID)
					INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON ( AGC.id = AG.defaultCopy OR AGC.id = AG.defaultSnapCopy )
					INNER JOIN MMDataPath DP WITH (NOLOCK) ON DP.copyId = AGC.id
					INNER JOIN MMDrivePool DRP WITH (NOLOCK) ON DRP.DrivePoolId = DP.DrivePoolId
					INNER JOIN MMHost MH WITH (NOLOCK) ON DRP.clientId = MH.clientId
WHERE (MH.Attribute & 32768) = 32768
)
*/
-- Won't retry if maximum retry reaches
	DECLARE @currentUTCTime BIGINT = dbo.GetUnixTime(GetUTCDate())
	DECLARE @TmpHostStopTryPowerOff TABLE(HostId INTEGER, RequestId INTEGER)
	INSERT INTO @TmpHostStopTryPowerOff
	SELECT PH.HostId, VM.RequestId FROM MMPowerMgmtHost PH WITH (NOLOCK)
	INNER JOIN MMVMPowerMgmtReq VM WITH (NOLOCK) ON VM.HostId = PH.HostId
WHERE VM.RequestStatus = 2
	AND (VM.RetryCount = @MaxRetryCount  OR VM.RetryCount > @MaxRetryCount)
AND (VM.Flags & 4) > 0
	AND VM.FailureReason = 1
	UPDATE PH
SET PH.PowerStatus = 2
	FROM MMPowerMgmtHost PH
	INNER JOIN @TmpHostStopTryPowerOff TMP ON TMP.HostId = PH.HostId
	UPDATE VM
	SET VM.FailureReason = 2,
VM.RequestStatus = 3,
	VM.RetryCount = CASE WHEN VM.RetryCount < @MaxRetryCount THEN @MaxRetryCount
					ELSE VM.Retrycount
					END,
	VM.LastUpdateTime = @currentUTCTime
	FROM MMVMPowerMgmtReq VM
	INNER JOIN @TmpHostStopTryPowerOff TMP ON TMP.HostId = VM.HostId AND VM.RequestId = TMP.RequestId
	DELETE TMPH
	FROM @tmpHostTable TMPH
	INNER JOIN @TmpHostStopTryPowerOff TMPSTOP ON TMPH.HostId = TMPSTOP.HostId
-- Processing GUI Request
Update TMP
SET isRequestFromGUI = 1
FROM @tmpHostTable TMP
INNER JOIN MMVMPowerMgmtReq VM WITH (NOLOCK) ON TMP.HostId = VM.HostId
WHERE (VM.Flags & 4) = 4
AND ( VM.RequestStatus = 1 OR (VM.RequestStatus = 2 AND VM.FailureReason = 1) )
INSERT INTO @tmpHostTable
SELECT DISTINCT VM.HostId, 1 FROM MMVMPowerMgmtReq VM WITH (NOLOCK)
WHERE (VM.Flags & 4) = 4
AND ( VM.RequestStatus = 1 OR (VM.RequestStatus = 2 AND VM.FailureReason = 1) )
AND VM.HostId NOT IN (SELECT HostId FROM @tmpHostTable)
SELECT HostId, isRequestFromGUI FROM @tmpHostTable
GO

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

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

insert into GXDBVersions values(2, 'MMGetCloudVMsToPowerOFF',  'v1.1.2.15.54.1', 'MMGetCloudVMsToPowerOFF', 'v1.1.2.15.54.1')
GO

