DDL Changes Log additional changes in script to get client IP

DDLChangeLogServiceBrokerClientIP.sql

GO
USE [ConnectDBA]
GO
/*Alter table to add new column to hold client machine IP*/
ALTER TABLE [dbo].[DDLChangeLog] ADD [ClientMachineIP] [varchar](20) NULL

GO
--Create a stored procedure which will hold logic, how to get data from queue and insert to DDLChangeLog table.
CREATE PROCEDURE [ConnectDBA_Queue_EventNotificatier]
    WITH EXECUTE AS OWNER
AS 
    DECLARE @message_body XML 
 
    WHILE ( 1 = 1 )
        BEGIN
            BEGIN TRANSACTION
      -- Receive the next available message FROM the queue
            WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time
                  @message_body = CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
                FROM dbo.[ConnectDBA_EventNotificationQueue] ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
      -- If we didn't get anything, bail out
            IF ( @@ROWCOUNT = 0 ) 
                BEGIN
                    ROLLBACK TRANSACTION
                    BREAK
                END 
            INSERT  INTO DDLChangeLog
                    SELECT  @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
                                                'varchar(128)') AS EventType,
                            CONVERT(DATETIME, @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)'))
                            AS PostTime,
                            @message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
                                                'varchar(128)') AS ServerName,
                            @message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
                                                'varchar(128)') AS LoginName,
                            @message_body.value('(/EVENT_INSTANCE/UserName)[1]',
                                                'varchar(128)') AS UserName,
                            @message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
                                                'varchar(128)') AS DatabaseName,
                            @message_body.value('(/EVENT_INSTANCE/SchemaName)[1]',
                                                'varchar(128)') AS SchemaName,
                            @message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
                                                'varchar(128)') AS ObjectName,
                            @message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
                                                'varchar(128)') AS ObjectType,
                            @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                                'nvarchar(max)') AS TSQLCommand,
--- To get client machine IP                                                
                            (SELECT client_net_address 
  								FROM sys.dm_exec_connections 
									WHERE Session_id =@message_body.value('(/EVENT_INSTANCE/SPID)[1]','varchar(100)'))AS ClientMachine
            
            COMMIT TRANSACTION
        END
GO