SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON

DECLARE @ScriptName		NVARCHAR(128)	= 'Check for software version mismatch between pseudo clients and their associated physical clients '
DECLARE @ErrorTag		NVARCHAR(20)	= '###ERROR### '
DECLARE @WarningTag		NVARCHAR(20)	= '###WARNING### '
DECLARE @AdviceTag		NVARCHAR(20)	= '###ADVICE### '
DECLARE @DescTag 		NVARCHAR(20)	= '###DESCRIPTION###'
DECLARE @HeaderTag		NVARCHAR(20)	= '###HEADER###'
DECLARE @DataTag		NVARCHAR(20)	= '###DATA###'
DECLARE @colSeperator		NVARCHAR(20)	= ':,:'
DECLARE @retCode		INT		= 0

DECLARE @clients		TABLE (t_pseudoClientId INT, t_appType INT, t_physicalClientId INT)
DECLARE @pseudoApptypes		TABLE (t_AgentName VARCHAR(256), t_apptype INT, t_status INT, t_attrName VARCHAR(256))
INSERT INTO @pseudoApptypes VALUES
	('Non-NDMP Filer',	33,	0,		'Turbo NAS Proxy Client'),
	('OpenVMS',		38,	0,		'Open VMS proxy client'),
	('DAG (MS EXCH DB)',	53,	0x200000,	''),
	('SharePoint Server',	78,	0,		'SharePoint Primary Member Server'),
	('Oracle RAC',		80,	0x0040,		''),
	('MSSQL',		81,	0x20000000,	'Proxy Client Id'),
	('Object Link',		124,	0,		'Object Server Member Servers List'),
	('SAP HANA',		135,	0x2000000,	'Database Clients'),
	('DB2 DPF',		103,	0x0200,		'DB2 Partition Clients'),	
	('Hyper-V',		106,	0,		'Vs Member Servers')
--,	('Distributed Apps',	64,	0,		'Distributed iDA Client')

DECLARE @clientCnt		INT = 0
DECLARE @agentType		NVARCHAR(64)
DECLARE @pseudoClient		NVARCHAR(256)
DECLARE @pseudoRelease		NVARCHAR(64)
DECLARE @physicalClient		NVARCHAR(256)
DECLARE @physicalRelease	NVARCHAR(64)


PRINT @DescTag + ' Check for software version mismatch between pseudo clients and their associated physical clients'

DECLARE @xmlProp		TABLE (t_cid INT, t_attrName VARCHAR(MAX), t_attrVal NVARCHAR(MAX))
INSERT INTO @xmlProp SELECT DISTINCT componentNameId, attrName, attrVal FROM APP_INSTANCEPROP WHERE attrName IN ('Availability Group', 'Availability Replicas', 'Vs Member Servers') AND modified = 0

DECLARE @VMClients		TABLE (t_ID INT, t_clientID INT, t_clientGroupID INT)
INSERT INTO @VMClients
SELECT	distinct A.clientID, ISNULL(P.value('@clientId', 'int'), 0), ISNULL(P.value('@clientGroupId', 'int'), 0)
FROM	APP_Application A, @xmlProp XP CROSS APPLY (select cast(t_attrval as xml)) as X(X) CROSS APPLY X.nodes('App_MemberServers/memberServers/client') as P(P)
WHERE	A.instance = XP.t_cid AND XP.t_attrName ='Vs Member Servers'

DECLARE @SQLClients		TABLE (t_clientID INT, t_PhysicalClientID INT)
INSERT INTO @SQLClients
SELECT	distinct A.clientID, P.value('@clientId', 'int')
FROM	APP_Application A, @xmlProp XP CROSS APPLY (select cast(t_attrval as xml)) as X(X) CROSS APPLY X.nodes('App_SQLAvailabilityGroup/primaryReplicaClient') as P(P)
WHERE	A.instance = XP.t_cid AND XP.t_attrName = 'Availability Group'
UNION
SELECT	distinct A.clientID, P.value('@clientId', 'int')
FROM	APP_Application A, @xmlProp XP CROSS APPLY (select cast(t_attrval as xml)) as X(X) CROSS APPLY X.nodes('App_SQLAvailabilityReplicas/SQLAvailabilityReplicasList/replicaClient') as P(P)
WHERE	A.instance = XP.t_cid AND XP.t_attrName = 'Availability Replicas'

-- Determine where SharePoint 'SharePoint Member Servers' client properties are being stored for PreUpgrade Check
DECLARE @SharePointMSLocation INT = 0	-- 0:Unknown, 1:APP_ClientProp, 2:APP_ComponentProp
-- Original Location
SELECT TOP 1 
	@SharePointMSLocation = 1
FROM APP_ClientProp CP WITH(NOLOCK)
WHERE
	CP.attrName = N'SharePoint Member Servers'
IF (@SharePointMSLocation <> 1)
BEGIN
	-- New Location
	SELECT TOP 1 
		@SharePointMSLocation = 2
	FROM 
		-- Relocated 'SharePoint Member Servers' XML Document from APP_ClientProp to APP_ComponentProp Table since attrVal limited to max data size of NVARCHAR(1024)
		APP_ComponentProp CP WITH (NOLOCK) 
	WHERE 
		CP.componentType = 3			-- CV_COMPONENT_TYPE_CLIENT
		AND CP.propertyTypeId = 1046	-- CV_COMPONENT_SHAREPOINT_SECONDARY_MEMBER_SERVERS		-- Old APP_ClientProp 'SharePoint Member Servers'
END


---------------------
-- Get pseudo/physical clients
INSERT INTO @clients
	--DAG: attrname = physical client host name; PROPERTY_DAG_MEMBERSERVER = 125
SELECT	distinct P.componentNameId, t_apptype, ISNULL((select id from APP_CLient where net_hostname = P.attrname), 0)
FROM	@pseudoApptypes, APP_ClientProp P
WHERE	t_AgentName = 'DAG (MS EXCH DB)' AND P.attrType = 125 AND P.modified = 0

UNION	--SAP HANA
SELECT	distinct A.clientId, t_apptype, T.data
FROM	@pseudoApptypes, APP_Application A, APP_InstanceProp P CROSS APPLY dbo.SplitString(P.attrVal, '+') T
WHERE	t_AgentName = 'SAP HANA' AND A.appTypeID = t_apptype AND A.instance = P.componentNameId AND P.attrName = t_attrName and P.modified = 0

UNION	--DB2 DPF
SELECT	distinct A.clientId, t_apptype, substring(T.Data, CHARINDEX(',', T.Data)+1, LEN(T.Data))
FROM	@pseudoApptypes, APP_Application A, APP_InstanceProp P CROSS APPLY dbo.SplitString(P.attrVal, ' ') T
WHERE	t_AgentName = 'DB2 DPF' AND A.appTypeID = t_apptype AND A.instance = P.componentNameId AND P.attrName = t_attrName and P.modified = 0

UNION	--Oracle RAC
SELECT	distinct A.clientId, t_apptype, I.clientId
FROM	@pseudoApptypes, APP_Application A, APP_OracleRacInstance I
WHERE	A.appTypeID = t_apptype AND A.instance = I.instaceId

UNION	--MSSQL1
SELECT	distinct A.clientId, t_apptype, P.attrVal
FROM	@pseudoApptypes, APP_Application A, APP_InstanceProp P
WHERE	t_AgentName = 'MSSQL' AND A.instance = P.componentNameId AND P.attrName = t_attrName AND P.modified = 0
UNION	--MSSQL2
SELECT	distinct t_clientID, t_apptype, t_PhysicalClientID
FROM	@pseudoApptypes, @SQLClients
WHERE	t_AgentName = 'MSSQL'

UNION	--Hyper-V
SELECT	distinct t_ID, t_apptype, CASE t_clientID WHEN 0 THEN G.clientID ELSE t_clientID END
FROM	@pseudoApptypes T, @VMClients C LEFT OUTER JOIN APP_ClientGroupAssoc G ON C.t_clientGroupID = G.clientGroupId
WHERE	t_AgentName = 'Hyper-V' AND (t_clientID > 0 OR t_clientGroupID > 0)

UNION	--Non-NDMP Filer
SELECT	distinct A.clientId, t_apptype, P.attrVal
FROM	@pseudoApptypes, APP_Application A, App_SubClientProp P
WHERE	t_AgentName = 'Non-NDMP Filer' AND A.Id = P.componentNameId AND P.attrName = t_attrName AND P.modified = 0

UNION	--OpenVMS, SharePoint1
SELECT	distinct componentNameId, t_apptype, attrVal
FROM	@pseudoApptypes, APP_ClientProp
WHERE	t_AgentName IN ('OpenVMS', 'SharePoint Server') AND attrName = t_attrName AND modified = 0

UNION	--SharePoint2

SELECT	distinct componentNameId, t_apptype, p.value('@clientId', 'int')
FROM	@pseudoApptypes, APP_ClientProp CP CROSS APPLY (select cast(attrVal as xml)) as X(X) CROSS APPLY X.nodes('App_MemberServers/memberServers/client') as P(P)
WHERE	t_AgentName = 'SharePoint Server' AND attrName = 'SharePoint Member Servers' AND modified = 0
		AND @SharePointMSLocation = 1
UNION
SELECT DISTINCT 
	CP.componentId, 
	pat.t_apptype, 
	P.value('@clientId', 'int')
FROM @pseudoApptypes pat,
	-- Relocated 'SharePoint Member Servers' XML Document from APP_ClientProp to APP_ComponentProp Table since attrVal limited to max data size of NVARCHAR(1024)
	APP_ComponentProp CP WITH (NOLOCK) 
	CROSS APPLY (SELECT CAST(CP.stringVal as xml)) AS X(X) 
	CROSS APPLY X.nodes('App_MemberServers/memberServers/client') AS P(P)
WHERE 
	pat.t_AgentName = 'SharePoint Server' 
	AND CP.componentType = 3		-- CV_COMPONENT_TYPE_CLIENT
	AND CP.propertyTypeId = 1046	-- CV_COMPONENT_SHAREPOINT_SECONDARY_MEMBER_SERVERS		-- Old APP_ClientProp 'SharePoint Member Servers'
	AND LEN(CP.stringVal) > 0 
	AND CP.modified = 0
	AND @SharePointMSLocation = 2

UNION	--Object Link
SELECT	distinct T._ID, t_apptype, P.componentNameId
FROM	@pseudoApptypes, APP_ClientProp P CROSS APPLY dbo.SplitIDString(replace(P.attrVal,':',',')) T
WHERE	t_AgentName = 'Object Link' AND P.attrName = t_attrName AND P.modified = 0

----------------------------------
-- return client list
DECLARE pseudoPhysicalClientCur CURSOR LOCAL FORWARD_ONLY FOR 
SELECT	t1.t_AgentName, p1.name, (select release from simAllGalaxyRel where id = p1.releaseID), p2.name, (select release from simAllGalaxyRel where id = p2.releaseID)
FROM	@pseudoApptypes t1, @clients t2, APP_Client p1, APP_Client p2
WHERE	t1.t_appType = t2.t_apptype AND t_pseudoClientId = p1.id AND t_physicalClientId = p2.id and p1.releaseID <> p2.releaseID
ORDER BY t1.t_appType, p1.name, p2.name

OPEN pseudoPhysicalClientCur
FETCH FROM pseudoPhysicalClientCur INTO @agentType, @pseudoClient, @pseudoRelease, @physicalClient, @physicalRelease
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @clientCnt = @clientCnt + 1

	IF @clientCnt = 1
	BEGIN
		SET @retCode = 1
		PRINT  @WarningTag + ' The following pseudo clients and their corresponding physical clients do not have the same software version:'
		PRINT  @HeaderTag + ' Type' + @colSeperator + ' Pseudo Client' + @colSeperator + 'Release' + @colSeperator + ' Physical Client' + @colSeperator + ' Release'
		PRINT  @AdviceTag + 'A pseudo client and its corresponding physical client(s) must have the same software version. Upgrade the appropriate clients to fulfill this requirement.'
	END

	PRINT @DataTag + @agentType + @colSeperator + @pseudoClient + @colSeperator + @pseudoRelease  + @colSeperator + @physicalClient + @colSeperator + @physicalRelease
	
	FETCH FROM pseudoPhysicalClientCur INTO @agentType, @pseudoClient, @pseudoRelease, @physicalClient, @physicalRelease
END
CLOSE pseudoPhysicalClientCur
DEALLOCATE pseudoPhysicalClientCur

IF @retCode = 0
	PRINT @ScriptName + 'passed'
	
SELECT @retCode