2

I have a text file which looks like this:

2014-11-24 12:59:42.169 101.0.0.0 source
2014-11-24 12:59:40.375 104.156.80.0 destination
2014-11-24 12:59:36.729 104.219.48.0 destination
2014-11-24 12:59:40.377 104.37.160.0 source
2014-11-24 12:58:58.902 107.188.128.0 both
2014-11-24 12:59:06.456 107.188.128.0 source
2014-11-24 12:59:06.840 107.192.0.0 both
2014-11-24 12:59:42.043 107.192.0.0 destination
2014-11-24 12:58:58.904 107.192.0.0 source
2014-11-24 12:59:55.488 111.0.0.0 both
2014-11-24 12:59:30.007 111.0.0.0 destination
2014-11-24 12:59:33.209 108.175.32.0 destination
2014-11-24 12:59:06.841 108.175.32.0 source
  • For IP 107.188.128.0 which is tagged as both and source, I just want to tag it as both.
  • For IP 107.192.0.0 which is tagged as both, destination and source, I just want to tag it as both.
  • For IP 111.0.0.0 which is tagged as both and destination, I just want to tag it as both.
  • For IP 107.192.0.0 which is tagged as destination and source, I just want to tag it as both.

My desired output should be like this:

2014-11-24 12:59:42.169 101.0.0.0 source
2014-11-24 12:59:40.375 104.156.80.0 destination
2014-11-24 12:59:36.729 104.219.48.0 destination
2014-11-24 12:59:40.377 104.37.160.0 source
2014-11-24 12:59:06.456 107.188.128.0 both
2014-11-24 12:59:42.043 107.192.0.0 both
2014-11-24 12:59:55.488 111.0.0.0 both
2014-11-24 12:59:33.209 108.175.32.0 both

in which the latest date and time matching the IP are output.

This is what I have tried:

awk '{print $3}' input.txt | sort -u | while read line

do 
grep $line input.txt | head -1 
done

But, it doesn't work with IP 108.175.32.0.

And this solution:

  sed '
      N
      s/\([0-9.]\)\s\S\+\n.*\1\s\S\+$/\1 both/
      t
      P
      D
      ' input.txt

But that works only with 108.175.32.0.

Is it possible to get the desired output in one shot using awk or sed? I am terribly stuck at this point.

  • 1
    Please specify which date field should be output. – agc Jun 7 '16 at 21:45
  • @agc the latest date and time. – Swatesh Pakhare Jun 7 '16 at 21:57
1

The question is similar like here with a little modification:

| sed '
    :1
    N    #add next line
    s/\([0-9.]\+\)\s\S\+\n.*\s\1\s\S\+$/\1 both/
    t1   #go to point 1 if exchange took place
    P    #print first line from two
    D    #remove first line, return to start
    '
1

Does this do what you want:

 awk 'BEGIN{ip="nothing" 
    time=""
    type=""
 }
 {
    # if the currently processed ip is not the same as the line 
    # being processed then we need to print the data.
    if (ip != $3)
    {
       # if ip == nothing then this is the first line do not print.
       # otherwise we are at a line with a new ip and we should print
       # the data saved from previous lines.
       if(ip != "nothing")
       { 
          print time, ip, type
       }
    # Remove the time update line since we are now doing it outside the
    # if statement so it always updates the time. This will make the 
    # outputted line print the last time stamp for each IP.
    #time=$1" "$2
    ip=$3
    type=$4
    }
    else if (type != $4)
    {
       type="both"
    }
    # no matter what update the time stamp value so that the latest
    # time stamp is kept for any given ip. Putting it after the if
    # that handles when a new ip is found, makes sure that it does not
    # override the value printed for the old ip line.
    time=$1" "$2
 }
 END{
    # Once we reach the end of the input, we still have 
    # the last set of values to print.
    print time, ip, type
 }'

It will read the file and if there are two consecutive lines with the same ip and different type (des, src, both) it will change the type to both otherwise if a new ip is found in the data, it will print the type it had..

  • It works perfectly fine.But, how can I make it select the latest time and date ? – Swatesh Pakhare Jun 7 '16 at 22:15
  • Make it update that time variable at every line. I will update the solution for you. – Rob Jun 8 '16 at 14:39
  • It doesn't select latest date and time. For ip 107.192.0.0, time stamp should be 2014-11-24 12:59:42.043. But, it selects, 2014-11-24 12:58:58.904 How can we do this? – Swatesh Pakhare Jun 8 '16 at 15:28
  • The original implementation would have picked the first time stamp that appears in the input, the second will pick the time stamp from the last line with that ip. It looks like the input has the time stamps for each ip with the most recent at the top so you want the original implementation unless that order is not guaranteed. Are you saying that the order of the input lines is arbitrary? – Rob Jun 8 '16 at 16:52
  • In the first case, it was picking latest time stamp but not for all IPs. Yes, it is arbitrary. Although, I am sorting first 3 fields as cat some_input | sort -r -k1 -k2 -k3 | sort -uk3 > input.txt . input.txt is a file which is being used in this post. – Swatesh Pakhare Jun 8 '16 at 17:14
1

Given input file foo.txt:

  1. sort the first three fields numerically,
  2. use datamash to do the real work of combining IP tags,
  3. cut a redundant field,
  4. then use sed to replace any combined tags with "both".

    sort -r -k1n -k2n -k3n foo.txt | \
      datamash -W -f -s -g3 collapse 4 | \
      cut --complement -f4 | \
      sed 's/\t[sdb].*,.*$/\tboth/g'
    

Output:

2014-11-24  12:59:42.169    101.0.0.0       source
2014-11-24  12:59:40.375    104.156.80.0    destination
2014-11-24  12:59:36.729    104.219.48.0    destination
2014-11-24  12:59:40.377    104.37.160.0    source
2014-11-24  12:59:06.456    107.188.128.0   both
2014-11-24  12:59:42.043    107.192.0.0     both
2014-11-24  12:59:33.209    108.175.32.0    both
2014-11-24  12:59:55.488    111.0.0.0       both
0

If you fix the typo destinatiion in the input file (or modify the script to use destinati?ion so that it copes with the mis-spelling) then the output of my perl script answer for your last question can be piped into uniq -f2 to get your desired output.

The -f2 option tells uniq to skip the first two fields when comparing lines for uniqueness.

e.g. with the @lines = map ... line in the perl script changed to:

@lines = map { $_ =~ s/(source|destinati?ion)$/both/oi; $_} @lines;

The output looks like this:

$ ./swatesh.pl < swatesh2.txt | uniq -f2
2014-11-24 12:59:42.169 101.0.0.0 source
2014-11-24 12:59:40.375 104.156.80.0 destinatiion
2014-11-24 12:59:36.729 104.219.48.0 destinatiion
2014-11-24 12:59:40.377 104.37.160.0 source
2014-11-24 12:58:58.902 107.188.128.0 both
2014-11-24 12:59:06.840 107.192.0.0 both
2014-11-24 12:59:55.488 111.0.0.0 both
2014-11-24 12:59:33.209 108.175.32.0 both

If you want the latest date and time output, then first sort the input in reverse order by IP address (field 3) and then date and time (fields 1 & 2) before piping it into my perl script (swatesh.pl) and uniq -f2. Optionally sort the final output so that it is in ascending date+time order. e.g.

$ sort -r -k3,3 -k1,2 swatesh2.txt | ./swatesh.pl | uniq -f2 | sort     
2014-11-24 12:59:06.456 107.188.128.0 both
2014-11-24 12:59:33.209 108.175.32.0 both
2014-11-24 12:59:36.729 104.219.48.0 destinatiion
2014-11-24 12:59:40.375 104.156.80.0 destinatiion
2014-11-24 12:59:40.377 104.37.160.0 source
2014-11-24 12:59:42.043 107.192.0.0 both
2014-11-24 12:59:42.169 101.0.0.0 source
2014-11-24 12:59:55.488 111.0.0.0 both
  • when I run your code, my output is same as input text. Why is that ? – Swatesh Pakhare Jun 8 '16 at 15:52
  • are you running it with the input exactly as in your question, or are you changing it in some way (e.g. with sort) first? – cas Jun 9 '16 at 0:09
  • Exactly in the same way given in the question. Thanks @cas – Swatesh Pakhare Jun 9 '16 at 0:19
  • and are you piping it through uniq -f2 after my script? if not, i think you'll find the output isn't the same as the input, it will have some lines with 'source' or 'destination' changed to 'both'. btw, i changed the answer a bit...your input data wasn't ordered as i thought it was so i used sort instead of tac – cas Jun 9 '16 at 0:29
  • Yes. I am piping it through uniq -f2 – Swatesh Pakhare Jun 9 '16 at 0:30
0

I modified the code given in OP:

awk '{print $3}' input.txt | sort -u | while read line
do 
    echo -n `grep $line input.txt | \
      sort -r | head -1 | \
      grep -oe "[^a-z]*"` ' ' # print latest time stamp
    if [[ $(grep -c $line input.txt) -ge 2 ]];  then 
        echo  'both'
    else
        echo `grep $line input.txt | grep -oe "[a-z]*"`
    fi
done

Output:

2014-11-24 12:59:42.169 101.0.0.0  source
2014-11-24 12:59:40.375 104.156.80.0  destination
2014-11-24 12:59:36.729 104.219.48.0  destination
2014-11-24 12:59:40.377 104.37.160.0  source
2014-11-24 12:59:06.456 107.188.128.0  both
2014-11-24 12:59:42.043 107.192.0.0  both
2014-11-24 12:59:33.209 108.175.32.0  both
2014-11-24 12:59:55.488 111.0.0.0  both
0

I made the script below. It sorts the result by date.
Call it fix.sh, invoke bash fix.sh <LOGFILENAME>

function fix {
    while read IP TYPE HOUR DATE; do
        if [ ! -z "$PREVIOUS_IP" ]; then
            if [ $IP == $PREVIOUS_IP ]; then
                TYPE=both
                [[ $DATE < $PREVIOUS_DATE ]] && DATE="$PREVIOUS_DATE"
                EQUAL_IPS=true
            else
                echo $PREVIOUS_LINE
                EQUAL_IPS=false
            fi
        fi
        PREVIOUS_DATE="$DATE"
        PREVIOUS_IP=$IP;
        PREVIOUS_LINE="$IP $TYPE $HOUR $DATE";
    done
    [ "$EQUAL_IPS" == true ] && echo $PREVIOUS_LINE
}

LOGFILE=$1

cat $LOGFILE | awk '{print $3 " " $4 " " $1 " " $2;}' | \
  sort | fix | awk '{print $3 " " $4 " " $1 " " $2;}' | sort

Its output is:

2014-11-24 12:59:06.456 107.188.128.0 both
2014-11-24 12:59:33.209 108.175.32.0 both
2014-11-24 12:59:36.729 104.219.48.0 destination
2014-11-24 12:59:40.375 104.156.80.0 destination
2014-11-24 12:59:40.377 104.37.160.0 source
2014-11-24 12:59:42.043 107.192.0.0 both
2014-11-24 12:59:42.169 101.0.0.0 source
2014-11-24 12:59:55.488 111.0.0.0 both
  • The tags are correct. But, the time stamp are not as required. I want to print latest time stamp. By latest I mean new or fresh. For ip 107.192.0.0, time stamp should be 2014-11-24 12:59:42.043. But, it selects, 2014-11-24 12:58:58.904. How can I do this? – Swatesh Pakhare Jun 8 '16 at 16:20
  • @Swatesh, now it should be working as you want. The script now will preserve the latest date for lines with the same ip address. – Bruno Negrão Zica Jun 10 '16 at 5:23

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.