LogParser to count number of emails

The task was to count emails sent in and sent out from the organization. LogParser 2.2 presented as an easy tool to parse logs in exchange server for counting the emails. For some reason, COUNT DISTINCT was not working so, I had take a round about.

1. Parse log files and count all MSGID and save the output to as a CSV file.
2. Parse the CSV file created above and count the MSGID
3. Here, I created a graphs as output for few and for others I used the result in excel for further manipulation.

Here are few queries I used:

internal email

SELECT DATE, MSGID, COUNT(MSGID) FROM

WHERE Recipient-Address NOT LIKE '%excludeThis@my.org'

AND Sender-Address NOT LIKE '%excludeThis@my.org%'

AND Sender-Address LIKE '%@my.org%'

For external Emails

LogParser -i:W3C "SELECT Date, MSGID, COUNT(MSGID) FROM \\LogFileLocation\2012*.log WHERE Sender-Address NOT LIKE '%@my.org%' AND Recipient-Address NOT LIKE '%excludeThis@my.org%' AND STRLEN(Sender-Address) > 3 GROUP BY Date, MSGID ORDER BY Date" -o:CSV > X:\ExternalEmail2012.csv

Then used above file to create user usable output file

LogParser -i:CSV "SELECT Year, Month, COUNT(MSGID) FROM X:\ExternalEmail2012.csv GROUP BY Year, Month ORDER BY Month" -o:CSV > H:\MonthlyBreakDown2012.CSV

Few commands for ref:

LogParser /?

LogParser -h -o:chart

LogParser -h -i:CSV

Links for ref: (was working at the time of writing)

http://www.logql.com/documentation/functions/

http://logparserplus.com/Functions

astu...

D.