How to process these DHCP logs to be human readable?

Question

I have this input:

Sep 23 13:43 192.168.6.200
Sep 23 13:44 192.168.6.166
Sep 23 13:45 192.168.6.200
Sep 23 13:46 192.168.6.166
Sep 23 13:47 192.168.6.200
Sep 23 13:48 192.168.6.166
Sep 23 13:49 192.168.6.176
Sep 23 13:49 192.168.6.200
Sep 23 13:50 192.168.6.166
Sep 23 13:51 192.168.6.176
Sep 23 13:51 192.168.6.200
Sep 23 13:52 192.168.6.166
Sep 23 13:54 192.168.6.166
Sep 23 13:54 192.168.6.176
Sep 23 13:56 192.168.6.176
Sep 23 13:57 192.168.6.166

I need this output:

Sep 23 13:43 192.168.6.200
Sep 23 13:51 192.168.6.200

Sep 23 13:44 192.168.6.166
Sep 23 13:57 192.168.6.166

Sep 23 13:49 192.168.6.176
Sep 23 13:56 192.168.6.176

So I have DHCP logs from an OpenWrt router that I generate with (I configured the dhcp server to dhcp lease to "5m"):

logread | fgrep DHCPACK | awk '{print $1" "$2" "$3" "$8}' | sed 's/:[0-9][0-9] / /g' | sort -u

But I need the mentioned input to be the mentioned output, how? are there any perl masters? :\ :D

Answer 1

Quick hack:

for ip in $(cut -d" " -f 4 < INPUT_FILE.txt | sort -u); do 
   grep "$ip" INPUT_FILE.txt | head -n1
   grep "$ip" INPUT_FILE.txt | tail -n1
   echo
done

However, this is not very fast, because it loops through the log file 3 times. And the output is not in the same order has your example; I'm not sure whether that is important to you.

Answer 2

Note that awk is (mostly) a superset of fgrep and sed, so you shouldn't need to call all three.

logread | awk '
  /DHCPACK/ {
    sub(/:..$/,"",$3)
    t = $1 " " $2 " " $3
    if (!($8 in first)) first[$8] = t
    last[$8] = t
  }
  END {
    for (i in first) {
      print first[i], i
      print last[i], i
      print ""
    }
  }'

Though they will not appear in any particular order. If you want them to appear in the order that the IP address first appears in the log, you could change it to:

logread | awk '
  /DHCPACK/ {
    sub(/:..$/,"",$3)
    t = $1 " " $2 " " $3
    if (!($8 in first)) {
      first[$8] = t
      ip[n++] = $8
    }
    last[$8] = t
  }
  END {
    for (i = 0; i < n; i++) {
      print first[ip[i]], i
      print last[ip[i]], i
      print ""
    }
  }'

Answer 3

perl version, with comments.

#! /usr/bin/perl 

use strict;

use Date::Parse;
use Date::Format;

my $format = '%b %d %R';

my %IP=();

# read the input file, convert date/time to a time_t timestamp,
# and store it into a hash of arrays (HoA) 'man perldsc' for details
while(<>) {
    chomp;
    my($month, $day, $time, $ip) = split ;
    my $time_t = str2time("$month $day $time");
    push @{ $IP{$ip} }, $time_t;
};

# NOTE: hashes are inherently unsorted. replace "keys %IP" below
# with an IP sort function if you need the IPs sorted - e.g.
# http://www.perlmonks.org/?node_id=37889

# print the first and last time stamp seen, converting
# back to the same time format as the input.
foreach my $key (keys %IP) {
    # sort the array held in $IP{$key} just in case the input
    # lines are out of order
    sort @{ $IP{$key} } ;

    my $first = $IP{$key}[0];
    my $last = $IP{$key}[scalar @{ $IP{$key} }-1];

    # print the first and last time seen
    # note: if an IP was seen only once, first and last will be the same.
    # easy enough to add an if or unless here if you want to exclude 
    # IPs where first == last.
    print time2str($format,$first) . " $key\n";
    print time2str($format,$last) . " $key\n\n";
}

if you want to pipe the original log into this rather than the log after being piped into grep, awk, and sed, then make the following changes.

1. immediately after the while(<>) {, add next unless /DHCPACK/;

2. change the line with split to:

   my ($month,$day,$year,undef,undef,undef,undef,$IP) = split;
   

Answer 4

Assuming Craig is right and that your input is chronologically ordered, this idiomatic awk script can be used:

parse.awk

!start[$4]  { start[$4] = $1" "$2" "$3 } 
            { end[$4]   = $1" "$2" "$3 } 
END { 
  for (ip in start) 
    print start[ip], ip, "\n" end[ip], ip, "\n"
}

Run like this:

logread | awk -f parse.awk

Output:

Sep 23 13:43 192.168.6.200 
Sep 23 13:51 192.168.6.200 

Sep 23 13:44 192.168.6.166 
Sep 23 13:57 192.168.6.166 

Sep 23 13:49 192.168.6.176 
Sep 23 13:56 192.168.6.176