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

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

  • Comments 17
  • Likes

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
  • 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 domain\user' 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

  • Are you running the import export wizard as administrator?

  • 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.

  • I have same problem like JT

    Help us

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

  • 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))

  • I also had the same problem as JT. Disabling UAC and a restart fixed the problem for me

  • 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))

  • He,

    Anybody has found solution for JT problem,

    regard,

    a.

  • 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)

  • Thanks!!! nice one :)

  • 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?

  • @fortiz - Try put HEX value between parentheses { and } as shown above.

  • 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

  • If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

    How?

    1.On the Tools menu, click Add-Ins.

    2.In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.

    3.If necessary, follow the instructions in the setup program.

    Source office.microsoft.com/.../hex2dec-HP005209111.aspx

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