AWK search a file from list in another file and format output

user_6223146 0
Viewed 228

I think this question has been answered before but I haven't been able to find one that addresses this specific need.

I am running into difficulties when using AWK to search a ~150mb text file of Ethernet packets for occurrences of a string from a list in csv (or text) file. One of the issues seems to be the leading spaces in the data file as well as the information after the colon in the data file.

I want to search the data file for the term "Epoch" in $1. That will be in every frame, then search for any of the terms in the list file that are found before the next occurrence of "Epoch" in $1 and if they are found print ($3/86400+25569)(to format it to work with Excel time format) from the "Epoch" line and then any of the occurrences from the list file separated by a comma(,).

The list file looks like this

bill_more_data:
and_more_data:
pay_hour:
age_years:
favorite_adventure_type:

The data file looks like this.

No.     Time           Source                Destination
      1 0.000000       xxx.xx.xxx.x           xxx.xx.xxx.x

Frame 1: 52 bytes on wire (100 bits), 22 bytes captured (757 bits)
    Arrival Time: Jun 28, 2021 04:17:23.747890000 Pacific Daylight Time
    [Time shift for this packet: 0.000000000 seconds]
    Epoch Time: 1624879043.747890000 seconds
    [Time delta from previous captured frame: 0.000000000 seconds]
    Frame Number: 1
 Ethernet II, Src: xxxxxxxxxxxxxx (xxx:xxx:xx:xx:xx:xx:x), Dst: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
    Destination: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
        Address: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
        .... .... .... .... .... .... = LG bit: Locally administered address (this is NOT the factory default)
        .... .... .... .... .... .... = IG bit: Individual address (unicast)
Internet Protocol Version 4, Src: xxx.xx.xxx.x, Dst: 000.00.00.00
    Flags: 0x0, Don't fragment
        0... .... = Reserved bit: Not set
Info Header
    message_id: 000x00
    message_length: 2
bill_some_data
    bill_that_data: 0
    bill_more_data: 1
    and_more_data: 0

0000  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   ................
0010  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00   ................

No.     Time           Source                Destination        
      2 0.000275       xxx.xx.xxx.x          xxx.xx.xxx.x          

Frame 2: 60 bytes on wire (454 bits), 55 bytes captured (454 bits)

    Arrival Time: Jun 28, 2021 04:17:23.748165000 Pacific Daylight Time
    [Time shift for this packet: 0.000000000 seconds]
    Epoch Time: 1624879043.748165000 seconds
    [Time delta from previous captured frame: 0.000275000 seconds]
    Frame Number: 2
   Ethernet II, Src: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x), Dst: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
    Destination: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
        Address: xxx:xxx:xx:xx:xx:xx:x (xxx:xxx:xx:xx:xx:xx:x)
        .... ..1. .... .... .... .... = LG bit: Locally administered address (this is NOT the factory default)
        .... ...0 .... .... .... .... = IG bit: Individual address (unicast)
Internet Protocol Version 4, Src: 172.28.1.72, Dst: 172.28.1.30
    0100 .... = Version: 4
    .... 0101 = Header Length: 20 bytes (5)
    Flags: 0x00
        0... .... = Reserved bit: Not set
        .0.. .... = Don't fragment: Not set
        ..0. .... = More fragments: Not set
    Fragment Offset: 0
    [Stream index: 1]
Info Header
    message_id: 0x00000
    message_length: 5
TED_name
    pay_hour: 3.25
    vacation_days: 0
    age_years: 22
    time_in_role: 0.1
    favorite_adventure_type: excellent

I have tried the following but I get no output. If I change the list file to include the info after the colon then it will at least print those lines but that doesn't do me much good here.

awk 'NR==FNR{a[$1]++;next}a[$1]' list.txt data.txt

Desired output

44375.4704137487,bill_more_data,1,bill_more_data,1
1624879043.748165000,pay_hour,3.25,age_years,22,favorite_adventure_type,excellent

or better with the epoch time formatted to work in Excel

44375.4704137487,bill_more_data,1,bill_more_data,1
44375.4704137518,pay_hour,3.25,age_years,22,favorite_adventure_type,excellent

I have tried several other methods with limited success. I am looking for a graceful way to do this that will also be able to handle future changes to the formatting in the way that these values are recorded. One of the other ways I tried was to run several AWK commands to first reverse the file, then search for the list items and print them, then search for the next occurrence or "Epoch" and print $3, then reverse the file again, then another AWK to printf any lines that aren't "Epoch". That did seem to work okay if I entered the list items directly in the command itself but I couldn't get it to work if I was trying to read from another file.

Thanks in advance for any help on this. This is my first dive into using AWK so please forgive me if I am asking a stupid question.

awk
3 Answers
$ cat tst.awk
BEGIN { OFS="," }
NR==FNR {
    tags[$0]
    next
}
$1 == "Epoch" {
    prt()
    time = $3
}
$1 in tags {
    tag2val[$1] = $2
}
END {
    prt()
}

function prt(   out) {
    if ( time != "" ) {
        out = sprintf("%0.10f",(time / 86400) + 25569)
        for (tag in tag2val) {
            val = tag2val[tag]
            sub(/:/,"",tag)
            out = out OFS tag OFS val
        }
        print out
    }
    delete tag2val
}


$ awk -f tst.awk list file
44375.4704137487,and_more_data,0,bill_more_data,1
44375.4704137519,favorite_adventure_type,excellent,age_years,22,pay_hour,3.25
user_17450010

awk 'NR==FNR{a[$1]++;next}a[$1]' list.txt data.txt

This part is right:

NR==FNR{a[$1]++;next}

that means at the end of list.txt the array a holds the strings in that file. But for the rest, you have to loop over that array to see what matches. Something like:

/Epoch:/ {
    if( epoch ) { printf "\n" }
    epoch = 1
    printf "%s" $2
}
epoch != 1 { next }

for( term in a ) {
    if( $0 ~ term ) { printf ... }
}

END { if( epoch ) { print "\n" } }

That prints a new line after every epoch: before every one except the 1st one, and at the end if any were seen.

user_4516010

Sorry for the late reply. @Ed Morton, thank you for the help on this. The script is working great!

It just needed a slight modification to filter out the blank time stamp line with an if statement and add { sub(/\r$/,"") } for the carriage return issue.

{ sub(/\r$/,"") }
BEGIN { OFS=","}
NR==FNR {
    tags[$0]
    next
}
$1 == "Epoch" {
    prt()
    time = $3
}
$1 in tags {
    tag2val[$1] = $2
}
END {
    prt()
}

function prt(   out) {
    if ( time != "" ) {
        out = sprintf("%0.10f",(time / 86400) + 25569)
        for (tag in tag2val) {
            val = tag2val[tag]
            sub(/:/,"",tag)
            out = out OFS tag OFS val
            i = i + 1
       }
    if  (i !=x )
    {
        print out
    }
x = i
    }
    delete tag2val
}
user_62231460
Related