Updating Log Events after update to Nodinite 5.4 or later

This article only applies if you have Logging enabled from BizTalk Server to Nodinite. This fix only needs to be applied once.

Background

There is an invalid translation of two-way Endpoint Directions for Microsoft BizTalk Server with versions before Nodinite 5.4. If you update to 5.4 or later, you may want to correct this. On this page is a template script to help you update the content in the Nodinite Log Databases.

Required steps

Use the Log Audit Search feature to find the data of interest. You must enter the Date when you applied the Nodinite update.

1. Enter the following text in the operations filter:

 List of Endpoint Ids with changed direction

2. Copy the LogAgentValueId and use it in the update script (@logAgentValueId)
3. Copy the comma-separated string with EndpointIds and use it in the update script (@CommadelimitedString).
   Find the required values to use in the update script.

Update script

You need to execute a script to update the Events table in every Log Database.

Replace the parameters as necessary and repeat the operation on all Log Databases.

DECLARE @CommadelimitedString VARCHAR(MAX) = '23,30,33,35' -- Replace from Log Audits search
DECLARE @LogDatabase VARCHAR(255)= 'NodiniteLog_Test72_20220114' -- Replace with the name of the target Nodinite Log Database
DECLARE @logAgentValueId INT = 1 -- VERY IMPORTANT THAT YOU SET THIS ACCORDINGLY, CONTACT THE NODINITE SUPPORT IF YOU ARE UNCERTAIN

-- Loop over all changed Endpoints
-- Find the current EndpointDirection
-- Update the Events table with the new direction for the Endpoint from the list in cursor loop

DECLARE @ChangedEndpoints TABLE (EndpointId BIGINT)

DECLARE @IntLocation INT
    WHILE (CHARINDEX(',', @CommadelimitedString, 0) > 0)
    BEGIN
            SET @IntLocation =   CHARINDEX(',',   @CommadelimitedString, 0)      
            INSERT INTO @ChangedEndpoints (EndpointId)
            --LTRIM and RTRIM to ensure blank spaces are   removed
            SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,  0, @IntLocation)))   
            SET @CommadelimitedString = STUFF(@CommadelimitedString,  1, @IntLocation,  '') 
    END
    INSERT INTO   @ChangedEndpoints (EndpointId)
    SELECT RTRIM(LTRIM(@CommadelimitedString))

DECLARE @rowCount INT
DECLARE @tsql NVARCHAR(MAX)
DECLARE @direction INT
DECLARE @EndpointId INT
DECLARE Endpoints_Cursor CURSOR FOR SELECT [EndpointId] FROM @ChangedEndpoints ORDER BY [EndpointId] DESC
	-- Open the cursor to loop through all rows.
	OPEN Endpoints_Cursor;
	FETCH NEXT FROM Endpoints_Cursor INTO @EndpointId
	WHILE @@FETCH_STATUS = 0
		BEGIN
		
		-- Find current direction for Endpoint
		SELECT @direction = E.Direction from EndPoints E WHERE  E.[EndPointId] = @EndpointId

		PRINT 'Updating Events for Endpoints with EndpointId: ' + CAST(@EndpointId AS VARCHAR(18)) + '. New Direction is: ' + CAST(@direction AS VARCHAR(18))
				
		SET @tsql = 'UPDATE ' + @LogDatabase + '.dbo.Events SET EndPointDirection= ' +  CAST(@direction AS VARCHAR(18)) + 
			' WHERE EventId IN (SELECT TOP 50000 EventId FROM ' + @LogDatabase + '.dbo.[Events] WHERE EndPointId = ' + CAST(@EndpointId AS VARCHAR(18)) 
			+ ' AND EndPointDirection <> ' + CAST(@direction AS VARCHAR(18)) + ' AND AgentSourceId=' + CAST(@logAgentValueId AS VARCHAR(18)) + ')'
		PRINT @tsql
		WHILE 1 = 1 
		BEGIN				
			EXEC sp_executesql @tsql
			SET @rowCount = @@ROWCOUNT
									
			PRINT 'Updated ' + CAST(@rowCount AS VARCHAR(18)) + ' rows in the Events table.'

			IF @rowCount <> 50000
				BREAK
		END
		FETCH NEXT FROM Endpoints_Cursor INTO @EndpointId
		END;
	CLOSE Endpoints_Cursor;
DEALLOCATE Endpoints_Cursor;

Modify Log Views

If you use the Direction search field in Log Views, you may need to modify them to include the Two-Way direction instead.

Two-Way Receive

• If you update the Log Databases with the script, change Receive to Two-Way Receive.
• If you do NOT update Log Databases with the script, Add the mathematical operator from = to IN and make sure to include both Receive and Two-Way Receive.
  
  Example when you did not modify the Log Database.

Two-Way Send

• If you update the Log Databases with the script, change Send to Two-Way Send.
• If you do NOT update Log Databases with the script, Add the mathematical operator from = to IN and make sure to include both Send and Two-Way Send.
  
  Example when you did not modify the Log Database.

---

Next Step

Log Events
BizTalk Logging Overview

Related

Update Nodinite
Log Databases