Tag Archives: TMG

Forefront TMG IP Issue

I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:

  1. I have certain blocks of IP addresses blocked on the Cisco firewall and occasionally a user will repeatedly hit a website that sits in one of those blocks.  I will then have to find out who is doing this as the firewall will always just report the address of the proxy server for the traffic.
  2. Occasionally a user will download malware and I’ll need to find out where they got it from so that the source of the traffic can go into the block list in point #1.

Either way I end up running a report on a system at the site which has SQL Server Management Studio installed on it with a query something like the below:

SELECT [ClientIP]
      ,[ClientUserName]
      ,[ClientAgent]
      ,[ClientAuthenticate]
      ,[logTime]
  FROM [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog]
 WHERE [ISALOG_20110317_WEB_000].[dbo].[WebProxyLog].[DestHostIP] = ‘d3bd0e17-FFFF-0000-0000-000000000000’

You’ll note that the address on the bottom line is rather unintelligible since it uses a semi-IPv6 format used by TMG.  To work around this I had been doing hand translation using the calculator in Windows to figure out what the address I am looking for should be.  However, I grew tired of that and wrote a program that will handle the translation/conversion to and from a regular IPv4 address and Forefront TMG 2010 address (because of some apparent vague interest, I’ve packed a zip with just the executable (no code) here).

I want to note though, that since it was casually put together I didn’t code it to handle any exceptions.  The source code is included though, so if you want to code around some fat fingering then feel free.