A Few Notes Regarding Remote SQL Logging with Microsoft ISA Server 2006&Forefront Threat Management

[LEFT]In response to my [URL="http://tmgblog.richardhicks.com/2009/05/29/remote-sql-logging-with-microsoft-isa-server-2006/"]last blog post[/URL] disucssing remote SQL logging for Microsoft ISA Server, several people asked about the data stored in the IP address fields of the ISA 2006 log database.
[URL="http://richardhicks.files.wordpress.com/2009/06/log_isa2006.png"][IMG]http://richardhicks.files.wordpress.com/2009/06/log_isa2006.png?w=450&h=75[/IMG][/URL]ISA 2006 IP Address Fields

Clearly these are not IP addresses. In ISA versions up to and including ISA Server 2004, the data type for these fields were [URL="http://msdn.microsoft.com/en-us/library/ms176089.aspx"]varchar(32)[/URL] and contained IP addresses in the familiar dotted decimal notation.
[URL="http://richardhicks.files.wordpress.com/2009/06/log_isa2004.png"][IMG]http://richardhicks.files.wordpress.com/2009/06/log_isa2004.png?w=449&h=80[/IMG][/URL]ISA 2004 IP Address Fields

Beginning with ISA Server 2006, the data type for these fields were changed to [URL="http://msdn.microsoft.com/en-us/library/ms187745.aspx"]bigint[/URL]. In order to convert the data from these fields to the more familiar dotted decimal notation, use the following SQL function [I](adapted from [URL="http://support.microsoft.com/?kbid=891223"]this KB article[/URL])[/I]:
[B]
CREATE FUNCTION [dbo].[fnConvertIPToText] (
@ISALogIPAddress [bigint]
)
RETURNS varchar(15) AS
BEGIN[/B]
[B] DECLARE @ConvertedAddress varchar(15)
SET @ConvertedAddress =[/B]
[B]CAST(@ISALogIPAddress / 256 / 256 / 256 % 256 AS VARCHAR) + ‘.’ + CAST(@ISALogIPAddress / 256 / 256 % 256 AS VARCHAR) + ‘.’ + CAST(@ISALogIPAddress / 256 % 256 AS VARCHAR) + ‘.’ + CAST(@ISALogIPAddress % 256 AS VARCHAR)[/B]
[B] RETURN @ConvertedAddress
END
[/B]
[Download script [URL="http://www.box.net/shared/aoo4r5iyy7"]here[/URL].]
[I](Special thanks to my good friend Christopher Schau, DBA extraordinaire, for creating this function for me!)[/I]
When selecting data from the table, use the function created above to convert the data follows:
[B]
SELECT
LogTime,
dbo.fnConvertIPToText(SourceIP) AS [SourceIP],
dbo.fnConvertIPToText(DestinationIP) AS [DestinationIP],
dbo.fnConvertIPToText(OriginalClientIP) AS [OriginalClientIP]
FROM
firewalllog
[/B]
Data from the IP address fields returned from this query will now appear in the familiar dotted decimal notation.
[URL="http://richardhicks.files.wordpress.com/2009/06/log_isa2006_converted.png"][IMG]http://richardhicks.files.wordpress.com/2009/06/log_isa2006_converted.png?w=450&h=90[/IMG][/URL]ISA 2006 IP Address Fields - Converted

Looking ahead to Microsoft Forefront Threat Management Gateway (TMG), the developers have once again changed the data type for IP address fields. In TMG, the data type for these fields were changed to [URL="http://msdn.microsoft.com/en-us/library/ms187942.aspx"]uniqueidentifier[/URL]. This was done in order to support IPv6 entries in these fields. In order to convert the data from these fields in to the more familiar dotted decimal notation, use the following SQL function:
[B]
CREATE FUNCTION [dbo].[fnIpAddressToText]
(
@Ipv6Address [uniqueidentifier]
)
RETURNS varchar(40) AS
BEGIN
DECLARE @strInAddress varchar(40)
DECLARE @strOutAddress varchar(40)
SET @strInAddress = LOWER(CONVERT(varchar(40), @Ipv6Address))
SET @strOutAddress = ”[/B]
[B] IF (SUBSTRING(@strInAddress, 10, 4) = ‘ffff’)
BEGIN
— ipv4 (hex to int conversion)
DECLARE @IsNum int, @ZERO int, @IsAlpa int
set @ZERO = ASCII(‘0′)
set @IsNum = ASCII(‘9′)
set @IsAlpa = ASCII(‘a’) – 10
DECLARE @intH int, @intL int[/B]
[B] SET @intH = ASCII(SUBSTRING(@strInAddress, 1, 1))
IF (@intH <= @IsNum) SET @intH = @intH – @ZERO ELSE SET @intH = @intH – @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 2, 1))
IF (@intL <= @IsNum) SET @intL = @intL – @ZERO ELSE SET @intL = @intL – @IsAlpa
SET @strOutAddress = CONVERT(varchar(3), @intH * 16 + @intL) + '.'[/B]
[B] SET @intH = ASCII(SUBSTRING(@strInAddress, 3, 1))
IF (@intH <= @IsNum) SET @intH = @intH – @ZERO ELSE SET @intH = @intH – @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 4, 1))
IF (@intL <= @IsNum) SET @intL = @intL – @ZERO ELSE SET @intL = @intL – @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'[/B]
[B] SET @intH = ASCII(SUBSTRING(@strInAddress, 5, 1))
IF (@intH <= @IsNum) SET @intH = @intH – @ZERO ELSE SET @intH = @intH – @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 6, 1))
IF (@intL <= @IsNum) SET @intL = @intL – @ZERO ELSE SET @intL = @intL – @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL) + '.'[/B]
[B] SET @intH = ASCII(SUBSTRING(@strInAddress, 7, 1))
IF (@intH <= @IsNum) SET @intH = @intH – @ZERO ELSE SET @intH = @intH – @IsAlpa
SET @intL = ASCII(SUBSTRING(@strInAddress, 8, 1))
IF (@intL <= @IsNum) SET @intL = @intL – @ZERO ELSE SET @intL = @intL – @IsAlpa
SET @strOutAddress = @strOutAddress + CONVERT(varchar(3), @intH * 16 + @intL)
END
ELSE
BEGIN
— ipv6
SET @strOutAddress = @strOutAddress + SUBSTRING(@strInAddress, 1, 4) + ':'
+ SUBSTRING(@strInAddress, 5, 4) + ':'
+ SUBSTRING(@strInAddress, 10, 4) + ':'
+ SUBSTRING(@strInAddress, 15, 4) + ':'
+ SUBSTRING(@strInAddress, 20, 4) + ':'
+ SUBSTRING(@strInAddress, 25, 4) + ':'
+ SUBSTRING(@strInAddress, 29, 4) + ':'
+ SUBSTRING(@strInAddress, 33, 4)
END
—- guid sample '6F9619FF-8B86-D011-B42D-FFF34FC964FF'
RETURN @strOutAddress
END
[/B]
[Download script [URL="http://www.box.net/shared/ufemtfu2yo"]here[/URL].]
[I](Special thanks to Avi Sander with Microsoft Israel for sharing this SQL code with me. Shortly after I received this code it was also [URL="http://tinyurl.com/md2wwt"]posted on the TMG Product Team Blog[/URL] as well.)[/I]
I apologize in advance if any of the SQL code listed in this post is not readable. When posting code like this, formatting with WordPress can cause problems. If you have any issue with code on this page, send me an [EMAIL="rhicks@celestix.com"]e-mail[/EMAIL] and I’ll be glad to send you the actual script files[/LEFT]