2

So I am trying to track down individuals on our network surfing porn via https. The pattern is pretty easy to see, 15+ hits in a row for images without a referrer in the HTTP header on our Bro logs.

The relevant columns in Bro are $3 = host IP, $11 = referrer, $14 = file size, and $27 = mime type.

Thus, I am currently using...

awk -F "\t" '$11 ~ /^\-$/ && $14 > 100000 && $27 ~ /^image/'

What I'd like to do is know if there is a way, still within the single line command, to run a sub-command that tells awk to only print lines where the IP in $3 occurs >= 15 times.

My guess is that I'll have to create an awk program to do something like that. I'm hoping there's a whiz on here that can help me avoid that. I'm not opposed to using another regex command if it would work better (perl, grep, egrep, agrep, bro-cut).

Update: The best way to explain this would be via Excel terms. Does awk have something like the countif Excel function? =countif(C1,C:C)>15

Sample Log:

1443534069  CGAdXyZgN3wVwihi6   123.456.789.012 59713   93.184.216.98   80  1   GET 40.media.tumblr.com /1fbe50fff7a17f84acdc30b03d9b6335/tumblr_nvf1dfH8oz1tco00do1_500.jpg    -   Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.99 Safari/537.36    0   89522   200 OK  -   -   -   (empty) -   -   -   -   -   FIGAv51OT15ak4eDCl  image/jpeg
1443534069  CkST1DjXDkCBDYhYa   123.456.789.012 59712   93.184.216.98   80  1   GET 40.media.tumblr.com /e8f958e0dcd3eb419035a8d3271d07e8/tumblr_npr5drTCOO1qk489oo1_500.jpg    -   Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.99 Safari/537.36    0   83743   200 OK  -   -   -   (empty) -   -   -   -   -   FWRWZX2XgQQqfm9OMe  image/jpeg
1443534069  C8GvXwqAiR84PGGkk   123.456.789.012 59714   93.184.216.98   80  1   GET 40.media.tumblr.com /0b80deef543f6da28b48db0578fb3bd4/tumblr_n0chjkQICf1qeu577o1_500.jpg    -   Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.99 Safari/537.36    0   70530   200 OK  -   -   -   (empty) -   -   -   -   -   FOHdJ62uCU30UE9VYg  image/jpeg
1443534069  CMXgz73HlqL5Z0WVR7  123.456.789.012 59715   54.230.193.223  80  1   GET 36.media.tumblr.com /547822945f762adb310bb966c1f9c886/tumblr_nv3xgebHVH1sbsr1vo1_500.jpg    -   Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/45.0.2454.99 Safari/537.36    0   67589   200 OK  -   -   -   (empty) -   -   -   -   -   FmaN4d2eimhA2CpEmd  image/jpeg
  • when $3 reaches that 15-hit threshold, does the script have to print all previous hits for that IP, or just all subsequent ones? Do you need to print the entire matching line or just the IP and the URL (in which case, what field number is the URL)? – cas Oct 7 '15 at 1:44
  • Needs to print all hits for that IP in the entire log (fixed log, not expanding). And I need to print the entire line {print $0}. – user112802 Oct 7 '15 at 1:46
  • any chance of getting a few lines of the log file for a working sample? suitably anonymised, of course. – cas Oct 7 '15 at 1:59
  • llike any other programming language, awk won't know if the count of each individual IP address seen in field $3 is going to be >=15 until it has read at least enough of the file to count that many. The spreadsheet has the entire file in memory so can make those sort of calculations. – cas Oct 7 '15 at 2:01
  • @cas I added a sample. Links are NSFW – user112802 Oct 7 '15 at 2:15
2

This perl script stores each log line that matches the criteria (an "image/", > 100000 bytes, referrer = '-') in a Hash of Arrays keyed by the IP address. At the end of the script, it prints out every array line for each IP address that has > 14 entries.

It uses a lot of memory, but not as much memory as it would if it stored every input line.

You could condense it into a one-liner but you'd just be making it unreadable/un-debugable for no good reason.

#! /usr/bin/perl

use strict;

my %LOGLINES = ();

while (<>) {
    next unless (/\bimage\//);
    my @F=split("\t");
    next unless ($F[10] eq '-');
    next unless ($F[13] > 100000);

    push @{ $LOGLINES{$F[2]} }, $_;
};  

foreach my $key (sort keys %LOGLINES) {
   print @{ $LOGLINES{$key} } if (scalar @{ $LOGLINES{$key} } > 14);
}  

Note that perl arrays are zero-based, not 1-based. so field numbers are offset by -1 from what you specified.

Here's another version that doesn't use anywhere near as much memory because it only stores up to 15 lines for each IP address it sees, then it starts printing matching lines as it sees them. The disadvantage is that the output isn't sorted by IP address but that's easily solved by piping to sort -t $'\t' -k2.

#! /usr/bin/perl

use strict;

my %LOGLINES = ();
my %count = ();

while (<>) {
    next unless (/\bimage\//);
    my @F=split("\t");
    next unless ($F[10] eq '-');
    next unless ($F[13] > 12000);

    $count{ $F[2] }++;

    if ($count{ $F[2] } == 15) {
      print @{ $LOGLINES{$F[2]} };   # print all the log lines we've seen so far
      print $_;                      # print the current line
    } elsif ($count{ $F[2] } > 15) {
      print $_;                      # print the current line
    } else {
      push @{ $LOGLINES{$F[2]} }, $_; # store the log line for later use
    }
};
  • Perl is new to me, could you kindly break down what that's doing a little? For instance, what if /image/ appears as part of the URI string for the image file, but the file isn't actually an image (as defined by mime type in column 27)? – user112802 Oct 7 '15 at 3:09
  • i've used \b there so it's unlikely to happen. i did it that way to avoid split()-ing the line unless it was an image. An alternative way to do it is to delete that line so that we split every line regardless and have next unless ($F[26] =~ /^image\//); immediately after the split; – cas Oct 7 '15 at 3:14

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.