LogParser Dumping ground

Exchange, LogParser Studio

LogParser Dumping ground

The following is an area where I will be dumping LogParser scripts. The format works with Log Parser Studio.

Return all senders (IP and reverse Lookup) through Receive Connector


SELECT EXTRACT_PREFIX(remote-endpoint,0,':') AS RemoteIP,
REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name,
count(*) AS hits
FROM '[LOGFILEPATH]'
where data LIKE '%EHLO%'
GROUP BY RemoteIP
ORDER BY hits DESC

Top 100 senders based on Message Tracking Logs

The To_Lowercase function means there are no duplicates if you have the same email address with lowercase and upper case formatting.

SELECT Top 100
To_lowercase(Sender-Address) AS Sender,
count(*) AS hits
FROM '[LOGFILEPATH]'
where source = 'SMTP'
AND event-id = 'RECEIVE'
GROUP BY Sender
ORDER BY hits DESC

Top 100 Recipients based on message tracking logs

These figures are not totally accurate as the recipient field can contain more than one user. This does not count the individual user per field, but will put a new entry with multiple users. For exampe if [email protected] received 10 emails, and [email protected] received 20 emails, and usera and userb were recipients on 5 emails together the results will look like

Recipient hits
[email protected] 10
[email protected] 20
[email protected];[email protected] 5


SELECT Top 100
To_lowercase(Recipient-Address) AS Recipient,
count(*) AS hits
FROM '[LOGFILEPATH]'
where source = 'SMTP'
AND event-id = 'RECEIVE'
GROUP BY Recipient
ORDER BY hits DESC

Leave a Reply

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