Share via

Querying the TMG log directly

  • Article

On a TMG server, logging is done using a local SQL Express 2008 server. This data can be viewed using the TMG management console, where you can view the data “live”, as requests come in, or create a query for specific details:

This interface is pretty elaborate, allowing you to query a specific client IP, a specific time period and much more, but ultimately some people might prefer to have more control over the query, beyond what the interface allows.

The good news is that the data is stored in SQL, so any SQL query tool can do the trick. You can install the full SQL server studio and use the SQL Management Studio to view the tables directly. You can also install Visual Studio and use the Server Explorer window to add the TMG database instance. There are other tools and options, like the ones listed here.

To connect to the SQL instance used by TMG, specify your connection as localhost\mswf. For example, here’s how to connect using the SQL Management studio (which comes with SQL 2008 R2):

Once connected, you can open the ISALOG_<date>_FWS_000 to view the Firewall log, which would be in the dbo.FirewallLog:

Here you can easily see the various columns, and their headers should be explanatory enough for most things. However, the option with the most control is probably using a script to run an SQL query directly. This could be done using VBScript and the ADODB method, which is built-in the windows OS. A query like that can be written to return the entire dataset, and once you have that, you could do anything you want with it. For example, you could export the data into a flat CSV file or into an XLS file. You could parse the data to look for patterns and trigger certain actions on them, like sending out an Email if a certain user logs in. You could count the data or perform statistics on them at certain periods (for example, how many unique IPs connected to VPN during lunch time). The great thing about it is that when you can write your own code to handle the data, you have the full power of data mining at your fingertips.

To do this, you need to initialize the ADODB object, and run an SQL query on the appropriate SQL table, and then run a loop through the dataset. Then, retrieve the cell values, and analyze them. Here’s a sample script:

set objConn=createobject("ADODB.connection") strConnString = "Provider=SQLOLEDB;data source='localhost\msfw';” &_ “IntegratedSecurity=SSPI;database=ISALOG_20111229_FWS_000" objConn.open strConnString strQuery = "select * from dbo.FirewallLog" set objRecordSet=objConn.execute(strQuery) for i= 1 to 200 strSourceIP = objRecordSet("SourceIP") strDestNetwork = objRecordSet("DestinationNetwork") strRule = objRecordSet("Rule") strTime = objRecordSet("GMTLogTime") wscript.echo strTime & ";" & strSourceIP & ";" & strDestNetwork & ";" & strRule objRecordSet.MoveNext next

Make sure to adjust the database name (in yellow above) to the actual one – this refreshes daily. The code above loops through the top 200 entries, but you can also use a do-loop structure to go through all the entries, using the following format:

Do while not objRecordSet.EOF …….read the recordset items objRecordSet.MoveNext loop

Keep in mind, of course, that if the log is very large, the dump could take a long time and put stress on your server.

In addition to the columns above, here are the other columns you can find in the TMG firewall log:

servername logTime protocol SourceIP SourcePort DestinationIP DestinationPort OriginalClientIP SourceNetwork DestinationNetwork Action resultcode rule ApplicationProtocol Bidirectional bytessent bytessentDelta bytesrecvd bytesrecvdDelta connectiontime connectiontimeDelta DestinationName ClientUserName ClientAgent sessionid connectionid Interface IPHeader Payload GmtLogTime ipsScanResult ipsSignature NATAddress FwcClientFqdn FwcAppPath FwcAppSHA1Hash FwcAppTrusState FwcAppInternalName FwcAppProductName FwcAppProductVersion FwcAppFileVersion FwcAppOrgFileName InternalServiceInfo ipsApplicationProtocol FwcVersion

Happy hunting!

 

Blog post written by Erez Ben Ari

Comments

  • Anonymous
    June 19, 2012
    Hi, there is a typo: Instead of: "specify your connection as localhostmswf" it must be: "specify your connection as localhostmsfw" greetings Marc Grote www.it-training-grote.de