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:
SELECT ClientIP as IPAddress from
FROM [ISALOG_20130926_WEB_000].[dbo].[WebProxyLog] WHERE [WebProxyLog].[Rule] = 'TMG Rule Name'
FROM [ISALOG_20130925_WEB_000].[dbo].[WebProxyLog] WHERE [WebProxyLog].[Rule] = 'TMG Rule Name'
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:
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 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 :)

How to check EMBG (Unique Master Citizen Number) using regex

In this post, I will share my implementation of how to check if some number looks like EMBG or Unique Master Citizen Number. For those of yo...