I typically have two issues that I run into with our Forefront TMG (aka ISA 2010) proxy server:
- 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.
- 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.