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


 

Comments (18)

  1. Thanks!!! nice one 🙂

  2. Anonymous says:

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

  3. Anonymous says:

    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

  4. Anonymous says:

    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)

  5. Anonymous says:

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

  6. JT says:

    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

  7. altecsole says:

    Are you running the import export wizard as administrator?

  8. altecsole says:

    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.

  9. Sabri says:

    I have same problem like JT

    Help us

  10. RA says:

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

  11. Tim says:

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

  12. Tim says:

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

  13. adam says:

    He,

    Anybody has found solution for JT problem,

    regard,

    a.

  14. fortiz says:

    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?

  15. Fonzwannabe says:

    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

  16. Fabio Araújo do Carmo says:

    You are amazing! Thank you very much!

  17. Anonymous says:

    Analysing Forefront TMG logs

  18. Rodolfo says:

    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