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

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


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:


This will give you output similar to this:


This part of the query string is important to note:


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.


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:


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


This will give you output similar to this:


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

Comments

  1. Alex says

    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

    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

  4. Cathy says

    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

    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

      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

    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

  7. ISMAIL KHAN W says

    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

    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

    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

  10. Adeel says

    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

    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

Leave a Reply

You have to agree to the comment policy.