Querying the TMG log directly

Querying the TMG log directly

  • Comments 1
  • Likes

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
  • Hi,

    there is a typo:

    Instead of: "specify your connection as localhost\mswf" it must be: "specify your connection as localhost\msfw"

    greetings Marc Grote

    www.it-training-grote.de

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment