Report Top Sender IP’s on Exchange Server 2010 using Log Parser

When you are investigating Exchange Transport server load one of the interesting pieces of data to look at is the IP addresses that are connecting to your server the most.

There are two different log sets that you can use for this:

• Protocol logs
• Message Tracking logs

One of the best ways to describe the difference between these is that protocol logs will capture SMTP connections that may or may not make it all the way in to the Transport pipeline. For example a connection from a spammer that gets blocked by IP filtering will appear in the protocol logs but not the message tracking logs.

The detail captured in a protocol log will look a lot like what you would see if you were manually testing SMTP via telnet on a server.

Message tracking logs will capture messages that get processed through the Transport pipeline, and capture information such as message submission and delivery rather than the SMTP conversation that protocol logging reflects.

Message tracking is also turned on by default and is set per-server, whereas protocol logging is not turned on by default and is set per-connector.

For this demonstration I’ll be using my Edge Transport server simply because it has slightly more interesting data since it receives a lot of connections from the internet.

Get Top Sender IP’s from Protocol Logs with Log Parser

To get the top sender IP’s from the protocol logs we can use this Log Parser query.

SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,
	REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name,
	Count(*) as Hits
FROM *.log
WHERE data LIKE '%EHLO%'
GROUP BY IP
ORDER BY Hits DESC

When run from the folder containing the protocol logs (in this case C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\ProtocolLog\SmtpReceive) it looks like this:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT EXTRACT_PREFIX(remote-endpoint,0,':') as IP,REVERSEDNS(EXTRACT_PREFIX(remote-endpoint,0,':')) as Name,Count(*) as Hits from *.log WHERE data LIKE '%EHLO%' GROUP BY IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -rtp:-1

This will give you output similar to this:

IP              Name                                    Hits
--------------- --------------------------------------- ----
83.222.31.220   v8622.vps.masterhost.ru                 52
204.13.248.72   mho-02-ewr.mailhop.org                  12
50.78.250.97    dcmail.designercabinetry.com            9
10.1.1.21       ho-ex2010-mb1.exchangeserverpro.net     8
64.61.92.26     static-64-61-92-26.isp.broadviewnet.net 7
217.108.179.228 mailhost.el-internationale.com          7
69.60.118.117   mail1.ambr.com.br                       4
10.1.1.22       ho-ex2010-mb2.exchangeserverpro.net     4
95.154.196.147  95.154.196.147                          4
118.22.2.202    pc2.land-ho-unet.ocn.ne.jp              3
187.108.193.223 cloud.newmediahost.com.br               2
109.169.77.169  109.169.77.169                          2
59.106.64.208   ns1.uranaikan.info                      2
204.13.248.71   mho-01-ewr.mailhop.org                  2
78.129.222.16   78.129.222.16                           2
199.119.76.15   mail.seoauditions.com                   1

Statistics:
-----------
Elements processed: 3359
Elements output:    16
Execution time:     17.41 seconds

This part of the query string is important to note:

WHERE data LIKE '%EHLO%'

This means that only those log entries where the EHLO occurred will be counted in the stats that Log Parser outputs. If you leave it out you’ll see a “Hit” for every log entry a remote IP generated. Depending on how “chatty” that particular SMTP conversation was it may skew the results a little. However since we’re looking more for indicative numbers rather than precise numbers it doesn’t matter which way you choose to go (at least not to me).

Get Top Sender IP’s from Message Tracking Logs with Log Parser

For message tracking logs the syntax is a little different because the field names in the log files are different.

SELECT client-ip as IP,
	REVERSEDNS(client-ip) as Name,
	Count(*) as Hits
FROM *.log
WHERE (event-id='RECEIVE')
GROUP BY IP
ORDER BY Hits DESC

When run from the folder containing the message tracking logs (in this case C:\Program Files\Microsoft\Exchange Server\V14\TransportRoles\Logs\MessageTracking) it will look like this:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT client-ip as IP,REVERSEDNS(client-ip) as Name,Count(*) as Hits from *.log WHERE (event-id='RECEIVE') GROUP BY IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -rtp:-1

If you get too much output you can limit it to the top X results by modifying the query slightly:

"C:\Program Files (x86)\Log Parser 2.2\logparser.exe" "SELECT TOP 20 client-ip as IP,REVERSEDNS(client-ip) as Name,Count(*) as Hits from *.log WHERE (event-id='RECEIVE') GROUP BY IP ORDER BY Hits DESC" -i:CSV -nSkipLines:4 -rtp:-1

This will give you output similar to this:

IP              Name                                 Hits
--------------- ------------------------------------ ----
204.93.210.179  mariajunco.com                       32
10.1.1.22       ho-ex2010-mb2.exchangeserverpro.net  23
216.151.172.180 hosted.airvm.net                     22
10.1.1.21       ho-ex2010-mb1.exchangeserverpro.net  22
83.142.48.139   83.142.48.139                        17
67.215.235.199  67.215.235.199.static.quadranet.com  13
109.169.76.124  109.169.76.124                       10
109.169.55.146  109.169.55.146                       10
109.169.62.15   109.169.62.15                        10
109.169.60.137  109.169.60.137                       9
173.254.208.113 173.254.208.113.static.quadranet.com 9
59.106.64.208   ns1.uranaikan.info                   8
72.11.150.131   72.11.150.131.static.quadranet.com   7
109.169.73.116  109.169.73.116                       7
109.169.55.135  109.169.55.135                       7
189.39.9.214    mail3.ibcbrasil.com.br               5
204.13.248.72   mho-02-ewr.mailhop.org               5
109.169.87.100  109.169.87.100                       4
109.169.84.105  109.169.84.105                       4
169.232.46.177  out-58.smtp.ucla.edu                 3

Statistics:
-----------
Elements processed: 1018
Elements output:    20
Execution time:     74.03 seconds (00:01:14.03)

You can use this information in a lot of situations such as when investigating load issues, or planning to decommission servers