2

I have a modified log file listing all the IP addresses which have accessed my server.

Next to the IP address on each line is a date/time stamp.

In my quest to filter bots and malicious activity, I would like to check if any IP addresses have made more than 5 requests in less than 15 seconds.

I have already posted a question on how to measure this from the first request, but I would like to take this further.

I am currently learning (G)AWK, and I would like to learn more. If possible, I would like this to be completed in (G)AWK and explained as explicitly as possible, so that I can study the script and, hopefully in the future, I will be able to write similar scripts myself.

While I understand this can most likely be done in Python, I am not currently learning Python.

Sample input

Here is a full (working) modified (to obfuscate the IP addresses) version of my log file: log-file.csv.

And, below is a [further modified] sample of the file, in case you prefer to test your results against that instead:

03/Nov/2020:06:33:09|000.000.000.001|200|/page-1/
03/Nov/2020:07:12:21|000.000.000.002|200|/page-2/
03/Nov/2020:07:24:52|000.000.000.003|200|/page-3/
03/Nov/2020:07:30:50|000.000.000.004|200|/page-4/
03/Nov/2020:07:47:29|000.000.000.005|200|/page-5/
03/Nov/2020:07:52:42|000.000.000.006|200|/page-6/
03/Nov/2020:07:52:55|000.000.000.007|200|/page-7/
03/Nov/2020:08:00:11|000.000.000.008|200|/page-8/
03/Nov/2020:08:05:00|000.000.000.009|200|/page-9/
03/Nov/2020:08:05:06|000.000.000.010|301|/page-10/
03/Nov/2020:08:05:32|000.000.000.007|200|/page-11/
03/Nov/2020:09:02:49|000.000.000.011|304|/page-12/
03/Nov/2020:09:02:49|000.000.000.011|404|/page-13/
03/Nov/2020:09:13:18|000.000.000.011|304|/page-14/
03/Nov/2020:09:13:19|000.000.000.011|404|/page-15/
03/Nov/2020:09:14:20|000.000.000.012|200|/page-16/
03/Nov/2020:09:23:48|000.000.000.011|304|/page-17/
03/Nov/2020:09:23:49|000.000.000.011|404|/page-18/
03/Nov/2020:09:34:19|000.000.000.011|304|/page-19/
03/Nov/2020:09:34:19|000.000.000.011|404|/page-20/
03/Nov/2020:09:35:42|000.000.000.013|301|/page-21/
03/Nov/2020:09:35:42|000.000.000.013|404|/page-22/
03/Nov/2020:09:44:49|000.000.000.011|304|/page-23/
03/Nov/2020:09:44:49|000.000.000.011|404|/page-24/
03/Nov/2020:09:53:38|000.000.000.014|200|/page-25/
03/Nov/2020:09:55:19|000.000.000.011|304|/page-26/
03/Nov/2020:09:55:19|000.000.000.011|404|/page-27/
03/Nov/2020:10:05:49|000.000.000.011|304|/page-28/
03/Nov/2020:10:05:49|000.000.000.011|404|/page-29/
03/Nov/2020:10:06:27|000.000.000.005|200|/page-30/
03/Nov/2020:10:16:19|000.000.000.011|304|/page-31/
03/Nov/2020:10:16:19|000.000.000.011|404|/page-32/
03/Nov/2020:10:17:21|000.000.000.015|200|/page-33/
03/Nov/2020:10:20:35|000.000.000.016|200|/page-34/
03/Nov/2020:10:20:37|000.000.000.017|404|/page-35/
03/Nov/2020:10:20:42|000.000.000.017|404|/page-39/
03/Nov/2020:10:20:49|000.000.000.016|200|/page-40/
03/Nov/2020:10:20:55|000.000.000.017|404|/page-41/
03/Nov/2020:10:21:01|000.000.000.017|404|/page-42/
03/Nov/2020:10:21:03|000.000.000.017|404|/page-43/
03/Nov/2020:10:21:05|000.000.000.017|404|/page-44/
03/Nov/2020:10:21:06|000.000.000.017|404|/page-45/
03/Nov/2020:10:21:11|000.000.000.017|404|/page-46/
03/Nov/2020:10:21:14|000.000.000.016|200|/page-47/
03/Nov/2020:10:21:34|000.000.000.016|200|/page-48/
03/Nov/2020:10:21:47|000.000.000.016|200|/page-49/
03/Nov/2020:10:22:14|000.000.000.016|200|/page-50/
03/Nov/2020:10:22:15|000.000.000.016|200|/page-51/
03/Nov/2020:10:22:15|000.000.000.016|200|/page-52/
03/Nov/2020:10:22:16|000.000.000.016|200|/page-52/
03/Nov/2020:10:22:17|000.000.000.016|200|/page-53/
03/Nov/2020:10:22:18|000.000.000.019|200|/page-1/
03/Nov/2020:10:22:20|000.000.000.016|200|/page-55/
03/Nov/2020:10:22:20|000.000.000.016|200|/page-56/

The desired output

I would like you to produce a document named bot-list.txt, containing a list of IP addresses which have made 5 requests or more in less than 15 seconds at any time (not necessarily the first 5). I may want to adjust the frequency/duration at a later date.

Because the log file is large, I don't feel it would be appropriate to post the whole thing here. However, the output of the (modified) sample above is only 2 IP addresses. Thus, it will be considerably more reliable to test the full log file.

000.000.000.017
000.000.000.016

Please be explicit in how you achieved the result. As an added bonus, if you do use any obscure technique/function, I would be pleased if you can point to a reference where I can learn more about this technique.

So, to be clear:

  • Search for each unique IP in log-file.csv
  • Where there are 5 or more instances of this IP, calculate the difference in seconds between them using the date/time stamp on each line.
  • Isolate IP addresses where 5 pages (or more) are accessed in less than 15 seconds.
  • Append these IP address to bot-list.txt.
  • If possible, complete the task in a single (G)AWK script.

Further advice on how to improve on this concept would be welcome.

What I have tried

I honestly don't know how to compare rows in GAWK, but it is clear from some of the answers in my previous question, that it is possible.

Currently, I am reading through the book "Effective AWK Programming". It is very interesting, but I struggle with some of it. I am also looking into associative arrays, but as I don't come from a programming background, I am picking this knowledge up slowly.

I cannot find anything which solves my specific problem and videos on this subject are few and far between.

If anyone can point to helpful resources which can help me to solve problems like this, I would be appreciative.

My attempt at comparing the dates:

egrep "000.111.000.111" log-file.csv | awk 'BEGIN{FS="|"; ORS=" "} NR==1 || NR==5 {print $1,$2}' | sed -e 's/[\/:]/\ /g' -e 's/Jan/1/g' -e 's/Feb/2/g' -e 's/Mar/3/g' -e 's/Apr/4/g' -e 's/May/5/g' -e 's/Jun/6/g' -e 's/Jul/7/g' -e 's/Aug/8/g' -e 's/Sep/9/g' -e 's/Oct/10/g' -e 's/Nov/11/g' -e 's/Dec/12/g' | awk '{print $3,$2,$1,$4,$5,$6 "," $10,$9,$8,$11,$12,$13","$14}' | awk -F, '{d2=mktime($2);d1=mktime($1);print d2-d1, $3}'  | awk '{if($1<15)print $2}' >> bot-list.txt

Unfortunately, as you can see, I could not do the whole thing in (G)AWK, but I would appreciate it if you can.

(This is my second time using Stack Exchange. I have attempted to follow advice on how to lay out my question, I am happy to receive further advice if I have made any errors).

Thank you.

Improve this question
11
  • 1
    I'm sorry this has been dowvoted while other questions with no attempt/research get upvoted here. Anyway, the main hassle here is the timestamp. It will be a major pain to verify if two timestamps in that format are 15 seconds apart. It would be so much easier if it was in Epoch time. Do you have control over the server to change this bit?
    – Quasímodo
    Nov 4, 2020 at 20:50
  • Hi @Quasímodo , I do have control over the server, and I am happy to make adjustments, but I will warn that I am not a programmer or sever admin, so my knowledge of servers is fairly limited. The data originates from nginx.
    – Bumbling Badger
    Nov 4, 2020 at 21:40
  • 1
    I wouldn't worry about it since it's trivial to convert date+time to epoch seconds with GNU awk as seen in the answers to your previous question
    – Ed Morton
    Nov 4, 2020 at 21:46
  • 1
    You said it will be considerably more reliable to test the full log file. but you didn't tell us what the expected output would be for the full log file so we can't know if a text passed or failed. The script in my answer output 126 IP addresses from your full log file but idk if that's the right or wrong number we should get.
    – Ed Morton
    Nov 4, 2020 at 22:24
  • @EdMorton You make a good point. My logic here is flawed. I guess what I meant to say was that given the small number of results that could be gleaned from my sample, the log file could provide more meaningful results, however, as you correctly stated, it is difficult to know, when answering the question when you have hit the mark. My thoughts: To take meaningful results, it would seem to me to be best to run a script against a large data sample. But, here lies the issue... How do I provide an accurate desired outcome on a large sample if I need a script to do it accurately? Advice welcome.
    – Bumbling Badger
    Nov 5, 2020 at 6:45

1 Answer 1

Reset to default
3

A simple, brute-force approach that reads all of your IP addresses and timestamps after converting to epoch seconds into memory (the secs[] array) then once the whole file has been read loops through the array one IP address at a time looking at the difference in timestamps every 5 entries apart to see if any are less than 15 secs:

$ cat tst.awk
BEGIN { FS="|" }
{
    split($1,t,"[/:]")
    monthNr = (index("JanFebMarAprMayJunJulAugSepOctNovDec",t[2])+2)/3
    currSecs = mktime(t[3] " " monthNr " " t[1] " " t[4] " " t[5] " " t[6])

    secs[$2][++count[$2]] = currSecs
}
END {
    range = 5
    for (ip in secs) {
        for (beg=1; beg<=(count[ip]-range)+1; beg++) {
            end = beg + range - 1
            if ( (secs[ip][end] - secs[ip][beg]) < 15 ) {
                print ip
                break
            }
        }
    }
}


$ awk -f tst.awk file
000.000.000.016
000.000.000.017

It does mean you need to have enough memory to store the list of IP addresses and epoch seconds but your file would have to be massive (billions of lines) for that to become an issue.

EDIT: per the comments under your question your input file isn't in the correct order (incrementing date+time) so we can do this to fix that:

$ awk -F'|' '
    {
        split($1,t,"[/:]")
        monthNr = (index("JanFebMarAprMayJunJulAugSepOctNovDec",t[2])+2)/3
        currSecs = mktime(t[3] " " monthNr " " t[1] " " t[4] " " t[5] " " t[6])

        print currSecs, NR, $0
    }
' log-file.csv | sort -k1,1n -k2,2n | cut -d' ' -f3- > sorted-log-file.csv

and then run the above script on THAT to get the following 50 IP addresses output:

$ awk -f tst.awk sorted-log-file.csv | sort
000.000.000.011
000.000.000.017
000.000.000.036
000.000.000.056
000.000.000.066
000.000.000.094
000.000.000.115
000.000.000.121
000.000.000.136
000.000.000.141
000.000.000.157
000.000.000.169
000.000.000.178
000.000.000.181
000.000.000.183
000.000.000.208
000.000.000.227
000.000.000.230
000.000.000.283
000.000.000.312
000.000.000.354
000.000.000.361
000.000.000.368
000.000.000.370
000.000.000.505
000.000.000.515
000.000.000.579
000.000.000.580
000.000.000.588
000.000.000.610
000.000.000.634
000.000.000.642
000.000.000.651
000.000.000.748
000.000.000.756
000.000.000.758
000.000.000.772
000.000.000.795
000.000.000.814
000.000.000.852
000.000.000.878
000.000.000.882
000.000.000.922
000.000.000.994
000.000.000.995
000.000.001.047
000.000.001.119
000.000.001.139
000.000.001.185
000.000.001.198
Improve this answer
4
  • Thank you. After fixing the log file, your script now delivers 55 IPs. I compared this to the previous script (for the last question) which delivered 40 on this particular log file.
    – Bumbling Badger
    Nov 8, 2020 at 3:49
  • You're welcome, see stackoverflow.com/help/someone-answers for what to do next.
    – Ed Morton
    Nov 8, 2020 at 13:21
  • I just want to check that the script matches the current format. From memory the script matched 128 IPs. Since the script was reordered by date, the same script matches 55. The reason could be that, due to my error, there were false positives or that, because the data is fresh, the user behaviour has changed. I realise from our previous discussions that this makes it tricky to measure as I cannot easily judge the performance of the script. For this reason, If you feel the new date order doesn't negatively affect the outcome of the script, I am happy to mark your solution as the answer.
    – Bumbling Badger
    Nov 8, 2020 at 21:39
  • 1
    The script is accurate.
    – Ed Morton
    Nov 8, 2020 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.