9

I am looking to compare the times that each user (IP address), that has visited my site, took to view a series of pages, to identify non-human activity on the website, and thus exclude the IP addresses from my analytics.

I would like to use awk (I am using GAWK) where possible, simply because I am learning it and want to improve. However, I am open to using other tools in bash.

I have a modified log file (output.csv) in the following format:

29/Oct/2020:07:41:42|111.111.111.111|200|/page-a/
29/Oct/2020:08:30:40|000.111.026.111|200|/page-a/
29/Oct/2020:08:30:44|000.111.026.111|200|/page-b/
29/Oct/2020:08:30:45|000.111.026.111|200|/page-c/
29/Oct/2020:08:30:47|000.111.026.111|200|/page-d/
29/Oct/2020:08:30:47|220.171.008.221|200|/page-h/
29/Oct/2020:08:30:48|000.111.026.111|200|/page-e/
29/Oct/2020:08:41:49|221.651.943.323|200|/page-a/
29/Oct/2020:08:41:52|060.121.125.144|200|/page-f/
29/Oct/2020:08:41:52|060.121.125.144|200|/page-g/
29/Oct/2020:08:41:54|000.111.026.111|200|/page-k/
29/Oct/2020:08:41:55|060.121.125.144|200|/page-l/
29/Oct/2020:08:41:57|060.121.125.144|200|/page-n/
29/Oct/2020:08:41:58|060.121.125.144|200|/page-s/

I would like to do the following:

  • search for each unique IP in output.csv
  • where there are 5 or more instances of this IP, calculate the difference in seconds between the first and fifth date/time on each line
  • isolate IP addresses where 5 pages are accessed in less than 15 seconds
  • append these IP address to file.txt

What I have tried

To get the time difference in seconds between a specific number of instances of an IP address, I used the following set of commands:

egrep "000.111.000.111" output.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}' >> file.txt

If 5 pages are accessed in under 15 seconds by the given IP address, the command above appends the IP to a file.

While this works, I am looking for a way to do this across all unique IPs in a single command/script.

I am also open to any more elegant approaches as I can see my approach is cumbersome.

The desired result

The desired result is a file with a list of IP addresses which all accessed the server at a rate of more than 5 pages in 14 seconds (timing can be adjusted).

Eg. the contents of file.txt, given the example above, would be:

000.111.026.111
060.121.125.144

Ideally, I would appreciate it if you could go through your method step by step to explain how it works as this will help me to learn.

1
  • Welcome to U&L, Bumbling Badger! Commentary on the answers should go under those answers, not as part of the Question, please. Stack Exchange indicates "solved" (answered) questions when the asker selects an Answer using the checkmark, as you have. Thank you for your question!
    – Jeff Schaller
    Nov 4 '20 at 14:15
4

Using GNU awk for mktime():

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

    if ( count[$2] == 1 ) {
        firstSecs[$2] = currSecs
    }
    else if ( (currSecs - firstSecs[$2]) < 15 ) {
        print $2
    }
}

$ awk -f tst.awk file
000.111.026.111
060.121.125.144

I think it's very clear what it's doing so no need to add text explaining it but if you have any questions please feel free to ask.

Oh, and you mentioned in a comment wishing you knew a way to convert your IP addresses to dummy values so you could post a more comprehensive example, well here's one way that'd be good enough for your specific problem:

$ awk '
    BEGIN { FS=OFS="|" }
    !($2 in map) { ip=sprintf("%012d",++cnt); gsub(/.../,"&.",ip); sub(/.$/,"",ip); map[$2]=ip }
    { $2=map[$2]; print }
' file
29/Oct/2020:07:41:42|000.000.000.001|200|/page-a/
29/Oct/2020:08:30:40|000.000.000.002|200|/page-a/
29/Oct/2020:08:30:44|000.000.000.002|200|/page-b/
29/Oct/2020:08:30:45|000.000.000.002|200|/page-c/
29/Oct/2020:08:30:47|000.000.000.002|200|/page-d/
29/Oct/2020:08:30:47|000.000.000.003|200|/page-h/
29/Oct/2020:08:30:48|000.000.000.002|200|/page-e/
29/Oct/2020:07:41:49|000.000.000.004|200|/page-a/
29/Oct/2020:08:41:52|000.000.000.005|200|/page-f/
29/Oct/2020:08:41:52|000.000.000.005|200|/page-g/
29/Oct/2020:08:41:54|000.000.000.002|200|/page-k/
29/Oct/2020:08:41:55|000.000.000.005|200|/page-l/
29/Oct/2020:08:41:57|000.000.000.005|200|/page-n/
29/Oct/2020:08:41:58|000.000.000.005|200|/page-s/

Edit: here's how you could have started to investigate the difference between the output my script produces and the output the version of Daves script you ran produces:

$ awk -f morton-botfilter.awk.txt output3test.csv > morton.out
$ awk -f dave-botfilter.awk.txt output3test.csv > dave.out
$ ip=$(comm -13 <(sort morton.out) <(sort dave.out) | head -1)
$ grep "$ip" output3test.csv | head -5
03/Nov/2020:07:52:55|000.000.000.007|200|/page-7/
03/Nov/2020:08:05:32|000.000.000.007|200|/page-11/
03/Nov/2020:11:28:56|000.000.000.007|200|/page-77/
03/Nov/2020:13:52:32|000.000.000.007|200|/page-143/
03/Nov/2020:13:52:33|000.000.000.007|200|/page-144/

Note that there's far longer than 15 seconds between the first and last timestamps above which tells you that the script in dave-botfilter.awk.txt is broken. See the comments below for more info.

10
  • Awesome! Thanks. Can a similar technique be used to obfuscate the pages (to perhaps match the above example)? Pages are in the format "/text-is-here/" or "/" for home page. Don't worry if it's a bother. If I were to share such a file with those who wanted to test with it. Where would you put it? I could share elsewhere (which I would be happy to do) and link to it, but I don't know the guidelines on that. Nov 2 '20 at 7:38
  • Sure, just do the same for $4 as I''m doing for $2 but using something like {$4="/page-"(++cnt4)"/"}. Idk where you'd put a large CSV, I saw someone recently put something on pastebin, but generally people on this forum don't want to see large examples anyway, they won't you to come up with a minimal example that demonstrates your problem and fits in your question.
    – Ed Morton
    Nov 2 '20 at 12:40
  • Btw I don't know about the other answers but if Dave's script and my script aren't outputting exactly the same set of IP address then there is some magic associated with your data as they both do exactly the same comparisons in exactly the same ways, the only significant functional difference is how we get the month number from the month name.
    – Ed Morton
    Nov 2 '20 at 15:36
  • Weird. OK I will run both scripts again and give the exact numbers. and how I am running both scripts. Apologies, because I am only just touching on arrays (and to be quite honest I don't understand them yet), I couldn't read that the scripts did the same things. Perhaps it is the way I run the scripts or something odd about the data file? I will get back to you on this. Nov 3 '20 at 0:05
  • I removed any other filters I was using, to get the most accurate result and your script delivers 94 IP addresses, while Dave's returns 158 IP addresses. I can't be sure why as I am not familiar with all the techniques you both used, as yet. I have used your command to clean up the file (thanks) and the results are the same, if you would like to have a look, I have placed the output csv file and the two awk scripts (morton-botfilter.awk and dave-botfilter.awk) in a shared folder awk-ip-filter. I'd like to know if I have messed up in some way. Nov 4 '20 at 2:05
3

Since you want to learn awk, and apparently have GNU awk (gawk), awk -f script <logfile where script contains

BEGIN{ split("Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec",n2m);
  for(i=1;i<=12;i++) m2n[n2m[i]]=i; FS="|"; }
function fixtime(str ,tmp){ split(str,tmp,"[:/]");
  return mktime(tmp[3] OFS m2n[tmp[2]] OFS tmp[1] OFS tmp[4] OFS tmp[5] OFS tmp[6]) }
++count[$2]==1 { first[$2]=fixtime($1) }
count[$2]==5 && fixtime($1)-first[$2]<15 { print $2 }

The first two lines set up an array m2n (month to number) which maps Jan to 1, Feb to 2, etc. and also sets the field delimiter to |. (It could instead do m2n["Jan"]=1; m2n["Feb"]=2; etc but that's more tedious.

The next two lines define a function which splits your time format using all / and : as delimiters (without needing to first translate them to space), converts the month name to a number, reorders as needed and feeds to mktime() (gawk only). Instead of OFS (which defaults to one space and hasn't been changed) you can use literal " " but I find that uglier.

The fifth and sixth lines find the first occurrence of any IPaddr and remember its timestamp, and the fifth occurrence of the same IPaddr and compare its timestamp to the remembered one to see if the interval is less than 15 seconds. Some people would put a ;next in the action on the fifth line to make clear that the fifth and sixth script lines will not execute on the same record (i.e. data line) but I didn't bother.

QEF.

If you prefer you can put the whole script on the commandline in '...' instead of using a script file, but I don't like doing that for more than about 100 characters.

5
  • Wow! That is a monster! I am going to have to go through this slowly to try to understand it. I really appreciate the notes. Yes I am using GAWK, sorry I will edit to make that clear. Fascinating stuff. Thanks Dave! Oct 31 '20 at 2:13
  • 4
    That's really hard to read - if you run gawk -o- to pretty-print it it'll be much easier to understand.
    – Ed Morton
    Oct 31 '20 at 4:39
  • 1
    @EdMorton Thank you. Oct 31 '20 at 4:57
  • This seems to do the trick. Complex for me to understand, but effective nevertheless. Thank you Dave! Nov 1 '20 at 2:11
  • @dave_thompson_085. Hey there. If you are interested, I won't be offended if not, I have another question which is a follow on from this one. Use AWK to Compare Times in a Column. Nov 6 '20 at 5:52
1
#!/bin/bash
awk -v mon=$(locale abmon) -v FS='[/:|]' '
BEGIN           {for(n=split(mon, M, ";"); n; n--) Mn[M[n]]=n}
!A[$7]++        {IP[$7] = mktime($3" "Mn[$2]" "$1" "$4" "$5" "$6)}
A[$7]==5 && mktime($3" "Mn[$2]" "$1" "$4" "$5" "$6) - IP[$7] < 15 {print $7}
' file > bot_ip

-v mon=$(locale abmon)- The variable mon is assigned the following line: Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec
for(n=split(mon, M, ";"); n; n--) - The split function returns the number of array elements with which we initiate the counter in the for loop

4
  • To make it clearer, I specially removed the FS variable from the BEGIN area.
    – nezabudka
    Oct 31 '20 at 19:14
  • Execute in terminal: locale abmon and LC_ALL=C locale abmon and if there is a difference, then change: -v mon="$(LC_ALL=C locale abmon)"
    – nezabudka
    Nov 1 '20 at 5:37
  • This works. Thank you! On my actual log file, this script delivered 13 results, while the script from @dave_thompson_085 delivered 18 unique IPs, looking at the two scripts do you see any reason for the difference. Note: all 13 were found by his script. Nov 1 '20 at 6:12
  • The syntax error was my own fault. Hence, I deleted the comment. Apologies. Nov 1 '20 at 6:13
1

This may be a little over the top but, noting that the csv is not time sequential then there may be a need to deal with this and, since you are learning gawk there may be a few things in here to stretch you...

awk -F'[|/:]' 'BEGIN{mth="   JanFebMarAprMayJunJulAugSepOctNovDec"}
        {mthn=int(index(mth,$2)/3)}
        {ipt=mktime($3" "mthn" "$1" "$4" "$5" "$6); ip[$7]++; print $7, ipt}' file | 
        sort | 
awk '{ix=(NR-1)%5; iy=(ix+1)%5; ip[ix]=$1;t[ix]=$2}
          NR>4&&(ip[ix]==ip[iy])&&((t[ix]-t[iy])<=15)&&(!found[$1]){print ip[ix]}'

walkthrough

Set the -Field separator give access to individual elements of the date field and set up a string of months

awk -F'[|/:]' 'BEGIN{mth="   JanFebMarAprMayJunJulAugSepOctNovDec"}

Get the month number as the index of the match in the month string / 3

        {mthn=int(index(mth,$2)/3);

Compose the date fields into a timespec for mktime and convert to a timestamp ipt and output the ip and the timestamp

            ipt=mktime($3" "mthn" "$1" "$4" "$5" "$6); print $7, ipt}' file |

Sort the output

        sort |

which sorts by ip and then by time. The output looks like this

000.111.026.111 1603949440
000.111.026.111 1603949444
000.111.026.111 1603949445
000.111.026.111 1603949447
000.111.026.111 1603949448
000.111.026.111 1603950114
060.121.125.144 1603950112
060.121.125.144 1603950112
060.121.125.144 1603950115
060.121.125.144 1603950117
060.121.125.144 1603950118
111.111.111.111 1603946502
220.171.008.221 1603949447
221.651.943.323 1603946509

Then feed it back into awk and load the fields into arrays containing the ip and timestamp where the index is calculated as (NR-1)%5 so that it rotates in the range 0-4 as we work through the file. iy is the 5th previous entry (ix+1)%5 wrapped in the same way

awk '{ix=(NR-1)%5; iy=(ix+1)%5; ip[ix]=$1;t[ix]=$2}

Then the fun part where if the record is the 5th or greater...

          NR>4

AND the ip in the current field is the same as that 5 fields before...

              &&(ip[ix]==ip[iy])

AND the time difference between those fields is <=15 seconds...

                &&((t[ix]-t[iy])<=15)

AND we haven't reported this ip before...

                  &&(!found[$1])

THEN increment our found counter so we dont report it again and report the ip this once

                    {found[$1]++; print $1}'
0
1

Instead of just looking at 1st and 5th occurrence of the second field, you might want to look at all occurrences five apart. A sliding window approach:

awk '
{
    n = c[$7] = ++c[$7] % 4
    m = index("..JanFebMarAprMayJunJulAugSepOctNovDec",$2)/3
    s = mktime($3 " " m " " $1 " " $4 " " $5 " " $6)
    if (s - t[$7,n] < 15 && !seen[$7]++) {
        print
    }
    t[$7,n] = s
}
' FS='[/:|]' output.csv
8
  • "you might want to look at all occurrences five apart" — Yes, that sounds like a good plan. Thank you for this, I will give it a go. Nov 1 '20 at 4:48
  • I ran the script. It worked, but only delivered 7 results on my log file, the script from @dave_thompson_085 delivered 18 results. Given that this is checking for all occurrences of 5 records, do you have any ideas as to why the script delivers so few results in comparison? I understand that it can be tricky to test without the csv file. If I knew a way to switch the IPs for dummies, I would send over the file, but alas I don't. Nov 1 '20 at 5:58
  • @BumblingBadger The first approach assumes that timestamps only ever increase for a given IP. Does the awk | sort | awk approach produce a different result?
    – rowboat
    Nov 1 '20 at 7:28
  • Of the sample file, Ed Morton's script and Dave Thompson's script both deliver 94 results, while your script delivers 50. Here is the sample file, should you want to take a look. As the method you propose is different, I will ask a new question and send you the link. Nov 4 '20 at 18:08
  • 1
    Correct date order is good. @BumblingBadger
    – rowboat
    Nov 9 '20 at 5:54
0

You can get a list of unique IPs using cut and sort and feed that into a while loop that runs most of your existing script:

#!/bin/bash

while read addr ; do
    # your scripting here
done < <(cut -d '|' -f 2 input_file | sort -u)

<() is a bash-ism, so this isn’t as portable.

8
  • Would recommend rewriting in Python or another scripting language if you plan to maintain and use this for any length of time. My argument being that a real script could do this in a single pass with better handling of time and sliding windows. Oct 31 '20 at 1:34
  • Appreciated! I will give it a shot. Also, I appreciate what you say about Python. It is something I want to look into soon. As far as the script, the current script looks for a specific IP address, will I be able to substitute that with this method? Do I just cut out the initial egrep? Any further details would be great if possible. That said, if you haven't the time I'll experiment and see what happens — sorry, I'm a bit green when it comes to scripting and loops. Oct 31 '20 at 1:59
  • Replace hard-coded IP in the grep with $addr. Oct 31 '20 at 2:01
  • Ah OK, was being slow. I understand now. Thanks Timothy!. Oct 31 '20 at 2:07
  • I ran the script and got a message: cut: the delimiter must be a single character Then, I changed '|' to "|" but now the script just hangs. Any thoughts? Nov 1 '20 at 2:07

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.