Using LogParser to query Exchange 5.5 SMTP traffic

We are working to decommission our existing Exchange 5.5 environment and looking to migrate all services to Exchange 2003. As part of the decom, we need to re-direct the Internal SMTP traffic off our Exchange 5.5 bridgeheads. This meant, determining which servers were connecting to the server. First we changed all MX records in our DNS to point to the new server. Then we watched the Application logs on the server for MSExchangeIMC - ID: 2000 events.
This provided some 2,000 hits on our server. Ouch! Using LogParser from Microsoft, I was able to generate a quick query to pull the information.

Server Hits
citrixserver.example.local 373
othersmtpserver.example.local 214
appserver.example.local.local 150
192.168.0.28 25
webserver.example.local 1

With this data, I was able to contact the server owners and have them change their relay information.

logparser "select trim(substr(strings,0,index_of(strings,'|'))) as Server, count(*) as hits into LogFile.csv from \\ExchangeServer\application where Sourcename like 'MSExchangeIMC' and TimeGenerated >= SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-01 00:00:00', 'yyyy-MM-dd hh:mm:ss')) and eventid=2000 group by strings order by hits DESC"

Let me break this down a bit.

  1. What fields I want
    This section determines which fields are pulled out. Strings is a Application Log field. The query pulls out the server name from this field, then counts each entry in the value HITS.

    trim(substr(strings,0,index_of(strings,'|'))) as Server, count(*) as hits

  2. Where to/from
    Put the results in the specified log file when pulling from this server's app log. Putting commas between the different servers, you can list several there. (\\server1\application, \\server2\application).

    into LogFile.csv from \\ExchangeServer\application

  3. Give me only what's relevant:
    Give me all MSExchangeIMC events where the ID = 2000 and in the last 2 days. The long line for 'timegenerated' is the long date difference field.

    Sourcename like 'MSExchangeIMC' and
    eventid=2000 and TimeGenerated >= SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00:00', 'yyyy-MM-dd hh:mm:ss'))

  4. Make it pretty
    This summarizes the data and combines on the SERVER field. Then it sorts the group by HITS in descending order.

    group by server order by hits DESC

This query runs in about 20 seconds on 2 servers with 3 days of data. Putting this into a BATCH file, I was able to make the script rather user friendly.