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
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.
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
