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:

clip_image002

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\msfw. For example, here’s how to connect using the SQL Management studio (which comes with SQL 2008 R2):

clip_image004

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

clip_image006

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!

Comments

  • Anonymous
    November 23, 2015
    Thanks for the great info. I really loved this. I would like to apprentice at the same time as you amend your web site, how could i subscribe for a blog site?
    For more info on showbox please refer below sites:
    http://showboxandroids.com/showbox-apk/
    http://showboxappandroid.com/
    Latest version of Showbox App download for all android smart phones and tablets. http://movieboxappdownloads.com/ - It’s just 2 MB file you can easily get it on your android device without much trouble. Showbox app was well designed application for android to watch movies and TV shows, Cartoons and many more such things on your smartphone.
    For showbox on iOS (iPhone/iPad), please read below articles:
    http://showboxappk.com/showbox-for-ipad-download/
    http://showboxappk.com/showbox-for-iphone/
    Showbox for PC articles:
    http://showboxandroids.com/showbox-for-pc/
    http://showboxappandroid.com/showbox-for-pc-download/
    http://showboxforpcs.com/
    There are countless for PC clients as it is essentially easy to understand, simple to introduce, gives continuous administration, effectively reasonable. it is accessible at completely free of expense i.e., there will be no establishment charges and after establishment it doesn't charge cash for watching films and recordings. Not simply watching, it likewise offers alternative to download recordings and motion pictures. The accompanying are the strides that are to be taken after to introduce Showbox application on Android. The above all else thing to be done is, go to the Security Settings on your Android telephone, Scroll down and tap on 'Obscure sources'.