Creating Custom Queries For Searching TMG 2010 Logging Databases

In this case I was trying to find out unique client IP addresses that were accessing TMG published web site in last 3 days. TMG server was having locally installed logging database. One way to achieve this task is using SQL Server Management Studio, another way is using powershell. In this article, I'll show how I have found the ip addresses using powershell.
After importing the SQLServerCmdletSnapin100, Invoke-Sqlcmd cmdlet will be available for running:

Add-PSSnapin SQLServerCmdletSnapin100

Now it's time to create the SQL query using here-strings:

$query=@"

SELECT ClientIP as IPAddress from
(SELECT DISTINCT ClientIP
FROM [ISALOG_20130926_WEB_000].[dbo].[WebProxyLog] WHERE [WebProxyLog].[Rule] = 'TMG Rule Name'
UNION
SELECT DISTINCT ClientIP
FROM [ISALOG_20130925_WEB_000].[dbo].[WebProxyLog] WHERE [WebProxyLog].[Rule] = 'TMG Rule Name'
UNION
SELECT DISTINCT ClientIP
FROM [ISALOG_20130924_WEB_000].[dbo].[WebProxyLog] WHERE [WebProxyLog].[Rule] = 'TMG Rule Name')t1;
"@

The SQL query is simple, so I'm not going into details.
Now it's time to execute the query using invoke-sqlcmd :

Invoke-Sqlcmd -Query $query -ServerInstance localhost\msfw -QueryTimeout 300 | ft

The result will contain IP addresses in unfriendly readable format, something like:

C0A8018A-FFFF-0000-0000-000000000000 

The reason for this kind of logging, is that TMG is using same field for logging IPv4 and IPv6 addresses. One way for converting CAA8018A into 192.168.1.138 is using Excel formula which looks like this:

HEX2DEC(MID(A1,1,2)) &"."&HEX2DEC(MID(A1,3,2))&"."&HEX2DEC(MID(A1,5,2))&"."&HEX2DEC(MID(A1,7,2))


Happy IP addresses hunting :)