Convert listner.log to csv format with comma seperated


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert listner.log to csv format with comma seperated
# 1  
Old 01-03-2018
Convert listner.log to csv format with comma seperated

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

Code:
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

Code:
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

Last edited by RudiC; 01-04-2018 at 06:09 AM..
# 2  
Old 01-03-2018
Code:
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'


Last edited by rdrtx1; 01-03-2018 at 06:12 PM..
# 3  
Old 01-03-2018
Thanks for the reply, however i am still not getting the correct results

this is my sample listner.log

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

Code:
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

Code:
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


Last edited by RudiC; 01-04-2018 at 06:10 AM..
# 4  
Old 01-03-2018
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?

Last edited by rdrtx1; 01-03-2018 at 06:12 PM..
# 5  
Old 01-03-2018
Basically from the listner.log i wanted all the data like below:

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


Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 01-04-2018 at 06:11 AM.. Reason: Added CODE tags.
# 6  
Old 01-03-2018
See update.
# 7  
Old 01-03-2018
its working and the output is as expected, Appreciate your help.

Thanks a lot..:-)Smilie


Moderator's Comments:
Mod Comment Please - in ALL your posts - use CODE tags as required by forum rules!
I did this for you in all above posts.

Last edited by RudiC; 01-04-2018 at 06:12 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Convert comma seperated file to line seperated.

Hi, I have data like this. 1,2,3,4 Output required: 1 2 3 4 I am trying to use tr function but getting error. Help is appreciated. (6 Replies)
Discussion started by: pinnacle
6 Replies

2. UNIX for Advanced & Expert Users

Urgent! need help! how to convert this file into comma delimited format

Hi experts, I need urget help! I have the a text file with this format: Types of fruits Name of fruits 1,1 Farm_no,1 apple,1 pineapple,1 grapes,1 orange,1 banana,1 2,2--->this is the record seperator Farm_no,2 apple,1 pineapple,1 grapes,3 orange,2 banana,1 3,3--->this is the... (2 Replies)
Discussion started by: natalie23
2 Replies

3. Shell Programming and Scripting

how to convert this file into comma delimited format

Hi experts, I need urget help! I have the a text file with this format: Types of fruits Name of fruits 1,1 Farm_no,1 apple,1 pineapple,1 grapes,1 orange,1 banana,1 2,2--->this is the record seperator Farm_no,2 apple,1 pineapple,1 grapes,3 orange,2 banana,1 3,3--->this is the... (1 Reply)
Discussion started by: natalie23
1 Replies

4. Shell Programming and Scripting

Removing blank lines from comma seperated and space seperated file.

Hi, I want to remove empty/blank lines from comma seperated and space seperated files Thanks all for help (11 Replies)
Discussion started by: pinnacle
11 Replies

5. Shell Programming and Scripting

Convert comma text file to Column in html format

I am trying to generate a report with below file : File1 : EQADM,edrtere9-phys,8122caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere11-phys,8227caef0,gpatmon,/bin/ksh,nuten Erick EQADM,edrtere3-phys,822caef0,gpatmon,/bin/ksh,nuten Erick can you help me convert it to html and add... (9 Replies)
Discussion started by: sriram003
9 Replies

6. UNIX for Dummies Questions & Answers

converting a tabular format data to comma seperated data in KSH

Hi, Could anyone help me in changing a tabular format output to comma seperated file pls in K-sh. Its very urgent. E.g : username empid ------------------------ sri 123 to username,empid sri,123 Thanks, Hema:confused: (2 Replies)
Discussion started by: Hemamalini
2 Replies

7. Shell Programming and Scripting

how to convert the result of the select query to comma seperated data - urgent pls

how to convert the result of the select query to comma seperated data and put in a .csv file using korn shell. Pls help me as its very urgent. Thanks, Hema. (1 Reply)
Discussion started by: Hemamalini
1 Replies

8. Shell Programming and Scripting

Handling .CSV( Comma seperated value) in awk

Hi Guys, I am trying to reading in comma seperated values in awk. I can set the delimiter to be a comma, but the tricky part is that commas that appear within quotes are not to be considered as delimiters. Could someone please help. Regards, Laud (1 Reply)
Discussion started by: Laud12345
1 Replies

9. UNIX for Advanced & Expert Users

How to load comma seperated values file (*.csv) into Oracle table

Hi all I need to input values in a .csv file into my Oracle table running in Unix, I wonder what would be the command to do so... The values are recorded in an excel file and I tried using a formatted text file to do so but failed because one of the field is simply too large to fit in the... (4 Replies)
Discussion started by: handynas
4 Replies

10. UNIX for Dummies Questions & Answers

How to load comma seperated values file (*.csv) into Oracle table

Hi all I need to input values in a .csv file into my Oracle table running in Unix, I wonder what would be the command to do so... The values are recorded in an excel file and I tried using a formatted text file to do so but failed because one of the field is simply too large to fit in the... (5 Replies)
Discussion started by: handynas
5 Replies
Login or Register to Ask a Question