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

About the Author

Paul Cunningham

Paul is a former Microsoft MVP for Office Apps and Services. He works as a consultant, writer, and trainer specializing in Office 365 and Exchange Server. Paul no longer writes for Practical365.com.

Comments

  1. Alex

    Thank you!
    Helped me a lot to find jobs sending logs by SMTP or scanners, etc.

    Because our biggest photocopier often used for scan to mail didn’t show up I found a little problem.
    For example our Canon devices don’t send EHLO but HELO.
    I added “or data LIKE ‘%HELO%’ ” and got some more Clients using the receive connector.
    WHERE data LIKE ‘%EHLO%’ or data LIKE ‘%HELO%’

    Alex

  2. Tomás

    Hi Paul,
    Great article!. I have a problem when I try to add date time, can you help me?.

    PS 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, TO_LOCALTIME(TO_TIMESTAMP(EXTRACT_PREFIX(TO_STRING([#Fields: date-time]),0,’T’), ‘yyyy-MM-dd’)) AS LogDate from c:\tools\*.log WHERE data LIKE ‘%EHLO%’ GROUP BY IP ORDER BY Hits DESC” -i:CSV -nSkipLines:4 -rtp:-1
    Error: Semantic Error: SELECT clause field-expression “LogDate” is not an aggregate function and does not contain GROUP BY field-expressions

    Thanks

  3. Leonardo

    Thank You Paul, You helped me a lot!

  4. Cathy

    Hey Paul,

    I don’t find a field ‘Client-IP’ in my exchange 2010 Hub. Do I need to turn it on somewhere?

    #Software: Microsoft Exchange Server
    #Version: 14.0.0.0
    #Log-type: SMTP Receive Protocol Log
    #Date: 2017-12-03T23:34:13.313Z
    #Fields: date-time,connector-id,session-id,sequence-number,local-endpoint,remote-endpoint,event,data,context

  5. Paul McCoy

    Hi Paul,

    I know I’m missing something very easy. This is being run in CMD as admin on Exchange 2016 Edge role:

    C:\>”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
    Error: SELECT clause: Syntax Error: unknown field ‘client-ip’

    To see valid fields for the CSV input format type:
    LogParser -h -i:CSV

    C:\>

    I opened a log file to double check and it seems to be available:

    #Software: Microsoft Exchange Server
    #Version: 15.01.0669.032
    #Log-type: Message Tracking Log
    #Date: 2017-11-30T14:00:03.535Z
    #Fields: date-time,client-ip,client-hostname,server-ip,server-hostname, etc.

    I’m planning to run this as a scheduled task, once a day.

    Thanks

    Paul

    1. Paul McCoy

      I found it, just need to run it from:

      C:\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\MessageTracking>

      Thanks again

  6. AlanW

    Hi,

    I get the following error: any idea what I might be doing wrong?

    Unexpected token ‘SELECT client-ip as IP,REVERSEDNS(client-ip) as Name,Count(*) as Hits from *.log WHERE (event-id=’REC
    EIVE’) GROUP BY IP ORDER BY Hits DESC’ in expression or statement.
    At line:1 char:195
    + “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
    + CategoryInfo : ParserError: (SELECT client-i…ER BY Hits DESC:String) [], ParentContainsErrorRecordExc
    eption
    + FullyQualifiedErrorId : UnexpectedToken

    1. Paul Cunningham

      Are you running in a CMD prompt or a PowerShell console? Should be CMD.

      1. AlanW

        That was it, thank you!

  7. ISMAIL KHAN W

    Hi Paul Cunningham,

    Hope your doing good?

    I am just looking for the SMTP Receive Connector Log Parser Queries. Identify the whichever possible

    Thanks & Regards
    Ismail khan

  8. Rajnayan

    Hello,

    I have large size logs and need find the reverse proxy and the count of the distinct uri. The format of the log is as below:

    Jan 29 0:03:07 fpp-mp-a01 127.3.0.0 – 33.42.670.281, 126.7.0.0 akman_t1 CN=U-100927121845499116,OU=K,OU=A,OU=External,OU=Persons,O=indigo Form [29/Jan/2014:00:03:07 +0200] POST /amm-server-serv4/main HTTP/1.1 200 246 6454 – Java/1.8.0_45

    Jan 29 0:03:07 fpp-mp-a01 127.3.0.0 – 81.58.160.252 Not Protected [29/Jan/2014:00:03:07 +0200] GET /flyworld HTTP/1.1 302 494 452 – Wget/1.11.4 Red Hat modified

    Here, I want to find out all the uri after the method “GET /flyworld” i.e fly world, amm-server-serv4, etc. in the logs and the total sum of count.
    There seems no tab between fields as its in notepad. I copied the logs into excel file in order to get a view of it. I paste below the exact log from Notepad: –

    Jun 29 00:03:24 frd-rp-p01 127.6.0.0 – “82.241.3.207, 127.4.0.0” “-” “-” “Auto” [29/Jun/2015:00:03:24 +0200] “GET /_layouts/1033/ie55up.js?rev=Ni7%2Fj2ZV%2FzCvd09XYSSWvA%3D%3D HTTP/1.1” 200 105258 103702 “w4.flygo.com/sites/F3D_Extended_FBI_MSI/_layouts/airNotif/…{D57‌​EACDA-FF62-433E-9375-511C53EB2E3B}&itemid=1790” “Mozilla/5.0 (Windows NT 6.2; WOW64; rv:38.0) Gecko/20100101 Firefox/38.0”

    Can you please help?

  9. Adeel Memon

    Hey Paul;

    is there a way to include the receive connector name in the script?

    “C:Program Files (x86)Log Parser 2.2logparser.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

    I tried

    “C:Program Files (x86)Log Parser 2.2logparser.exe” “SELECT connector-id, 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

    1. Paul Cunningham

      No, because it is grouping by IP address. Each IP may be hitting multiple receive connectors.

      1. Adeel Memon

        I thought each IP would hit only the receive connectors it’s either specifically allowed to or via default connector if it’s open.

        1. Paul Cunningham

          Possibly.

          But more importantly, Log Parser GROUP BY doesn’t let you specify multiple fields to group by, as far as I can tell.

  10. Adeel

    Is there a way to use Log Parse to provide the same report from a CSV file.

    I generated the CSV file via Get-transportserver | Get-messageTrackingLog -ResultSize unlimited -EventID Receive | export-csv d:senders.csv -NoTypeinformation

    This pulls all my hub servers, I would like to then provide a report of top senders via top senders IP.

    Thanks

  11. Chris

    Very useful, thanks for sharing.

    A script provinding top MB senders per day would be great, I mean bandwith killers (I’m quite sure each company has some users that does not take care of attaching heavy files…)

    1. Paul Cunningham

      Stay tuned, will try to put something together in the next week or so 🙂

      1. Andrew

        HI Paul, did you ever get around to putting this script together?

  12. Charles Derber

    Thanks & its been informative…Normally we analyze using Ironport 🙂

Leave a Reply