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

Shell Programming and Scripting
1

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

2

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

3

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

Regards

4

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

5

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

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

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

#!/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:

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

Here SQL query and Time taken will be on the same file only on different locations and there is a relation between SQL query line followed by Time taken line,i want to list SQL queries whose time > 2 sec from log file for performance tuning.