Posts

Showing posts from September, 2013

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&#…