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
Thanks Paul, I was trying to see what was hitting my Receive connectors before shutting down on-prem. Your guidance got me to here which i thought others might use:
SELECT DISTINCT EXTRACT_PREFIX(remote-endpoint,0,’:’) AS IP,connector-id,data
FROM ‘[LOGFILEPATH]’
WHERE data LIKE ‘%EHLO%’ or data LIKE ‘%HELO%’
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
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
Thank You Paul, You helped me a lot!
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
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
I found it, just need to run it from:
C:\Program Files\Microsoft\Exchange Server\V15\TransportRoles\Logs\MessageTracking>
Thanks again
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
Are you running in a CMD prompt or a PowerShell console? Should be CMD.
That was it, thank you!
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
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/…{D57EACDA-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?
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
No, because it is grouping by IP address. Each IP may be hitting multiple receive connectors.
I thought each IP would hit only the receive connectors it’s either specifically allowed to or via default connector if it’s open.
Possibly.
But more importantly, Log Parser GROUP BY doesn’t let you specify multiple fields to group by, as far as I can tell.
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
Pingback: Troubleshooting Email Delivery with Exchange Server Protocol Logging
Pingback: Log Parser's EXTRACT_PREFIX and EXTRACT_SUFFIX Functions
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…)
Stay tuned, will try to put something together in the next week or so 🙂
Thank you Paul!
HI Paul, did you ever get around to putting this script together?
No I didn’t.
Thanks & its been informative…Normally we analyze using Ironport 🙂