count the unique ip addresses for each five minute interval?
 

i was tasked to review our maillogs to determine if any unique ip address has exceeded 30 connections in a five minute interval.

can you guys/gals suggest the best way to tackle this?

here is a sample of my log file:

Please elaborate. Does the 30-connection threshold apply to any five-minute interval (such as 22:31:17-22:36:16) or only to crisp five-minute blocks (such as 22:30:00-22:34:59 and 22:35:00-22:39:59)? This is not a nit-picking detail; the complexity and processing time for the former case could be considerably more.

Daniel B. Martin

you can cron this every minute:

that's a good question.

for this purpose, it's going to be a fixed five minute period, i.e. 12:00:00-12:04:59.

With this InFile ...
... this code ...
... produced this OutFile ...
Daniel B. Martin

that's awesome daniel - thank you.

would it be too much for you to explain that code? it will help me learn the hows and whys you did that.

Starting with this InFile ...
... we do a tr which replaces blanks and bracket symbols with colons to simplify subsequent parsing.
This is the intermediate result ...
Now we do an awk which copies useful portions of each line, loses the colons, and "plugs in" a colon between hour number and minute number for cosmetic value. Most important: $4=5*int($4/5) rounds the minute value down to 0 or 5. It does this by doing an integer divide by 5 and a multiply by 5.
This is the intermediate result ...
Now we do a sort and a uniq with count. The sort is required to enable the uniq to work. The count field identifies the most-frequent appearances of distinct ip addresses.
This is the intermediate result ...Now we do a sort on the left-most field (that's the count of occurrences) in descending order.
This is the intermediate result ...Now we do an awk to keep only those lines with a frequency count above a chosen threshold. I chose 3 for this example but you would use another value (such as 30) with real-world data.
This is the finished product...Daniel B. Martin

thank you so much daniel. you have no idea how much this helps me understand text manipulation.

Here is a technique which may help you develop and debug this kind of code. Use tee to materialize the intermediate results in a series of work files. Then inspect each of the work files to see if you got the expected intermediate results.
When the code works as intended, remove all the tees and delete the work files.

Daniel B. Martin

Here is a slightly cleaner version:
You run it like so (after making it executable):
This simply outputs the counts and the ips, so if you want the time portion as well you may need to play a little further, but it gives you something to think about :)

I took my previous solution and hammered it into a one-liner.

This code ...
... produced this OutFile ...
Daniel B. Martin

One thing to be cautious of Daniel is that using 'j in a' by default has no sort order so your current output would be deemed lucky

The problem statement didn't require the output to be sorted. It didn't even require the number of observations to be printed. It asked only for a list of distinct ip addresses which were "heavy hitters."

Daniel B. Martin