Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Parsing log with sed problem

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Parsing log with sed problem
# 1  
Old 01-05-2011
Parsing log with sed problem
I have huge logs that have sql statements in them and I need to parse those statements out.

Log sample:
Code:
OUT1: #STMT# [SELECT X FROM P WHERE A=1] from: [SELECT X FROM P WHERE A=?]
[E1 Fine]: 2010.12.20 14:01:16.357--DatabaseSessionImpl(17489534)--Connection(11145468)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT GETDATE()
[E2 Finer]: 2010.12.20 14:01:16.357--UnitOfWork(12167196)--Thread(Thread[AWT-EventQueue-0,6,main])--begin unit of work commit
[E3 Finer]: 2010.12.20 14:01:16.357--DatabaseSessionImpl(17489534)--Connection(11145468)--Thread(Thread[AWT-EventQueue-0,6,main])--begin transaction
[E4 Fine]: 2010.12.20 14:01:16.357--DatabaseSessionImpl(17489534)--Connection(11145468)--Thread(Thread[AWT-EventQueue-0,6,main])--UPDATE S SET CNT=CNT+1 WHERE SNAME='ABC'
[E5 Fine]: 2010.12.20 14:01:16.373--DatabaseSessionImpl(17489534)--Connection(11145468)--Thread(Thread[AWT-EventQueue-0,6,main])--SELECT CNT FROM S WHERE SNAME='ABC'
[E6 Finer]: 2010.12.20 14:01:16.373--DatabaseSessionImpl(17489534)--Connection(11145468)--Thread(Thread[AWT-EventQueue-0,6,main])--commit transaction

I'd like to get
Code:
SELECT X FROM P WHERE A=1
SELECT GETDATE()
UPDATE S SET CNT=CNT+1 WHERE SNAME='ABC'
SELECT CNT FROM S WHERE SNAME='ABC'

my script:
Code:
#!/bin/ksh
sed -n  -e '/\-\-SELECT/  s/^.*\-\-//p' \
        -e '/\-\-UPDATE/  s/^.*\-\-//p' \
        -e '/\-\-DELETE/  s/^.*\-\-//p' \
        -e '/#STMT#/    s/.*# \[//;s/\].*//p'   $1

produces this
Code:
SELECT X FROM P WHERE A=1
SELECT GETDATE()
[E2 Finer
[E3 Finer
UPDATE S SET CNT=CNT+1 WHERE SNAME='ABC'
SELECT CNT FROM S WHERE SNAME='ABC'
[E6 Finer

What would be suggestions of sed gurus to get clean sql statements. Please note: sed is much preferred in my environment.
# 2  
Old 01-05-2011
Well, rather than focusing on the positive, variable SQL you want to keep, you might focus on the more static part just before that you want to remove. There are a lot of key words, also english words, that might appear.

You lost the from clause derived table there. Is that good?
# 3  
Old 01-05-2011
How about this:

Code:
#!/bin/sh
sed -n \
    -e '/\-\-SELECT/  s/^.*\-\-//p' \         
    -e '/\-\-UPDATE/  s/^.*\-\-//p' \        
    -e '/\-\-DELETE/  s/^.*\-\-//p' \        
    -e '/#STMT#/      s/\].*//'     \
    -e '/#STMT#/      s/.*# \[//p' $1

-- Edit --
or, I think this is the sort of thing DGPickett was suggesting:
Code:
#!/bin/sh
sed -n \
    -e '/\-\-begin/d' \
    -e '/\-\-commit/d' \
    -e 's/^.*])\-\-//p' \
    -e '/#STMT#/s/^.*# \[\([^]]*\)].*/\1/p'

Last edited by Chubler_XL; 01-05-2011 at 11:04 PM..
These 2 Users Gave Thanks to Chubler_XL For This Post:
DerekAsirvadem migurus
# 4  
Old 01-06-2011
Or awk:
Code:
awk -F'--|[][]' '/#STMT#/{print $2;next}$NF!~/[[:lower:]]/{print $NF}' infile

Code:
sed -n 's/.*#STMT# \[\([^]]*\).*/\1/p;/--[^[:lower:]]*$/s/.*--//p' infile

Last edited by Scrutinizer; 01-06-2011 at 06:34 AM..
# 5  
Old 01-06-2011
Quote:
Originally Posted by Chubler_XL
How about this:
... snip ...
Code:
    -e '/#STMT#/s/^.*# \[\([^]]*\)].*/\1/p'

works great, but I have difficulty understanding the regular expression in that. The way I read it is
/^.*# \[ - match from the beginning to the last # sign followed by space and open square bracket
\([^]]*\)].*/ - match [^] and mark it as 1st -- what is [^] here?
the last part ].* says match from the closing square braket to the end and it is being excluded. So, my question is what is this \([^]]*\)
I'd appreciate if you will find time to explain it.
Thanks.
# 6  
Old 01-06-2011
Quote:
Originally Posted by migurus
works great, but I have difficulty understanding the regular expression in that. The way I read it is
/^.*# \[ - match from the beginning to the last # sign followed by space and open square bracket
\([^]]*\)].*/ - match [^] and mark it as 1st -- what is [^] here?
the last part ].* says match from the closing square braket to the end and it is being excluded. So, my question is what is this \([^]]*\)
I'd appreciate if you will find time to explain it.
Thanks.
Yep your spot on with your analysis.

To explain \([^]]*\) as you say the \( and \) are for grouping and store the matching string in \1 so we are just left with [^]]* which is: zero or more non closing-square-bracket characters (The ^ symbol means any character not listed, for example [^a-zA-z] would match any single non-alpha character).

The net effect is: On any line that contains "#STMT#" replace everything up to the last "# [" with nothing, and then everything from the first "]" with nothing.
Last edited by Chubler_XL; 01-06-2011 at 05:59 PM..
These 2 Users Gave Thanks to Chubler_XL For This Post:
DerekAsirvadem migurus
# 7  
Old 01-06-2011
Code:
awk -F "--" 'NR==1{split($0,a,"[][]");print a[2];next} {print $NF}' infile |grep -v "[a-z]"
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parsing via sed issue

sorry I messed up the last post with too many mistakes and corrections so I closed it and opening a new one which should be clear to everyone .my apologies to the admins. I am using sun solaris and Linux , what I want is SED to print any string (or output it to a file preferably) that does... (2 Replies)
Discussion started by: boncuk
2 Replies

2. UNIX for Dummies Questions & Answers

sed or Grep Parsing

I would like to parse two strings from lines in a file only when both strings appear on the same line. For example, if I have the following line: string1 string2 string3 string4 string5 string6 string7 string8 string9 I would like the output to be: string2: string7 Can someone give me... (5 Replies)
Discussion started by: ARBlue79
5 Replies

3. Shell Programming and Scripting

[SED] Parsing to get a single value

Hello guys, I guess you are fed up with sed command and parse questions, but after a while researching the forum, I could not get an answer to my doubt. I know it must be easy done with sed command, but unfortunately, I never get right syntax of this command OK, this is what I have in my... (3 Replies)
Discussion started by: manolain
3 Replies

4. Shell Programming and Scripting

Sed special parsing

What is the shortest & right way to remove the string "" with a sed statement ? echo 'whateverwhatever' | sed ........ ? :) (2 Replies)
Discussion started by: ctsgnb
2 Replies

5. Shell Programming and Scripting

sed (parsing value)

All, Can somebody provide me with some sed expertise on how to parse the following line. 27-MAR-2011 10:28:01 * (CONNECT_DATA=(SID=dmart)(CID=(PROGRAM=sqlplus)(HOST=mtasnprod1)(USER=mtasnord))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.197.7.47)(PORT=54881)) * establish * dmart * 0 I would like... (3 Replies)
Discussion started by: BeefStu
3 Replies

6. Shell Programming and Scripting

Parsing cron with sed

Hello I want to convert my cron list into a csv Can you please help me with sed ? eg: Convert #06,21,36,51 * * 1,2 * (. ~/.profile ; timex /some/path/script -30 -15) >> /some/path/logfile2 2>&1 * * * * * (. ~/.profile ; timex /some/path/script2) > /some/path/logfile2 To:... (1 Reply)
Discussion started by: drbiloukos
1 Replies

7. Shell Programming and Scripting

Parsing with awk or sed

I want to delete corrupt records from a file through awk or sed. Can anyone help me with this Thanks Striker Change subject to a descriptive one, ty. (1 Reply)
Discussion started by: Rahul_us
1 Replies

8. Shell Programming and Scripting

Another parsing line awk or sed problem

Hi, After looking on different forums, I'm still in trouble to parse a parameters line received in KSH. $* is equal to "/AAA:111 /BBB:222 /CCC:333 /DDD:444" I would like to parse it and be able to access anyone from his name in my KSH after. like echo myArray => display 111 ... (1 Reply)
Discussion started by: RickTrader
1 Replies

9. Shell Programming and Scripting

Sed parsing error

I'm having a problem with a sed script. A programmer needs to change columns 942,943,944 to blank spaces only where it has the number 999 in every line. I didn't have a copy of the data file to test with originally so made my own up with a bunch of x's and put 999 in columns 5-7. The sed... (1 Reply)
Discussion started by: gravy26
1 Replies

10. Shell Programming and Scripting

awk sed parsing

hi , i would like to parse some file with the fallowing data : data data data "unwanted data" data data "unwanted data" data data data data #unwanted data. what i want it to have any coments between "" and after # to be erased using awk or/and sed. has anyone an idea? thanks. (3 Replies)
Discussion started by: Darsh
3 Replies
Login or Register to Ask a Question