31 August 2010

Export TMG proxy logs to w3c while maintaining native reporting

When I implemented the new TMG 2010 infrastructure I was only moderately impressed by the native reporting.  Following TMG SP 1 this changed a bit.  There were some "Significant enhancements to the reporting.  For more details on this check out:

http://blogs.technet.com/b/isablog/archive/2010/08/15/reporting-improvements-in-forefront-tmg-sp1.aspx

Grand, so for this first time TMG actually generated a report worth keeping.  But now here is the catch.  In ISA server you could export to w3c files and it would not make much difference.  In TMG if you switch to w3c logging you loose the ability to generate reports.  You also loose the ability in the logging filter to specify anything other than Live.  This is actually a problem.

So in short you want to use a 3rd party log analyser you will loose out on the native functionality.

Unless you can come up with a cunning plan.

Here is what I ended up doing.

  • I log directly to the native SQL2008 Express.
  • I then have a scheduled task that executes a script that runs a modified  SQL to W3C Conversion script
  • I can then pick up my exported files for processing.
The conversion script is MSDEtotext.vbs

It is one of the tools available form here :
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=82027864-4abd-4896-8255-55f6ea775489

Two things to look out for:

If importing into webspy there is and additional vbcr that breaks the import
The bytes sent and received are reversed. Webspy has made this an option in their loader now.

I have fixed these in my scripts but just be aware of them.

Drop me a mail if you want them.

4 comments:

Scott Glew said...

Hey Etienne,

We've not got a version of the script with the offending vbcr removed available at:
http://www.webspy.com/resources/utils/MSDEToText_TMG2010.zip

Cheers!
Scott from WebSpy

Etienne Liebetrau said...

Scott is my "inside man" at Webspy. Thanks for updating and letting us know.

Navegador said...

I'm tring to export the file, but the Webpsy says is not a valid W3C document... Do you still have that working?

Etienne Liebetrau said...

Navegador - You need to use the updated scrip from webspy

http://www.webspy.com/resources/utils/MSDEToText_TMG2010.zip

Post a Comment