Both ISA Server 2004/2006 and TMG 2010 can store logs in SQL Server (MSDE/Express Edition) which is recommended configuration.

Unlike ISA Server which stores IP address as bigint type TMG 2010 stores them as GUIDs for IPv4 and IPv6 compatibility.

So, a function is needed to see IPv4 addresses in easily readable way.

CREATE FUNCTION [dbo].[GuidToIPv4]
(
@guid VARCHAR(36)
)
RETURNS VARCHAR(15)
AS
BEGIN
 DECLARE @hex VARCHAR(8)
 SET @hex = LEFT(@guid, 8 )
 RETURN CONVERT(VARCHAR(3), sys.fn_replvarbintoint(sys.fn_cdc_hexstrtobin(LEFT(@hex, 2))), 0) + '.' +
  CONVERT(VARCHAR(3), sys.fn_replvarbintoint(sys.fn_cdc_hexstrtobin(SUBSTRING(@hex, 3, 2))), 0) + '.' +
  CONVERT(VARCHAR(3), sys.fn_replvarbintoint(sys.fn_cdc_hexstrtobin(SUBSTRING(@hex, 5, 2))), 0) + '.' +
  CONVERT(VARCHAR(3), sys.fn_replvarbintoint(sys.fn_cdc_hexstrtobin(RIGHT(@hex, 2))), 0)
END
GO

Actually, IPv4 address forms a GUID where first 8 characters naturally make 4 bytes in hex – 1.1.1.1 makes 01010101-FFFF-0000-0000-000000000000 and 255.255.255.255 makes FFFFFFFF-FFFF-0000-0000-000000000000, so if you read hex fluently – here you go.

When creating custom webparts or other custom features in SharePoint that retrieves data from a SQL server, there are a few gotchas and pitfalls that you should be aware of. I spent a few hours battling a couple of problems, so I thought I’d document the things I found out here to spread the knowledge.

Read More