Tales From Tech Support – Part 17: Searching in Huge Log Files

The ORF Log Viewer Tool is excellent for checking what happened to an email: by building simple filters with a few clicks, you have all the information you need. However, loading extremely huge log files (we are talking about hundreds of megabytes) may take quite a lot of time, as ORF logs of this size simply contain too much data.

For these cases, we provide a log parser tool which can parse and dump the log data into SQL databases, so you can use SQL queries to retrieve the information you seek. Moreover, you can also use this parsed data (now in SQL) for web-based reports for end-users (see the demo on the link above).

But what if a user asks you to show him all blacklisted emails sent to his address from the past week ASAP, and you simply don’t have the time or mood to setup an SQL database for this? Meet the Microsoft Log Parser.

The Microsoft Log parser is a “powerful, versatile tool that provides universal query access to text-based data”. By using it, you can

  • simply search in multiple ORF log files at once
  • export the query results to a CSV file, which can be then viewed in Microsoft Excel or OpenOffice.org’s Calc application

How can we do this?

1. First, download the Microsoft Log Parser from this site.
2. Install it.
3. Start it from the Start menu (it will open a command line window).

You should read the documentation about the query syntax. Basically, we need to set the input location and type, the output file (where we dump the query results), and the SQL query parameters. Here is a sample query with a single query parameter (recipient address is test@mydomain.com).

logparser -i:w3c “SELECT x-event-datetime AS Date-Time,x-event-class AS Event,x-event-severity AS Severity,x-event-msg AS Description,x-filtering-point AS Filter,x-ip AS IP-address,x-msg-id AS MsgID,x-msg-subject AS Subject,x-sender AS Sender,x-recipients AS Recipient INTO test_report.csv FROM ‘C:\Program Files\ORF Enterprise Edition\*.log’ WHERE recipient LIKE ‘%%test@mydomain.com%%'”

This command will create a file called test_report.csv in the Log Parser directory which includes all log entries for this recipient from the ORF logs (located in Program Files\ORF Enterprise Edition). Note that if you do not have the logging of the message ID in ORF Enabled (Configuration \ Global \ Log and events \ ORF Text log – Configure button \ Settings tab) you should remove the “x-msg-id AS MsgID” part.

As you can see, the query is flexible, so the possibilities are endless: feel free to experiment and share your ideas in the comments.

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code