How to View TMG Logs when using SQL Server Express for Logging

1. Introduction

By default Forefront TMG 2010 will store the Firewall and Web Proxy Logging in Microsoft SQL Server 2008 Express databases. . Many Forefront TMG administrators need access to much older data than can be easily maintained on the firewall itself, so these administrators need a simple method for moving their log data to a server without impacting TMG functionality. This posting will describe how to export your Forefront TMG SQL Express logs to a text file using SQL Express Log Export functionality.

Note: You can also use MSDE BCP tool from www.isatools.org , which is a tool that uses SQL BCP automation to export ISA/TMG logs to text files which are suitable for import using SQL BCP. In order to use this script on Forefront TMG 2010, you will need to install the Microsoft SQL Server 2008 Feature Pack. To use it on Forefront TMG MBE, you need to install the Microsoft SQL Server 2005 Feature Pack

2. Exporting TMG Logs

You should schedule your log export process for a time when TMG is not busy so that you avoid overworking the Forefront TMG log instance, possibly causing TMG to fall back on its log queue.

In order to export the data from the SQL Server Express database installed on TMG, follow the steps below:

1. On Forefront TMG click All Programs / Microsoft SQL Server 2008 / Import and Export Data.

2. Click Next on the welcome window.

3. Choose your Data Source and database as shown below (notice that in this case I’m exporting the Web Proxy log) and click Next to continue:

Note: do not try to export the currently-used log database. Doing so may result in database access contention.

4. On the Choose Destination page, change the destination to Flat File Destination, choose a location and select the options according to the figure below. Click Next to continue.

5. On the Specify Table Copy or Query page, leave the default option selected (Copy data from one or more tables or views) and click Next to continue.

6. On the Configure Flat File Destination page verify if the source table is correct, select “tab” in the column delimiter and click Next to continue.

7. On the Run Package page, leave the default option selected and click Next.

8. Click Finish to complete and you should see the execution summary as shown in the figure below:

3. Reading the Logs

Now you can open your file (using Microsoft Excel for example) and you will be able to easily examine the data. You can use the Web Proxy Log Fields article for more information on each field within this table, plus the following ones:

· Object source http://technet.microsoft.com/en-us/library/cc441680.aspx

· Return code http://technet.microsoft.com/en-us/library/cc441734.aspx

· Cache status http://technet.microsoft.com/en-us/library/cc441710.aspx

One thing you may notice as you examine the log data is that the ClientIP field does not display an IP address as you would see it in the Forefront TMG log viewer.

The reason why the client IP address is stored using this format is because of the need to store IPV4 and IPV6 addresses in the same field type. Because IPv4 and IPv6 addresses are represented using different-sized data and we only have one field, Forefront TMG encodes the IPv4 address in a special way. In order to convert this value into a dotted-decimal IPv4 format, you need to convert it.

In the example above, C0A8019A-ffff-0000-0000-000000000000 represents an IPv4 address. In order to convert this value to dotted-decimal form:

· Separate the first eight digits into hexadecimal pairs, such as C0, A8, 01, 9A

· Convert these values to their decimal equivalent::

C0 = 192

A8 = 168

01 = 1

9A = 154

· Concatenate the decimal values in the order they appear using periods “.”: 192.168.1.154

In summary, we store the IPv4 in the following format: xxxxxxxx-ffff-0000-0000-000000000000. To automate this conversion process, you can create a new column on Excel called ClientIPV4Converted (or whatever name you prefer) and write the following formula on this cell (B2):

=HEX2DEC(MID(A17,2)) &"."&HEX2DEC(MID(A17,3,2))&"."&HEX2DEC(MID(A17,5,2))&"."&HEX2DEC(MID(A17,7,2))

Here how it will show up:

A

B

1

ClientIP

ClientIPV4Converted

2

{C0A8019A-FFFF-0000-0000-000000000000}

192.168.1.154

4. Conclusion

These simple steps can be very useful for administrators to rapidly have access to the TMG logs in a flat file so they can manipulate in the best way that they want. The goal of this post was to show you how a built in tool installed by Microsoft SQL Server Express can assist you in your Forefront TMG log maintenance.

Authors

Yuri Diogenes - Sr Security Support Escalation Engineer, Microsoft CSS Forefront Security Edge Team

Franck Heilmann - Escalation Engineer, Microsoft CSS Forefront Security Edge Team

Technical Reviewers

Thomas Detzner - Escalation Engineer, Microsoft CSS Forefront Security Edge Team

Jim Harrison - Program Manager, Microsoft Forefront Security Edge Team