extracting fields from a file in Shell

Question

I have lots of data in a file like as below

 alert tcp any any -> any any (msg: "test1"; sid:16521; rev:1;created_at 2010_07_30, updated_at 2016_07_01;)
 alert tcp any any -> any any (msg: "test2"; nocase; sid :23476;distance:0; rev:1;created_at 2010_10_30, updated_at 2013_07_11;)
 alert tcp any any -> any any (msg: "test3"; sid:236487; file_data; content:"clsid"; nocase; distance:0; created_at 2008_08_03, updated_at 2016_05_01;

I want to extract sid,msg,created_at and updated_at from the file & output should looks like

test1 | 16521 | 2010_07_30 | 2016_07_01
test2 | 23476 | 2010_10_30 | 2013_07_11
test3 | 236487| 2008_08_03 | 2016_05_01

and the script which i used is

cat $file  | grep -v "^#" | grep "^alert" | sed 's/\"//g' | awk -F ';' '
{
        for(i=1;i<=NF;i++)
    {
            if (match($i,"sid:")>0)
        {
            split($i, array1, ":")
            Rule_sid=array1[2]
                    }
            if(match($i,"msg:")>0)
                    {
                        split($i, array, "(")
            split(array[2], array2, ":")
                        message=array2[2]
                    }
            if(match($i,/metadata:/)>0 )
        {
            split($i, array3,/created_at/)
            create_date=array3[2]
        }
            if(match($i,/metadata:/)>0 )
                    {
                        split($i, array4, ", updated_at ")
            update_date=array4[2]
                    }


           }
            print Rule_sid "|" message "|" create_date "|" update_date
    }' >> Rule_Files/$file

Answer 1

For starters, you can extract the fields with a regex using sed:

sed '/^alert/s/^.*msg[: ]*"\([^"]*\)".*sid[: ]*\([0-9][0-9]*\);.*created_at *\([^,]*\),.*updated_at *\([0-9_][0-9_]*\).*$/\1|\2|\3|\4/' $file

Which gives you output like this:

test1|16521|2010_07_30|2016_07_01
test2|23476|2010_10_30|2013_07_11
test3|236487|2008_08_03|2016_05_01

Now you want that nicely lined up into columns, you'll have to feed it to something else, perhaps awk:

sed '/^alert/s/^.*msg[: ]*"\([^"]*\)".*sid[: ]*\([0-9][0-9]*\);.*created_at *\([^,]*\),.*updated_at *\([0-9_][0-9_]*\).*$/\1|\2|\3|\4/' $file |
awk -F\|  'BEGIN { OFS="| " } {$2=sprintf("%6d",$2)}1'

Which gives you this:

test1|  16521| 2010_07_30| 2016_07_01
test2|  23476| 2010_10_30| 2013_07_11
test3| 236487| 2008_08_03| 2016_05_01

If you have to deal with arbitrarily-wide column values and still want the vertical alignment, then you'll have to write something that processes all of the lines first to find the widest value of each before printing anything out. That is an exercise I leave to the reader.

Answer 2

Using awk

Modify -v OFS=" | " and -v extract="msg,sid,created_at,updated_at" as per your interest, OFS is output field separator and variable extract holds list of fields (separated by comma) which need to be parsed, if any field not found it will give Null

Program assumes field value exists next to current field match, suppose field sid found when j=4, its value exists at j+1 that is at j=5.

Input

$ cat file
 alert tcp any any -> any any (msg: "test1"; sid:16521; rev:1;created_at 2010_07_30, updated_at 2016_07_01;)
 alert tcp any any -> any any (msg: "test2"; nocase; sid :23476;distance:0; rev:1;created_at 2010_10_30, updated_at 2013_07_11;)
 alert tcp any any -> any any (msg: "test3"; sid:236487; file_data; content:"clsid"; nocase; distance:0; created_at 2008_08_03, updated_at 2016_05_01;)

Output

$ awk -v OFS=" | " -v extract="msg,sid,created_at,updated_at" '
 BEGIN{
    split(extract,Fields,/,/)
 }
 {
    gsub(/[:";,()]/," "); 
    s=""; 
    for(i=1; i in Fields; i++)
    { 
        f = 1
        for(j=1; j<=NF; j++)
        { 
            if($j==Fields[i])
            {
              f = 0 
              s = ( s ? s OFS :"") $(j+1) 
              break 
            } 
        }
        if(f){
            s = (s ? s OFS:"") "Null"
        }     
     } 
        print s 
 }' file

test1 | 16521 | 2010_07_30 | 2016_07_01
test2 | 23476 | 2010_10_30 | 2013_07_11
test3 | 236487 | 2008_08_03 | 2016_05_01