-- ====================================================================================================================================================
-- Name:        Merge_Duplicate_Nodes_v0.9.3.sql
-- Author:      Intel
-- Author date: 04NOV24
-- Description:	SQL script to merge duplicate endpoints in Intel EMA.  Endpoints are identified by the Hwid in newer versions or by name in older
--              versions of Intel EMA, collected and merged if duplicates are found.
-- ====================================================================================================================================================
-- Variable descriptions:
--     PriEpIdKey: sets the primary value to use for duplicate record identification
--     SecEpIdKey: sets the secondary value to use for duplicate record identification
--     PriSqlQuery: SQL query string for the primary endpoint ID value
--     SecSqlQuery: SQL query string for the secondary endpoint ID value
--     EmaVer: Current version of the Intel EMA agents in the database
--     EmaMin: Minimum version of Intel EMA required to execute the secondary method
--     ForceDate: Past date to force endpoint configuration to the top of the manageability server queue and restore CIRA connectivity more quickly
-- ====================================================================================================================================================
DECLARE @PriEpIdKey NVARCHAR(max),
        @SecEpIdKey NVARCHAR(max),
        @PriSqlQuery NVARCHAR(max),
        @SecSqlQuery NVARCHAR(max),
        @EmaVer INT,
        @EmaMin INT,
        @ForceDate AS DATE = (DATEADD(YEAR, -1, GETDATE()))
-- ====================================================================================================================================================
-- Check the Intel EMA version based on the version of the endpoint agent in the database and conditionally set the endpoint record fields to query to
-- facilitate script schema evaluation at the time of execution
--
-- Only the first method will execute on legacy versions of Intel EMA but the value for the field to query is set redundantly to mitigate schema
-- evaluation errors
-- ====================================================================================================================================================
SET @EmaMin = 11400
SELECT TOP (1) @EmaVer = [VERSION] FROM [dbo].[MeshAgents]
IF @EmaVer >= @EmaMin
  BEGIN
    SET @PriEpIdKey = N'Hwid'
    SET @SecEpIdKey = N'ComputerName'
  END
ELSE
  BEGIN
    SET @PriEpIdKey = N'ComputerName'
    SET @SecEpIdKey = N'ComputerName'
  END
-- ====================================================================================================================================================
-- Execute using the endpoint record value set above for all Intel EMA versions
-- Intel EMA v1.14 and newer will use the HwId value as the unique identifier for endpoints but will execute a second time using the ComputerName value
-- Intel EMA versions prior to v1.14 will use the ComputerName as the unique identifier and only execute one time
-- ====================================================================================================================================================
-- NOTE: Duplicate records for endpoints with hostnames that have changed will not be identified unless Intel EMA is v1.14 or newer and had been online
-- after the server had been upgraded
-- ====================================================================================================================================================
IF OBJECT_ID(N'tempdb..#Temp_UniqueEndpointsPrimary') IS NOT NULL
  BEGIN
    DROP TABLE #Temp_UniqueEndpointsPrimary
  END

IF OBJECT_ID(N'tempdb..#Temp_DuplicateEndpointsPrimary') IS NOT NULL
   BEGIN
     DROP TABLE #Temp_DuplicateEndpointsPrimary
   END

SELECT @PriSqlQuery = N'SELECT '+QUOTENAME(@PriEpIdKey)+', NodeIdentity, NodeId, UpdateTime
  INTO #Temp_UniqueEndpointsPrimary
  FROM Nodes,
    (SELECT MAX(NodeIdentity) AS EndpointID
    FROM Nodes
    GROUP BY '+QUOTENAME(@PriEpIdKey)+') TopNodes, 
      (SELECT '+QUOTENAME(@PriEpIdKey)+' AS EndpointComputer, COUNT('+QUOTENAME(@PriEpIdKey)+') AS Duplicate
      FROM Nodes
      GROUP BY '+QUOTENAME(@PriEpIdKey)+'
        HAVING COUNT('+QUOTENAME(@PriEpIdKey)+') > 1) EndpointDups
  WHERE Nodes.NodeIdentity = TopNodes.EndpointID
    AND Nodes.'+QUOTENAME(@PriEpIdKey)+' = EndpointDups.EndpointComputer;

SELECT Nodes.'+QUOTENAME(@PriEpIdKey)+', Nodes.NodeIdentity, Nodes.NodeId, Nodes.UpdateTime, UniqueEP.NodeId AS UniqueId, 
  DupCount = ROW_NUMBER() OVER (PARTITION BY Nodes.'+QUOTENAME(@PriEpIdKey)+' ORDER BY Nodes.LastUpdate)
  INTO #Temp_DuplicateEndpointsPrimary
  FROM Nodes, #Temp_UniqueEndpointsPrimary AS UniqueEP
  WHERE Nodes.'+QUOTENAME(@PriEpIdKey)+' = UniqueEP.'+QUOTENAME(@PriEpIdKey)+'
    AND Nodes.NodeIdentity NOT IN (SELECT NodeIdentity FROM #Temp_UniqueEndpointsPrimary)
  ORDER BY Nodes.NodeId;

DELETE FROM [Manageability].[Amt8021XSetup_IntelAmtSetup]
  WHERE NodeID in (SELECT NodeID FROM #Temp_DuplicateEndpointsPrimary);

DELETE FROM [dbo].[UsbrSession]
  WHERE EndpointId in (SELECT NodeID FROM #Temp_DuplicateEndpointsPrimary);

UPDATE EndpointConnectInfo
  SET EndpointConnectInfo.NodeID = Duplicates.UniqueId
  FROM #Temp_DuplicateEndpointsPrimary AS Duplicates
  WHERE EndpointConnectInfo.NodeID = Duplicates.NodeID
    AND Duplicates.DupCount = 1
    AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM EndpointConnectInfo);

UPDATE AmtCertMap
  SET AmtCertMap.NodeID = Duplicates.UniqueId
  FROM #Temp_DuplicateEndpointsPrimary AS Duplicates
  WHERE AmtCertMap.NodeID = Duplicates.NodeID
    AND Duplicates.DupCount = 1
    AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM AmtCertMap);

UPDATE IntelAmtSetup
  SET IntelAmtSetup.NodeID = Duplicates.UniqueId, IntelAmtSetup.LastSetup = Duplicates.UpdateTime, IntelAmtSetup.LastAttempt = @ForceDate, IntelAmtSetup.AdoptionState = 1, IntelAmtSetup.State = 2
  FROM #Temp_DuplicateEndpointsPrimary AS Duplicates
  WHERE IntelAmtSetup.NodeID = Duplicates.NodeID
    AND Duplicates.DupCount = 1
    AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM IntelAmtSetup);

DELETE FROM IntelAmt
  WHERE IntelAmt.NodeID IN (SELECT NodeId  FROM #Temp_DuplicateEndpointsPrimary);

DELETE FROM Nodes
  WHERE Nodes.NodeID IN (SELECT NodeId FROM #Temp_DuplicateEndpointsPrimary);

SELECT '+QUOTENAME(@PriEpIdKey)+' FROM #Temp_DuplicateEndpointsPrimary'

EXECUTE sp_executesql @PriSqlQuery, N'@ForceDate AS DATE', @ForceDate

IF OBJECT_ID(N'tempdb..#Temp_UniqueEndpointsPrimary') IS NOT NULL
  BEGIN
    DROP TABLE #Temp_UniqueEndpointsPrimary
  END

IF OBJECT_ID(N'tempdb..#Temp_DuplicateEndpointsPrimary') IS NOT NULL
   BEGIN
     DROP TABLE #Temp_DuplicateEndpointsPrimary
   END

-- ====================================================================================================================================================
-- Second execution using the original method of ComputerName to catch legacy endpoint records that do not have a HW ID but the hostname is the same as
-- the original endpoint record
-- This secondary method will only execute if the Intel EMA version is v1.14 or newer as identified with the agent version evaluation above
-- ====================================================================================================================================================
IF @EmaVer >= @EmaMin
  BEGIN
  IF OBJECT_ID(N'tempdb..#Temp_UniqueEndpointsSecondary') IS NOT NULL
    BEGIN
      DROP TABLE #Temp_UniqueEndpointsSecondary
    END

  IF OBJECT_ID(N'tempdb..#Temp_DuplicateEndpointsSecondary') IS NOT NULL
     BEGIN
       DROP TABLE #Temp_DuplicateEndpointsSecondary
     END

  SELECT @SecSqlQuery = N'SELECT '+QUOTENAME(@SecEpIdKey)+', NodeIdentity, NodeId, UpdateTime
    INTO #Temp_UniqueEndpointsSecondary
    FROM Nodes,
      (SELECT MAX(NodeIdentity) AS EndpointID
      FROM Nodes
      GROUP BY '+QUOTENAME(@SecEpIdKey)+') TopNodes, 
        (SELECT '+QUOTENAME(@SecEpIdKey)+' AS EndpointComputer, COUNT('+QUOTENAME(@SecEpIdKey)+') AS Duplicate
        FROM Nodes
        GROUP BY '+QUOTENAME(@SecEpIdKey)+'
          HAVING COUNT('+QUOTENAME(@SecEpIdKey)+') > 1) EndpointDups
    WHERE Nodes.NodeIdentity = TopNodes.EndpointID
      AND Nodes.'+QUOTENAME(@SecEpIdKey)+' = EndpointDups.EndpointComputer;

  SELECT Nodes.'+QUOTENAME(@SecEpIdKey)+', Nodes.NodeIdentity, Nodes.NodeId, Nodes.UpdateTime, UniqueEP.NodeId AS UniqueId, 
    DupCount = ROW_NUMBER() OVER (PARTITION BY Nodes.'+QUOTENAME(@SecEpIdKey)+' ORDER BY Nodes.LastUpdate)
    INTO #Temp_DuplicateEndpointsSecondary
    FROM Nodes, #Temp_UniqueEndpointsSecondary AS UniqueEP
    WHERE Nodes.'+QUOTENAME(@SecEpIdKey)+' = UniqueEP.'+QUOTENAME(@SecEpIdKey)+'
      AND Nodes.NodeIdentity NOT IN (SELECT NodeIdentity FROM #Temp_UniqueEndpointsSecondary)
    ORDER BY Nodes.NodeId;
  DELETE FROM [Manageability].[Amt8021XSetup_IntelAmtSetup]
    WHERE NodeID in (SELECT NodeID FROM #Temp_DuplicateEndpointsSecondary);

  DELETE FROM [dbo].[UsbrSession]
    WHERE EndpointId in (SELECT NodeID FROM #Temp_DuplicateEndpointsSecondary);

  UPDATE EndpointConnectInfo
    SET EndpointConnectInfo.NodeID = Duplicates.UniqueId
    FROM #Temp_DuplicateEndpointsSecondary AS Duplicates
    WHERE EndpointConnectInfo.NodeID = Duplicates.NodeID
      AND Duplicates.DupCount = 1
      AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM EndpointConnectInfo);

  UPDATE AmtCertMap
    SET AmtCertMap.NodeID = Duplicates.UniqueId
    FROM #Temp_DuplicateEndpointsSecondary AS Duplicates
    WHERE AmtCertMap.NodeID = Duplicates.NodeID
      AND Duplicates.DupCount = 1
      AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM AmtCertMap);

  UPDATE IntelAmtSetup
    SET IntelAmtSetup.NodeID = Duplicates.UniqueId, IntelAmtSetup.LastSetup = Duplicates.UpdateTime, IntelAmtSetup.LastAttempt = @ForceDate, IntelAmtSetup.AdoptionState = 1, IntelAmtSetup.State = 2
    FROM #Temp_DuplicateEndpointsSecondary AS Duplicates
    WHERE IntelAmtSetup.NodeID = Duplicates.NodeID
      AND Duplicates.DupCount = 1
      AND Duplicates.UniqueId NOT IN (SELECT NodeId FROM IntelAmtSetup);

  DELETE FROM IntelAmt
    WHERE IntelAmt.NodeID IN (SELECT NodeId  FROM #Temp_DuplicateEndpointsSecondary);

  DELETE FROM Nodes
    WHERE Nodes.NodeID IN (SELECT NodeId FROM #Temp_DuplicateEndpointsSecondary);

SELECT '+QUOTENAME(@SecEpIdKey)+' FROM #Temp_DuplicateEndpointsSecondary'

  EXECUTE sp_executesql @SecSqlQuery, N'@ForceDate AS DATE', @ForceDate
  PRINT 'Used: '+@SecEpIdKey

  IF OBJECT_ID(N'tempdb..#Temp_UniqueEndpointsSecondary') IS NOT NULL
    BEGIN
      DROP TABLE #Temp_UniqueEndpointsSecondary
    END

  IF OBJECT_ID(N'tempdb..#Temp_DuplicateEndpointsSecondary') IS NOT NULL
     BEGIN
       DROP TABLE #Temp_DuplicateEndpointsSecondary
     END
  END