

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/APPGetFWOutGoingRoutes.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/APPGetFWOutGoingRoutes.sp,v $ $Id: APPGetFWOutGoingRoutes.sp,v 1.8.86.13.20.1 2021/06/16 01:01:11 ybaheti Exp $";
--
--  +========================================================================+
--  |   Procedure:  APPGetFWOutGoingRoutes()
--  |
--  | Description:  xx
--  |
--  |       Input:  xx
--  |
--  |      Output:  Returns list of Firewall Outgoing routes for a
-- 	|				given client.
--  |      Return:  xxx
--  |
--  |   Revisions  Author						Description
--  |   ---------  -------				--------------------------------
--  |   1.1        Bhavyan Mehta   Initial Edit
--  +========================================================================+
--
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='APPGetFWOutGoingRoutes')
	delete from GXDBVersions where aliasname = 'APPGetFWOutGoingRoutes'
GO
print '... Creating Procedure: APPGetFWOutGoingRoutes'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure APPGetFWOutGoingRoutes
  @i_clientId integer
AS
  DECLARE @o_remoteClientId integer
  DECLARE @o_remoteClientName nvarchar(255)
  DECLARE @o_remoteClientHostName nvarchar(255)
  DECLARE @o_remoteClientGUID nvarchar(255)
  DECLARE @o_tunnelPort integer
  DECLARE @o_cvdPort integer
  DECLARE @o_isRoaming integer
  DECLARE @o_additionalPortRanges varchar(256)
  DECLARE @o_fwOutGoingRouteOptions XML
  DECLARE @o_generateNwConfig integer
  DECLARE @o_tunnelPortValid integer
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN
	IF OBJECT_ID('tempdb.dbo.#fwOutGoingRoutesTable') IS NOT NULL
		DROP TABLE #fwOutGoingRoutesTable
	CREATE TABLE #fwOutGoingRoutesTable (
		t_remoteClientId INTEGER ,
		t_fwOutGoingRouteOptions XML
	)
	CREATE CLUSTERED INDEX IDX_FWOutGoingRoutes_RemoteClientID ON #fwOutGoingRoutesTable(t_remoteClientId)
	EXEC AppGetSmartNWTopologyOutGoingRoutes @i_clientId
	-- First insert all the data pertaining to clientgroups
	INSERT INTO #fwOutGoingRoutesTable
		SELECT
			CG.clientId,
			CAST(FW.fwOutGoingRouteOptions AS NVARCHAR(MAX))
		FROM App_FWOutGoingRoutes FW
			 JOIN APP_ClientGroupAssoc CG
ON FW.forClientGroupId = CG.clientGroupId AND FW.flag & 4 <> 4
		WHERE FW.clientId = @i_clientId
		UNION ALL
		SELECT
			FW.forClientId,
			CAST(FW.fwOutGoingRouteOptions AS NVARCHAR(MAX))
		FROM App_FWOutGoingRoutes FW
			 JOIN APP_ClientGroupAssoc CG
ON FW.clientGroupId = CG.clientGroupId AND CG.clientId = @i_clientId AND FW.flag & 4 <> 4
		WHERE FW.forClientId <> 0
		UNION ALL
		SELECT
			CG.clientId,
			CAST(FW.fwOutGoingRouteOptions AS NVARCHAR(MAX))
		FROM App_FWOutGoingRoutes FW
			 JOIN APP_ClientGroupAssoc CG
ON FW.forClientGroupId = CG.clientGroupId AND FW.flag & 4 <> 4
		WHERE FW.clientGroupId IN ( SELECT clientGroupId
									   FROM APP_ClientGroupAssoc CG
									   WHERE CG.clientId = @i_clientId
									 )
		UNION ALL
		-- Insert Data about the client
		SELECT
			forClientId,
			CAST(fwOutGoingRouteOptions AS VARCHAR(MAX))
		FROM App_FWOutGoingRoutes FW
WHERE clientId = @i_clientId and forClientId <> 0 AND FW.flag & 4 <> 4
	-- Get all client's with their firewall options
BEGIN
	SET NOCOUNT ON
	-- Flatten Firewall Options
	-- Note should re-design this table for better performance on PK
	IF OBJECT_ID('tempdb..#FWOptions') IS NOT NULL
		DROP TABLE #FWOptions
	CREATE TABLE #FWOptions
	(
		clientId		INT,
		clientGroupId	INT,
		tunnelPort		INT,
		isRoaming		INT,
		isCfgdAuto		INT,
		cgOrder			BIGINT,		-- firewall options table grouping order for all other configuration data.  Note can be different the Firewall Table ordering.
		PRIMARY KEY (clientGroupId, clientId)
	)
	INSERT INTO #FWOptions
		SELECT
			fo.clientId,
			fo.clientGroupId,
			fo.tunnelconnectionPort,
			fo.isRoamingClient,
			ISNULL(ep.value('@configureAutomatically', 'int'), 0),
			ISNULL(cgo.cgOrder, 0)	-- 0 - client has a firewall option row directly, if client associated by clientgroup want only the lowest ranked number
FROM (SELECT clientId, clientGroupId, tunnelconnectionPort, isRoamingClient, extendedProperties, flag FROM App_FirewallOptions FOpts WHERE (flag=0 OR (flag & (~4)) <> 0)) fo
			CROSS APPLY fo.extendedProperties.nodes('/App_FirewallExtendedProperties') foep(ep)
			LEFT OUTER JOIN (
				-- Rank ordering of client groups so client is only associated with the lowest rank group first for firewall options
				SELECT
					o.clientGroupId,
					RANK() OVER (ORDER BY o.id ASC) cgOrder
				FROM App_FirewallOptions o
				WHERE
o.clientGroupId > 0 AND ( o.flag = 0 OR ((o.flag & (~4)) <> 0))
			) cgo ON
				cgo.clientGroupId = fo.clientGroupId
	-- Get all grouped clients auto configuration / roaming - if client is part of any group with auto cfg roaming on then it is always on matter what group firewall configuration is used!
	IF OBJECT_ID('tempdb..#CfgdAuto') IS NOT NULL
		DROP TABLE #CfgdAuto
	CREATE TABLE #CfgdAuto
	(
		clientId		INT PRIMARY KEY,
		isCfgdAuto		INT,
		isRoamingClient INT,
		tunnelPort		INT
	)
	INSERT INTO #CfgdAuto
		SELECT
			cga.clientId,
			MAX(fwo.isCfgdAuto) isCfgdAuto,		-- if auto cfg on(1)[off(0)] for any group it is the client's default setting
			MAX(fwo.isRoaming) isRoamingClient,  -- if is roaming on(1)[off(0)] for any group, it is the client's default setting
			0
		FROM #FWOptions fwo
			INNER JOIN APP_ClientGroupAssoc cga ON
				fwo.clientGroupId > 0			-- only want group firewall configurations
				AND cga.clientGroupId = fwo.clientGroupId
		GROUP BY
			cga.clientId
	;WITH CA_NAME (clientId, tunnelPort, cgOrder)
	 AS
	(
		SELECT ACGA.clientId, FO.tunnelPort, ROW_NUMBER () over (PARTITION BY ACGA.clientId ORDER BY ACG.id ) cgOrder
		FROM #FWOptions FO
		INNER JOIN APP_ClientGroup ACG
		ON FO.clientGroupId = ACG.id
		INNER JOIN APP_ClientGroupAssoc ACGA
		ON ACGA.clientGroupId=ACG.id
		WHERE FO.isCfgdAuto=0
	)
	UPDATE CA
	SET CA.isCfgdAuto = 0, CA.tunnelPort = CAN.tunnelPort
	FROM #CfgdAuto CA
	INNER JOIN CA_NAME CAN
	ON CA.clientId = CAN.clientId
	WHERE CAN.cgOrder = 1
	-- Create a table with all Client's and their firewall options
	IF object_id('tempdb.dbo.#ClientFWOptions') IS NOT NULL
		DROP TABLE #ClientFWOptions
	CREATE TABLE #ClientFWOptions (
		clientId				INT PRIMARY KEY,
		tunnelPort				INT,
		cvdPort					INT,
		isRoaming				INT,
		additionalPortRanges	VARCHAR(255)	-- comma separated port ranges like:  5000,9000-10000,11000,12000-12001
	)
	DECLARE @defaultTunnelPort INTEGER = 8403
	INSERT INTO #ClientFWOptions (clientId, tunnelPort, cvdPort, isRoaming, additionalPortRanges)
		SELECT
			q.clientId,
			q.tunnelPort,
			q.cvdPort,
			q.isRoaming,
			q.additionalPortRanges
		FROM (
				SELECT
					c.id clientId,
					0 clientGroupId,
					CASE fwo.isCfgdAuto
						WHEN 0 THEN fwo.tunnelPort
						ELSE
							CASE c.cvdPort
								WHEN 0
									THEN @defaultTunnelPort
								ELSE
									c.cvdPort + 3
							END
					END tunnelPort,
					c.cvdPort,
					fwo.isRoaming isRoaming,
					ISNULL(
						pl.portList,
						ISNULL(		-- if individual client not directly configured with additional ports, find parent client group and use those ports instead????
							(
								SELECT
									STUFF((SELECT
											',' + CASE
													WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
													ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
												END
										FROM APP_FWPortRange fwpr
											INNER JOIN App_ClientGroupAssoc cga
											ON fwpr.clientGroupId=cga.clientGroupId
											WHERE cga.clientId=c.id
											GROUP BY
												fwpr.startPort,
												fwpr.endPort
											FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
									)
							),
							''	-- if none found, nothing!
						)
					) additionalPortRanges,
					1 pos		-- direct client configuration is always position 1
				FROM APP_Client c
					INNER JOIN #FWOptions fwo ON
						fwo.clientGroupId = 0
						AND fwo.clientId = c.id
					OUTER APPLY (
						-- Get directly configured client additional ports
						SELECT
							STUFF((SELECT
									',' + CASE
											WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
											ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
										END
								FROM APP_FWPortRange fwpr
								WHERE
									fwpr.clientId = c.id
								GROUP BY
									fwpr.startPort,
									fwpr.endPort
								FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
							) portList
					) pl
				UNION ALL
				SELECT
					c.id clientId,
					fwo.clientGroupId,
					CASE ca.isCfgdAuto		-- determine if auto cfg is on for any group the client is associated with
						WHEN 0 THEN ca.tunnelPort
						ELSE
							CASE c.cvdPort
								WHEN 0
									THEN @defaultTunnelPort
								ELSE
									c.cvdPort + 3
							END
					END tunnelPort,
					c.cvdPort,
					fwo.isRoaming isRoaming,
					ISNULL(pl.portList, '') additionalPortRanges,
					ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY c.id ASC, fwo.cgOrder ASC) pos
				FROM APP_ClientGroupAssoc cga
					INNER JOIN #FWOptions fwo ON
						fwo.clientGroupId > 0
						AND fwo.clientGroupId = cga.clientGroupId
					INNER JOIN APP_Client c ON
						c.id = cga.clientId
						AND c.id NOT IN (SELECT clientId FROM #FWOptions WHERE clientGroupId = 0)		-- does client directly have associated firewall options, than do not include any group associations
					INNER JOIN #CfgdAuto ca ON
						ca.clientId = c.id
					OUTER APPLY (
						-- Get indiectly configed client group additional ports via client????
						SELECT
							STUFF((SELECT
										',' + CASE
												WHEN fwpr.startPort = fwpr.endPort THEN CAST(fwpr.startPort AS VARCHAR(12))
												ELSE CAST(fwpr.startPort AS VARCHAR(12)) + '-' + CAST(fwpr.endPort AS VARCHAR(12))
											END
									FROM APP_FWPortRange fwpr
										INNER JOIN APP_ClientGroupAssoc cga
										ON fwpr.clientGroupId=cga.clientGroupId
										WHERE cga.clientId=c.id
										GROUP BY
											fwpr.startPort,
											fwpr.endPort
										FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, ''
								) portList
					) pl
			) q
		WHERE
			q.pos = 1		-- Want only client position 1 rows, anything greater 1 is thrown away.  Want the earliest client group association configured for firewall options.
		IF OBJECT_ID('tempdb.dbo.#CfgdAuto') IS NOT NULL
			DROP TABLE #CfgdAuto
		IF OBJECT_ID('tempdb.dbo.#FWOptions') IS NOT NULL
			DROP TABLE #FWOptions
END
IF object_id('tempdb.dbo.#tmpResultSet') IS NOT NULL
	DROP TABLE #tmpResultSet
CREATE TABLE #tmpResultSet(
						clientId						integer,
remoteClientName	  			nvarchar(255),
remoteClientHostName	  		nvarchar(255),
remoteClientGUID	  			nvarchar(255),
						tunnelPort		  				integer,
						cvdPort			  				integer,
						isRoaming			  			integer,
						additionalPortRanges 			varchar(256),
						fwOutGoingRouteOptions 			XML,
						generateNwConfig				integer,
						tunnelPortValid					integer
							)
CREATE CLUSTERED INDEX IDX_FWOutGoingRoutes_ResultSet_ON_ClientId ON #tmpResultSet(clientId)
	INSERT INTO #tmpResultSet
	SELECT
		og.t_remoteClientId clientId,
		c.name clientName,
		c.net_hostname,
		case
			when c.releaseId < 15 then NULL
			else c.GUID
		end clientGUID,
		ISNULL(co.tunnelPort, CASE c.cvdPort WHEN 0 THEN @defaultTunnelPort ELSE c.cvdPort + 3 END) tunnelPort,
		ISNULL(co.cvdPort, 0) cvdPort,
		ISNULL(co.isRoaming, 0) isRoaming,
		ISNULL(co.additionalPortRanges, '') additionalPortRanges,
		og.t_fwOutGoingRouteOptions fwOutGoingRouteOptions,
		CASE WHEN SIP.ClientId IS NULL THEN 0 ELSE 1 END,
		CASE WHEN co.tunnelPort IS NULL THEN 0 ELSE 1 END
	FROM #fwOutGoingRoutesTable og
		INNER JOIN APP_Client c ON
			og.t_remoteClientId = c.id
AND c.releaseId >= 14
		LEFT OUTER JOIN #ClientFWOptions co ON		-- can this be changed to an INNER JOIN????
			co.clientId = c.id
		LEFT OUTER JOIN (SELECT ClientId
						FROM simInstalledPackages
						GROUP BY ClientId )  SIP ON
			c.id=SIP.ClientId
	UPDATE #tmpResultSet
	SET generateNwConfig=1
	FROM #tmpResultSet TRS
	INNER JOIN APP_ClientProp ACP
	ON TRS.clientId=ACP.componentNameId
	WHERE (ACP.attrName='Include in Firewall Config' AND ACP.attrVal='1' AND  ACP.attrType=7 /* INTEGER */ AND ACP.modified=0) OR
		   (ACP.attrName='Virtual Client' AND ACP.attrVal='1' AND ACP.modified=0)
-- Output Result Set to caller
	SELECT clientId, remoteClientName, remoteClientHostName, remoteClientGUID, tunnelPort, cvdPort, isRoaming, additionalPortRanges, fwOutGoingRouteOptions, generateNwConfig, tunnelPortValid
	FROM #tmpResultSet
IF object_id('tempdb.dbo.#tmpResultSet') IS NOT NULL
	DROP TABLE #tmpResultSet
IF object_id('tempdb.dbo.#fwOutGoingRoutesTable') IS NOT NULL
	DROP TABLE #fwOutGoingRoutesTable
IF object_id('tempdb.dbo.#ClientFWOptions') IS NOT NULL
	DROP TABLE #ClientFWOptions
END
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'APPGetFWOutGoingRoutes',  'v1.8.86.13.20.1', 'APPGetFWOutGoingRoutes', 'v1.8.86.13.20.1')
GO

