Share via

Log Parser Studio - Exchange 2003 Message Tracking Logs

  • Article

If you saw the post about Exchange log file formats known from within LPS as "EEL" and "EELX" you may remember that they all follow the same basic format albeit with a few exceptions. Exchange 2003 message tracking logs is one of those exceptions. Exchange 2003 message tracking logs are in a TSV type format so the TSV input log type works but it needs a slight modification of the default settings. Here's how to set up LPS to query these logs:

Point LPS to the Exchange 2003 message tracking logs in the log file manager window.

  1. Create a new query. File > New Query or click the new query icon (far left in the toolbar).

  2. Next you'll need to change the log file type to TSVLOG:

  3. Once TSVLOG has been selected click the gear icon to bring up the properties for TSV:

  4. Once the properties window appears, the only change that is required is to set nSkipLines to a value of 2 and click OK:

  5. Press F5 to run the query.

Remember that you can revert to the default TSV format at any time by opening the properties window and clicking reset. The properties window will close and the defaults are now in effect. To bring this one to a close, I'll leave you with a starter query for Exchange 2003 Message Tracking. This query counts the number of messages per sender, recipient, message subject, date and client IP address for the top 20,000 messages in order of occurence:

/* Exchange 2003 Message Tracking - Count Messages per Sender/Recipient/Date/IP/Subject */ SELECT TOP 20000 [# Date] AS Date, client-ip, Count(*) AS Total, Sender-Address, Recipient-Address, Message-Subject
FROM '[LOGFILEPATH]'
GROUP BY Date, client-ip, Sender-Address, Recipient-Address, Message-Subject

You can copy and paste the above into LPS or you can import/merge the XML file below into the LPS library. To import, from LPS choose File > Import > XML to Library. Make absolutely sure you choose "Merge now" instead of "Replace now" then search for "Exchange 2003:" in the library without the quotes.

You can also import it directly into the new query you setup above (bypassing the library) by right-clicking inside the query window and choosing Import. Change the file type to .XML, load the file and if you have already created a query and made the settings above, it is ready to run. Just be sure to save it (File > Save or CTRL+S) if you decide you want it as part of the current library.
 

Exchange2K3MT1.XML

Comments

  • Anonymous
    September 23, 2015
    Thanks for this tutorial
  • Anonymous
    November 18, 2015
    Awesome! Helped me a lot!
  • Anonymous
    November 19, 2015
    Hi Kary,
    I cannot do any successful Where command. Tried the command below. Any WHERE command doesn't seems to work. Please help thanks.

    SELECT [# Date] AS Date, Time, client-ip, Client-hostname, Server-hostname, server-IP, Sender-Address, Recipient-Address, Message-Subject
    WHERE Recipient-Address LIKE User@domain.com
    FROM '[LOGFILEPATH]'
  • Anonymous
    November 19, 2015
    I found the command. The where should be under FROM ;-)

    SELECT [# Date] AS Date, Time, client-ip, Client-hostname, Server-hostname, server-IP, Sender-Address, Recipient-Address, Message-Subject
    FROM '[LOGFILEPATH]'
    WHERE Recipient-Address LIKE User@domain.com

    • Anonymous
      May 17, 2016
      Hi Ally,I somehow missed this, yes you are correct, the WHERE goes under the FROM statement. Glad you go it sorted.Kary