Nederlands (Nederland)English (United Kingdom)



TSQL: Convert IP address from log file TMG server

When reading in the log file of TMG server, the IP numbers are stored in a unique identifier. To convert this, use this function.

CREATE FUNCTION [dbo].[ufn_getIPAddr](@nIP uniqueidentifier) RETURNS nvarchar(20)
AS
BEGIN
DECLARE @binIP varbinary(4) 
DECLARE @h1 varbinary(1)
DECLARE @h2 varbinary(1)
DECLARE @h3 varbinary(1)
DECLARE @h4 varbinary(1)declare @strIP nvarchar(20)

SELECT @binIP = CONVERT(VARBINARY(4),@nIP )
SELECT @h1 = SUBSTRING(@binIP,1,1)
SELECT @h2 = SUBSTRING(@binIP,2,1)
SELECT @h3 = SUBSTRING(@binIP,3,1)
SELECT @h4 = SUBSTRING(@binIP,4,1)
SELECT @strIP = CONVERT(nvarchar(3),CONVERT(int,@h4))+ '.' + CONVERT(nvarchar(3),CONVERT(int,@h3)) + '.' + CONVERT(nvarchar(3),CONVERT(int,@h2)) + '.' + CONVERT(nvarchar(3),CONVERT(int,@h1))

RETURN @strIP
END 

select ufn_getipaddr(ClientIP) [clientIP] from webproxylog













Copyright © 2017 Eamonn Computer Consultancy. All Rights Reserved.