Convert listner.log to csv format with comma seperated

Shell Programming and Scripting
1

Hi All,

I am new to shell scripting i am trying to convert the listner.log to csv which can be inturn converted to excel for easy reading.

i used this command

awk '/SID=/ && /HOST=/ && /PORT=/ && /USER=/ {

i=match($0,"SID="); i=i+RLENGTH; h0=substr($0,i);
i=match(h0,")"); sid=substr(h0,1,(i-1));

i=match($0,"HOST="); i=i+RLENGTH; h0=substr($0,i);
i=match(h0,")"); host=substr(h0,1,(i-1));

i=match($0,"PORT="); i=i+RLENGTH; h0=substr($0,i);
i=match(h0,")"); port=substr(h0,1,(i-1));

i=match($0,"USER="); i=i+RLENGTH; h0=substr($0,i);
i=match(h0,")"); user=substr(h0,1,(i-1));

print "SID="sid", HOST="host, PORT="port, User="user;
}' /u01/app/oracle/local/network/log/sample.log| \
sort -u

to display the output like

SID,HOST,PROT,USER
TEST,TEST1,1601,oracle

however all the lines are not getting displayed, if there is better script to get the desired results is much appreciated.

Thanksm

2 
v="DATE,SID,USER,HOST"

awk -v v="$v" '
BEGIN {
   c=split(v, a, ",");
}

{
   f=0;
   for (j=2; j<=c; j++) {
      w=a[j] "=";
      if (match($0, w)) f++;
   }
   if (f==c-1) {
      $1=$1 " " $2;
      for (j=2; j<=c; j++) {
         l=$0;
         w=a[j] "=";
         sub(".*" w, "", l);
         i=match(l, w); i=i+RLENGTH; h0=substr(l,i);
         i=match(h0, ")"); $j=substr(h0,1,(i-1));
      }
      NF=c;
      print $0;
   }
}
' OFS="," sample.log | sort -u | awk -v v="$v" 'NR==1 {print v} ; 1'
3

Thanks for the reply, however i am still not getting the correct results

this is my sample listner.log

12-DEC-2017 15:20:57 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=62965)) * establish * TEST * 0
12-DEC-2017 15:21:11 * (CONNECT_DATA=(CID=(PROGRAM=SQL DevelTEST)(HOST=__jdbc__)(USER=Naresh))(SID=TEST)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.33.9.20)(PORT=50314)) * establish * TEST * 0
12-DEC-2017 15:21:39 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=perl@test)(HOST=test)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63031)) * establish * TEST * 0
12-DEC-2017 15:21:51 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=perl@test)(HOST=test)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63049)) * establish * TEST * 0
12-DEC-2017 15:22:23 * (CONNECT_DATA=(CID=(PROGRAM=SQL DevelTEST)(HOST=__jdbc__)(USER=Naresh))(SID=TEST)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.33.9.20)(PORT=50323)) * establish * TEST * 0
12-DEC-2017 15:25:21 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=perl@test)(HOST=test)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63393)) * establish * TEST * 0
12-DEC-2017 15:25:50 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63440)) * establish * TEST * 0
12-DEC-2017 15:26:49 * (CONNECT_DATA=(SID=TEST)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63537)) * establish * TEST * 12518

which i wanted to convert this to a csv file with comma seperated.

DATE,SID,HOST,USER,ADDRESS

Thank you

---------- Post updated at 05:43 PM ---------- Previous update was at 05:31 PM ----------

this is the output i am getting

SID,HOST,PORT,USER
TEST,__jdbc__,50314,Naresh,DevelTEST)(HOST=__jdbc__)(USER=Naresh))(SID=TEST)),*,(ADDRESS=(PROTOCOL=tcp)(HOST=10.33.9.20)(PORT=50314)),*,establish,*,TEST,*,0
TEST,__jdbc__,50323,Naresh,DevelTEST)(HOST=__jdbc__)(USER=Naresh))(SID=TEST)),*,(ADDRESS=(PROTOCOL=tcp)(HOST=10.33.9.20)(PORT=50323)),*,establish,*,TEST,*,0
TEST,__jdbc__,62965,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=62965)),*,establish,*,TEST,*,0
TEST,__jdbc__,63440,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63440)),*,establish,*,TEST,*,0
TEST,__jdbc__,63537,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63537)),*,establish,*,TEST,*,12518
TEST,test,63031,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63031)),*,establish,*,TEST,*,0
TEST,test,63049,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63049)),*,establish,*,TEST,*,0
TEST,test,63393,oracle,*,(ADDRESS=(PROTOCOL=tcp)(HOST=000.00.000.00)(PORT=63393)),*,establish,*,TEST,*,0
4

See edited code. Not sure what should be included in the ADDRESS field. Between parentheses or first value after equal sign? Also, there are 2 HOST= matches in string. Which should be used in output?

5

Basically from the listner.log i wanted all the data like below:

DATE,SID,USER,HOST
12-DEC-2017 15:21:11,TEST,oracle,000.00.00.0
6

See update.

7

its working and the output is as expected, Appreciate your help.

Thanks a lot..:-):slight_smile: