How ISA MSDE Logging Works

Recently on a mailing list a question was asked for someone to explain how ISA does logging to MSDE and why you sometimes see a lot of log files for the same day. Dana Epp, of Scorpion Software, quickly responded with a very concise and clear response.

When using MSDE, ISA stores the logs in daily database files. If you make any policy changes to the firewall, it stops the instance and restarts it with a new name. As an example for today the database would be called ISALOG_20070110_FWS_000. (That is the format YYYYMMDD in case you missed it). If you stopped and restarted ISA, it would then be ISALOG_20070110_FWS_001. You would need to function concat() { [native code]}the 000 and the 001 to get the complete set of log events for the day. For the web proxy, its "_WEB_" instead of of "_FWS_". Microsoft does this to apparently prevent data corruption, although I have yet to see how that matters in this regard. There is no reason it couldn't be merged. (IMNSHO). I think they do it to prevent the DB size limitation for MSDN databases.

Depending on your audit log retention policy, you might have up to a month or two of these hanging around. What Firewall Dashboard (Dana's ISA add-on) does is merge all the data together, consolidate all the events down to remove log events not helpful in analysis, and import them into the FWDB database instance. Thats how we can literally go from a few hundred thousand events down to a few hundred, depending on the scenario.

The actual table structure for the whole lot is stored under the ISA directory. If you wish to see the structure of the data, its in *.sql scripts in the base dir of ISA.

If you are finding that the files are hanging around past the date you want, you can freely delete them... with one caveat. If you are consolidating the data with the ISA reporting engine, make sure you aren't deleting the summary/archive data.

There is a KB on configuring logging for ISA. Not sure if you would find that useful or not. You can see it at: http://support.microsoft.com/?id=302372