• Home
  • Topics
    • Office 365
    • Teams
    • SharePoint Online
    • Exchange 2019
    • Exchange 2016
    • Exchange 2013
    • Hybrid
    • Certificates
    • PowerShell
    • Migration
    • Security
    • Azure
  • Blog
  • Podcast
  • Webinars
  • Books
  • About
  • Videos
    • Interview Videos
    • How To Guide Videos
  • Subscribe
    • Facebook
    • Twitter
    • RSS
    • YouTube

Practical 365

You are here: Home / Exchange Server / Report Top Sender IP's on Exchange Server 2010 using Log Parser

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

May 9, 2012 by Paul Cunningham 24 Comments

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.

1
2
3
4
5
6
7
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:

1
"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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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:

1
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.

1
2
3
4
5
6
7
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:

1
"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:

1
"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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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

Exchange Server Edge Transport, Exchange 2010, Hub Transport, Log Parser, Message Tracking, Protocol Logs

Comments

  1. Alex says

    January 31, 2022 at 9:59 am

    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

    Reply
  2. Tomás says

    March 12, 2020 at 7:46 pm

    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

    Reply
  3. Leonardo says

    March 14, 2018 at 3:57 am

    Thank You Paul, You helped me a lot!

    Reply
  4. Cathy says

    December 6, 2017 at 5:41 am

    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

    Reply
  5. Paul McCoy says

    December 1, 2017 at 3:41 am

    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

    Reply
    • Paul McCoy says

      December 1, 2017 at 3:57 am

      I found it, just need to run it from:

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

      Thanks again

      Reply
  6. AlanW says

    November 16, 2017 at 3:12 am

    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

    Reply
    • Paul Cunningham says

      November 16, 2017 at 6:18 am

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

      Reply
      • AlanW says

        November 17, 2017 at 7:30 pm

        That was it, thank you!

        Reply
  7. ISMAIL KHAN W says

    January 31, 2017 at 10:31 am

    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

    Reply
  8. Rajnayan says

    August 6, 2015 at 1:53 am

    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?

    Reply
  9. Adeel Memon says

    December 17, 2013 at 1:21 am

    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

    Reply
    • Paul Cunningham says

      December 17, 2013 at 8:36 pm

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

      Reply
      • Adeel Memon says

        December 18, 2013 at 5:06 am

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

        Reply
        • Paul Cunningham says

          December 18, 2013 at 6:59 pm

          Possibly.

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

          Reply
  10. Adeel says

    August 15, 2013 at 3:06 am

    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

    Reply
  11. Chris says

    May 18, 2012 at 6:35 pm

    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…)

    Reply
    • Paul Cunningham says

      May 20, 2012 at 9:35 pm

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

      Reply
      • Chris says

        May 21, 2012 at 5:47 pm

        Thank you Paul!

        Reply
      • Andrew says

        July 20, 2013 at 9:35 am

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

        Reply
        • Paul Cunningham says

          July 23, 2013 at 7:57 pm

          No I didn’t.

          Reply
  12. Charles Derber says

    May 10, 2012 at 10:42 pm

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

    Reply

Leave a Reply Cancel reply

You have to agree to the comment policy.

Recent Articles

  • Monitoring Microsoft Information Protection with Microsoft Sentinel
  • Three Steps to Securing Microsoft Teams
  • Turn On MFA: Real-World Example of Fraud, Domain Stealing, and the Nearly Lost House Deposit
  • Changes in Microsoft 365 Apps Channels and Why You Should Care
  • A New Tool to Manage Exchange-related Attributes Without Exchange Server

Copyright © 2022 Quadrotech Solutions AG · Disclosure · Privacy Policy
Alpenstrasse 15, 6304 Zug, Switzerland