CODEWITHSUNDEEP
perlbashawksed
jaredwins
jaredwins

Reputation: 87

awk sed perl to merge rows based on keyword match

I've been banging my head head against the wall on this issue due to my limited awk/sed wizardry. I'm happy to use awk,sed,bash,perl, or whatever to accomplish this text manipulation.

I have the following output and would like to merge lines based on a sort of key match:

 Node: server1
 Active Server: SECONDARY
 Standby Server: PRIMARY
 Primary 192.168.1.1
 Secondary 192.168.1.2

 Node: server2
 Active Server: PRIMARY
 Standby Server: SECONDARY
 Primary 10.1.1.1
 Secondary 10.1.1.2

Desired output:

 Node: server1
 Active Server: Secondary 192.168.1.2
 Standby Server: Primary 192.168.1.1

 Node: server2
 Active Server: Primary 10.1.1.1
 Standby Server: Secondary 10.1.1.2

So I need the lines to merge based on the words "primary" and "secondary". My first thought was to change "Primary" to "PRIMARY" so it would be easier to match.

My eventual goal is to have this: 

 server1,Active,192.168.1.2,Standby,192.168.1.1
 server2,Active,10.1.1.1,Standy,10.1.1.2

(but I can figure this part out after help merging the rows)

Thanks for the help!

Upvotes: 3

Views: 250

Answers (9)

Borodin
Borodin

Reputation: 126722

This Perl solution seems to do what you ask. It simply pulls the values into a hash line by line, and dumps the hash contents when all the required values are present.

Update I've used any from List::Util in place of grep to make the code more legible. 

use strict;
use warnings;
use autodie;

use List::Util 'any';

my @names = qw/ node active standby primary secondary /;

open my $fh, '<', 'myfile.txt';

my %server;

while (my $line = <$fh>) {
  next unless my ($key, $val) = lc($line) =~ /(\w+).*\s+(\S+)/;

  %server = () if $key eq 'server';
  $server{$key} = $val;

  unless ( any { not exists $server{$_} } @names ) {
    printf "%s,Active,%s,Standby,%s\n", @server{'node', $server{active}, $server{standby}};
    %server = ();
  }
}

output

server1,Active,192.168.1.2,Standby,192.168.1.1
server2,Active,10.1.1.1,Standby,10.1.1.2

Upvotes: 2

glenn jackman
glenn jackman

Reputation: 246807

awk '
    $1 == "Active"  {active = tolower($NF); next} 
    $1 == "Standby" {standby = tolower($NF); next} 
    $1 == "Primary" {ip["primary"] = $0; next} 
    $1 == "Secondary" {
        ip["secondary"] = $0
        print "Active Server:",ip[active]
        print "Standby Server:",ip[standby]
        next
    }
    1
'

This assumes the "Secondary" line is at the end of a "block".

To achieve your next output:

awk -v OFS="," '
    $1 == "Node:"   {node = $NF}
    $1 == "Active"  {active = tolower($NF)} 
    $1 == "Standby" {standby = tolower($NF)} 
    $1 == "Primary" {ip["primary"] = $2} 
    $1 == "Secondary" {
        ip["secondary"] = $2; 
        print node, "Active",ip[active],"Standup",ip[standby]
    }
'

Responding to jhill's comment:

awk -v RS="" -v OFS=, '{
    node = active = standby = ""
    delete ip
    for (i=1; i<NF; i++) {
        if      ($i == "Node:")     {node=$(++i)}
        else if ($i == "Active")    {active = tolower( $(i+=2) )}
        else if ($i == "Standby")   {standby = tolower( $(i+=2) )}
        else if ($i == "Primary")   {ip["primary"] = $(++i)}
        else if ($i == "Secondary") {ip["secondary"] = $(++i)}
    }
    print node, "Active", ip[active], "Standup", ip[standby]
}'

Upvotes: 1

Jotne
Jotne

Reputation: 41456

You can use this awk

awk -v RS="" '{$5=tolower($5);sub(".",substr(toupper($5),1,1),$5);$8=tolower($8);sub(".",substr(toupper($8),1,1),$8);print $1,$2"\n"$3,$4,$5,$10"\n",$6,$7,$8,$12}' file
Node: server1
Active Server: Secondary 192.168.1.1
 Standby Server: Primary 192.168.1.2
Node: server2
Active Server: Primary 10.1.1.1
 Standby Server: Secondary 10.1.1.2

By sette setting RS to nothing, awk works with group of line.

Upvotes: 0

Miller
Miller

Reputation: 35198

Or using a one-liner for the intermediate desired solution (final solution to follow):

perl -00 -lpe '
     s/ Server: \K(\w+)(?=.*^(\1[^\n]*))/$2/ismg;
     s/\n[^:]+$//;
   ' file.txt

Outputs:

Node: server1
Active Server: Secondary 192.168.1.2
Standby Server: Primary 192.168.1.1

Node: server2
Active Server: Primary 10.1.1.1
Standby Server: Secondary 10.1.1.2

Explanation:

  • Switches:
    • -00: process input in paragraph mode (separated by double returns)
    • -l: enable line ending processing
    • -p: assume "while (<>) { ...; print; }" loop around program
    • -e: evaluate perl code
  • Code:
    • Replace all Server values with a matching line that begins with the same key
    • Remove the server list at the bottom.

To get the final solution you want, the following one liner will accomplish that goal.

There are some slight changes from the first solution like using -n instead of -p because we want to move from two newlines between records to one new line. However, the regex tools are the same:

perl -00 -ne'
    s/ Server: (\w+)(?=.*^\1\s+(\S+))/:$2/ismg;
    s/\n[^:]+$//;
    s/^Node: //;
    s/[\n:]/,/g;
    print "$_\n";
  ' file.txt

Outputs:

server1,Active,192.168.1.2,Standby,192.168.1.1
server2,Active,10.1.1.1,Standby,10.1.1.2

Upvotes: 1

Tiago Lopo
Tiago Lopo

Reputation: 7959

You can use tr to eliminate spaces, then sed to put then back in the right place and use perl to get the output you want:

Input file:

tiago@dell:/tmp$ cat file
 Node: server1
 Active Server: SECONDARY
 Standby Server: PRIMARY
 Primary 192.168.1.1
 Secondary 192.168.1.2

 Node: server2
 Active Server: PRIMARY
 Standby Server: SECONDARY
 Primary 10.1.1.1
 Secondary 10.1.1.2

Script:

tiago@dell:/tmp$ cat test.sh 
#! /bin/bash

tr -d '\n' < $1 | sed -r 's/(Node:)/\n\1/g' |\
     perl -lne '
        /^\s+$/ && next;
        /Node:\s+(\w+.*?)\s/ && {$server=$1};
        /Active Server:\s+(\w+.*?)\s/ && {$active=$1};
        /Standby Server:\s+(\w+.*?)\s/ && {$standby=$1};
        /Primary\s+(\w+.*?)\s/ && {$pri=$1};
        /Secondary\s+(\w+.*?)\s/ && {$sec=$1};

        if ( "$active" eq "PRIMARY" ){
            $out="$server,Active,$pri,Standby,$sec";
        }else{
            $out="$server,Active,$sec,Standby,$pri";          
        }
        print $out;
    '

Execution:

tiago@dell:/tmp$ bash test.sh  file 
server1,Active,192.168.1.2,Standby,192.168.1.1
server2,Active,10.1.1.1,Standby,192.168.1.2

Upvotes: 1

a5hk
a5hk

Reputation: 7834

awk ' s==0{print;s=1;next;}
      s==1{i=$0;s=2;next;}
      s==2{j=$0;s=3;next;}
      s==3{r1=$0;s=4;next;}
      s==4{r2=$0;
           sub(/SECONDARY/,r2,i);sub(/PRIMARY/,r1,j);
           sub(/SECONDARY/,r2,j);sub(/PRIMARY/,r1,i);
           s=5; print i;print j;next}
      s==5{s=0;print}' input.txt

Output:

 Node: server1
 Active Server:  Secondary 192.168.1.2
 Standby Server:  Primary 192.168.1.1

 Node: server2
 Active Server:  Primary 10.1.1.1
 Standby Server:  Secondary 10.1.1.2

Prints first line of the current input section, stores next four lines in variables, then makes replacements and then print the result. then reads and print the blank line and starts again for next section.

Upvotes: 0

mpapec
mpapec

Reputation: 50637

It is dense and very ugly multi-liner,

perl -00 -nE'
  s/ ^(\w+)\s+([\d.]+)\s* / $s{$1}=$2; ""/xmge;
  ($l=$_) =~ s! \s*\w+:\s*|\n !,!xg;
  $l =~ s|\U$_|$s{$_}| for keys %s;
  ($_=$l) =~ s/^,|,$//g;
  say
' file

output

server1,Active,192.168.1.2,Standby,192.168.1.1
server2,Active,10.1.1.1,Standby,10.1.1.2

Explanation

# -00 => instead of single line read lines into $_ until \n\n+
perl -00 -nE'
  # read and remove 'Primary|Secondary IP' into $s{Primary} = IP
  s/ ^(\w+)\s+([\d.]+)\s* / $s{$1}=$2; ""/xmge;

  # replace 'something:' or new line by ','
  ($l=$_) =~ s! \s*\w+:\s*|\n !,!xg;

  # replace SECONDARY|PRIMARY with actual IP address
  $l =~ s|\U$_|$s{$_}| for keys %s;

  # remove ',' at beginning and end of the string
  ($_=$l) =~ s/^,|,$//g;

  # print result
  say
' file

Upvotes: 1

ghoti
ghoti

Reputation: 46846

Here's an option in awk.

#!/usr/bin/awk -f

# Output processing goes in a function, as it's called from different places
function spew() {
  split(servers[d["active"]], active);
  split(servers[d["standby"]], standby);
  printf("%s,%s,%s,%s,%s\n",
     d["name"], active[1], active[2], standby[1], standby[2]);
}

# trim unnecessary (leading) whitespace
1 { $1=$1; }

# Store our references
$1=="Active" {
  d["active"]=tolower($3);
}
#
$1=="Standby" {
  d["standby"]=tolower($3);
}

# And store our data
/^ *[A-za-z]+ [0-9.]+$/ {
  servers[tolower($1)]=tolower($0);
}

# Then, if we hit a new record, process the last one.
$1=="Node:" && length(d["name"]) {
  spew();
}

# And if we've just process a record, clear our workspace.
$1=="Node:" {
  delete d;
  delete s;
  d["name"]=$2;
}

# Finally, process the last record.
END {
  spew();
}

An advantage of this over some of the other solutions is that it can handle names other than "primary" and "secondary". The idea is that if you have data like:

Node: serverN
Active Server: starfleet
Standby Server: babylon5
starfleet 172.16.0.1
babylon5 172.16.0.2

the Active/Standby lines will refer to a record by its index, rather than assuming "Primary" or "Secondary".

I've normalized everything to lower case for easier handling, but you can of course adjust tolower() to suit.

Upvotes: 0

Oesor
Oesor

Reputation: 6642

A bit more verbose:

use strict;
use warnings;
use feature qw/say/;

my $struct;
local $/ = 'Node: ';


for my $record (<DATA>) {
    next if $record =~ /^Node:/; # skip first
    my ($node, @values) = split /\n\s*/, $record;
    for my $line (@values) { 
        my ($intent, $actual, $ip);
        if ( ($intent, $actual) = $line =~ /(Active|Standby) Server: (.*)$/ ) {
            $struct->{$node}{lc($intent)} = lc($actual);
        }
        elsif ( ($actual, $ip) = $line  =~ /(Primary|Secondary) (.*)$/ ) {
            $struct->{$node}{lc($actual)} = $ip;
        }
    }
}


for my $node (sort keys %$struct) {
    printf "Node: %s\n", $node;
    printf "Active server: %s %s\n", ucfirst $struct->{$node}{active}, $struct->{$node}{$struct->{$node}{active}};
    printf "Standby server: %s %s\n", ucfirst $struct->{$node}{standby}, $struct->{$node}{$struct->{$node}{standby}};
    print "\n";
}

## Desired final output is simpler:
for my $node (sort keys %$struct) {
    say join ',', $node, 'Active', $struct->{$node}{$struct->{$node}{active}}, 'Standby', $struct->{$node}{$struct->{$node}{standby}};
}


__DATA__
Node: server1
 Active Server: SECONDARY
 Standby Server: PRIMARY
 Primary 192.168.1.1
 Secondary 192.168.1.2

 Node: server2
 Active Server: PRIMARY
 Standby Server: SECONDARY
 Primary 10.1.1.1
 Secondary 10.1.1.2

Upvotes: 0

Related Questions