concatenate and display 2 lines as 1 with a condition for 2 line ?

vithala · #1 (**permalink**) 07-10-2008

I have 2 pattern of lines (SQL query and Time taken)in a log i need to capture all SQL queries with time taken >20 sec and need to display as one line.

2 lines from log:

2007-10-23 11:39:17,061 DEBUG [server.startup : 1] [com.fourcs.clm.fw.pem.CLMPEMDBService] - SQL Query : SELECT A.GROUP_CD , C.FN_CD FROM UP_GROUP A , PRD_GROUP_TO_FN B , PRD_FN C WHERE A.GROUP_ID = B.GROUP_ID AND B.FN_ID = C.FN_ID ORDER BY A.GROUP_CD

2007-10-23 11:39:17,061 DEBUG [server.startup : 1] [com.fourcs.clm.fw.pem.CLMPEMDBService] - Time Taken : 640

vithala · #2 (**permalink**) 07-10-2008

i have tried to display results Time taken > 20000 by using a command but i want this results to be concatenated with above line(SQL Query line) for Time taken > 20000

cd /logs/WS/apps/logs

grep -i 'Time Taken :' /logs/WS/apps/logs/SQLPerformance_x1prdapp7*Node01_C*S*.log > /export/home/jftl784/script/Latest_SQLTIME_TMP.txt
awk '$12 > 20000 {print $0}' < /export/home/jftl784/script/Latest_SQLTIME_TMP.txt > /export/home/jftl784/script/Latest_SQLTIME_TMP_2.txt

grep -v 'Non-deferrable Alarm :' < /export/home/jftl784/script/Latest_SQLTIME_TMP_2.txt > /export/home/jftl784/script/Latest_SQLTIME_MSGS_$1.txt

Franklin52 · #3 (**permalink**) 07-10-2008

Post a portion of your log file and the desired output.

Regards

vithala · #4 (**permalink**) 07-10-2008

Input :
2007-10-23 11:39:17,061 DEBUG [server.startup : 1] [com.fourcs.clm.fw.pem.CLMPEMDBService] - SQL Query : SELECT A.GROUP_CD , C.FN_CD FROM UP_GROUP A , PRD_GROUP_TO_FN B , PRD_FN C WHERE A.GROUP_ID = B.GROUP_ID AND B.FN_ID = C.FN_ID ORDER BY A.GROUP_CD

2007-10-23 11:39:17,061 DEBUG [server.startup : 1] [com.fourcs.clm.fw.pem.CLMPEMDBService] - Time Taken : 640

output:
SELECT A.GROUP_CD , C.FN_CD FROM UP_GROUP A , PRD_GROUP_TO_FN B , PRD_FN C WHERE A.GROUP_ID = B.GROUP_ID AND B.FN_ID = C.FN_ID ORDER BY A.GROUP_CD
: Time Taken : 640

vithala · #5 (**permalink**) 07-10-2008

Log file contents :
2007-10-23 11:42:30,759 DEBUG [WebContainer : 0] [com.fourcs.clm.fw.pem.CLMPEMDBService] - SQL Query : Select UIG.UIG_ID , UIG.UIG_REF AS UIG_CD , GRP_ORDER , UIG.UIG_NM , GLANG.GROUP_LABEL , GLANG.GROUP_DESC , ROLE_CD , UIL_ID from (select GRP.UIG_ID , GRP.UIG_REF , GRP.UIG_NM , GRP_ORDER , ROLE_CD , UIL_ID from UI_INTFXLOCATION x , UI_GROUP GRP where x.UII_ID=13 and x.UIG_ID=GRP.UIG_ID and x.IS_ACTIVE='Y')
UIG LEFT JOIN UI_GROUP_LANG GLANG on UIG.UIG_ID=GLANG.UIG_ID and GLANG.LANG_CD='eng' order by GRP_ORDER
2007-10-23 11:42:30,759 DEBUG [WebContainer : 0] [com.fourcs.clm.fw.pem.CLMPEMDBService] - Time Taken : 354
2007-10-23 11:42:31,573 DEBUG [WebContainer : 0] [com.fourcs.clm.fw.pem.CLMPEMDBService] - PEM Event : SQLExcessTimeEvent for SQL Refer : Not Available
2007-10-23 11:42:31,573 DEBUG [WebContainer : 0] [com.fourcs.clm.fw.pem.CLMPEMDBService] - SQL Query : select UIE.UIE_ID , UIE.UIE_REF AS UIE_CD , UIE.UIE_NM , UIE_URL , UIE_LANG.ELEMENT_LABEL , UIE_LANG.ELEMENT_DESC , UIG_ID , ELMNT_ORDER , IS_MANDATORY from (select elm.UIE_ID , elm.UIE_REF , elm.UIE_NM , UIE_URL , grpx.UIG_ID , grpx.ELMNT_ORDER , IS_MANDATORY from UI_INTFXLOCATION intf , UI_GROUP grp , UI_GRPXELMNT
grpx , UI_ELEMENT elm where intf.UII_ID=13 and intf.UIG_ID=grp.UIG_ID and grp.UIG_ID=grpx.UIG_ID and grpx.UIE_ID=elm.UIE_ID and grpx.IS_ACTIVE='Y')UIE left join UI_ELEMENT_LANG UIE_LANG on UIE.UIE_ID=UIE_LANG.UIE_ID and UIE_LANG.LANG_CD='eng' order by UIG_ID , ELMNT_ORDER
2007-10-23 11:42:31,573 DEBUG [WebContainer : 0] [com.fourcs.clm.fw.pem.CLMPEMDBService] - Time Taken : 708

shamrock · #6 (**permalink**) 07-10-2008

Code:

awk '
{
    FS="- "
    OFS=" : "
    split($NF,a,OFS)
    if (tolower(a[2]) ~ "select")
       sav = a[2]
    if (a[2]+0 > 20)
       print sav, a[1], a[2]
}' file

Diabolist · #7 (**permalink**) 07-10-2008

Hmm, I read your other post. This may not be what you want... but o-well.

Code:

#!/bin/ksh

INPUT=zzz.input
QUERYFILE=zzz.query
TIMEFILE=zzz.time
OUTPUT=zzz.out

grep "SQL Query :" $INPUT > $QUERYFILE

grep "Time Taken :" $INPUT |awk '{if ($NF > 20000) print $0; else print ""}' > $TIMEFILE

paste $QUERYFILE $TIMEFILE > $OUTPUT

The awk statement might be better as this:

Code:

awk '{if ($NF > 20000) print "ALERT - Time taken:",$NF; else print "OK - Time taken:",$NF}'

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
searching and storing unknown number of lines based on the string with a condition	swamymns	Shell Programming and Scripting	7	05-13-2008 01:02 AM
How to enter if-then condition on command prompt/line ?	mpc8250	Shell Programming and Scripting	3	02-10-2008 12:27 PM
display all lines	shary	Shell Programming and Scripting	3	02-17-2007 04:09 PM
display lines after a particular line number	rajashekar.y	UNIX for Dummies Questions & Answers	6	01-03-2007 03:53 AM
how to concatenate two command in one line and get the display in one screen	vasikaran	UNIX for Dummies Questions & Answers	9	07-01-2005 05:41 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: