Udostępnij za pośrednictwem

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

  • Artykuł

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

Comments

  • Anonymous
    January 01, 2003
    Thanks!!! nice one :)

  • Anonymous
    January 01, 2003
    There must be a semicolon in stead of comma =HEX2DEC(MID(A2;2;2)) &"."&HEX2DEC(MID(A2;4;2))&"."&HEX2DEC(MID(A2;6;2))&"."&HEX2DEC(MID(A2;8;2))

  • Anonymous
    January 01, 2003
    Youi Rock!!! I have an adition because you only convert one. But i like to convert the whole column. Create new column (B2) and name it Converted IP4 Select B2 and paste following sum: =HEX2DEC(MID(A2;2;2)) &"."&HEX2DEC(MID(A2;4;2))&"."&HEX2DEC(MID(A2;6;2))&"."&HEX2DEC(MID(A2;8;2)) To apply the sum to the whole column Select B2 scroll down to the last B* and click while holding shift Now click at the end of the sum and do "CTRL + Enter" Cheers

  • Anonymous
    January 01, 2003
    The JT's problem is already solved in altecsole post. So you must run Import/Export Wizard as administrator (right click then choose Run As Administrator) or turn off UAC (not just well solution on TMG)

  • Anonymous
    January 01, 2003
    @fortiz - Try put HEX value between parentheses { and } as shown above.

  • Anonymous
    August 19, 2010
    Hi In your step 3, where you specify the database name, I cannot do the same. It simply shows the system db's there, no web or fw db's.  I can see all the sql express db's on the filesystem. When I type in the name (or full path even) to the db, then it says 'Login failed for user domainuser' where it shows my login details.  How do I access the databases then. I specifically chose one that is no longer in use. The TMG2010 is a default setup, with default logging to SQL Express. It never asked for an "sa" password so it must be using windows auth internally, but how do i use the provided tool then to connect to the db's ?   Please help

  • Anonymous
    October 20, 2010
    Are you running the import export wizard as administrator?

  • Anonymous
    October 22, 2010
    Having looked at this I've used a solution that uses Volume Shadow Copy to backup the mdf and ldf log files. You can then attach the backed up database in SQL Server 2008 (I used the Express Edition) and run your own query on the table.

  • Anonymous
    February 19, 2011
    I have same problem like JT Help us

  • Anonymous
    February 22, 2011
    I am also facing the same issue like JT and Sabri, is there any work around?

  • Anonymous
    May 11, 2011
    The Correct Excel Code, for Cell B2 should be: =HEX2DEC(MID(A2,2,2)) &"."&HEX2DEC(MID(A2,4,2))&"."&HEX2DEC(MID(A2,6,2))&"."&HEX2DEC(MID(A2,8,2))

  • Anonymous
    May 11, 2011
    I also had the same problem as JT. Disabling UAC and a restart fixed the problem for me

  • Anonymous
    February 15, 2012
    He, Anybody has found solution for JT problem, regard, a.

  • Anonymous
    October 22, 2012
    Good afternoon. Place the formula: = HEX2DEC (MID (A2, 2, 2)) & "." & HEX2DEC (MID (A2, 4, 2)) & "." & HEX2DEC (MID (A2, 6, 2)) & "." & HEX2DEC (MID (A2, 8, 2)) and throws me the error: # NAME?. anyone know why this is this error?

  • Anonymous
    March 19, 2013
    The comment has been removed

  • Anonymous
    April 03, 2013
    You are amazing! Thank you very much!

  • Anonymous
    December 25, 2013
    Analysing Forefront TMG logs

  • Anonymous
    May 29, 2015
    I know this is old posting, but I just want to share the formula that worked for me:
    =HEX2DEC(MID(A2,1,2)) &"."&HEX2DEC(MID(A2,3,2))&"."&HEX2DEC(MID(A2,5,2))&"."&HEX2DEC(MID(A2,7,2)). A2,1,2 means cell A2, start number, character count