Calculate query response time - awk in bash

query.log is the log file of the query daemon of my application. It prints the query debug lines in the following format:

$ cat query.log
Tue Oct 25 11:36:35 2008 (debug) : query received: Select ...... ;
Tue Oct 25 11:36:49 2008 (debug) : response prepared:
Tue Oct 25 11:36:50 2008 (debug) : query response sent: total records sent = 50
Tue Oct 25 11:39:04 2008 (debug) : query received: Select ..... ;
Tue Oct 25 11:40:04 2008 (debug) : response prepared:
Tue Oct 25 11:40:06 2008 (debug) : query response sent: total records sent = 23
....
....

Output required: For each query, I need to find the total time taken for the whole execution of the query, i.e the diff of "query response sent" and "query received" time.

The bash script using sed:

$ cat querytime.sh
#!/bin/sh

QFILE=query.log

while read logline
do
event=$(echo $logline | sed 's/.*(debug).*: \(.*\):.*/\1/')
case $event in
       "query received") ST=$(date +%s -d "${logline%(debug)*}")
               echo "$logline";;
       "query response sent") ET=$(date +%s -d "${logline%(debug)*}")
               echo "$logline"
               echo "Total time taken=$(($ET - $ST)) sec";;
       *) echo "$logline" ;;
esac
done < $QFILE

Executing the above script:
$ ./querytime.sh
Tue Oct 25 11:36:35 2008 (debug) : query received: Select ...... ;
Tue Oct 25 11:36:49 2008 (debug) : response prepared:
Tue Oct 25 11:36:50 2008 (debug) : query response sent: total records sent = 50
Total time taken=15 sec
Tue Oct 25 11:39:04 2008 (debug) : query received: Select ..... ;
Tue Oct 25 11:40:04 2008 (debug) : response prepared:
Tue Oct 25 11:40:06 2008 (debug) : query response sent: total records sent = 23
Total time taken=62 sec
....
....

Find column number based on field header - awk

One of my application generates a raw file where number of fields (columns) is more than 40. So to know the field position for a particular field was tough.

This is a sample file to show how we can find the column number of a particular column by using the column header.

$ cat rawfile.txt
#Fields date time loc my-det nsv-ps max-auto-s help-n min-new
Sat-Oct-25 02:00 IN o1asbi09 9076 1000 - 12
Sat-Oct-25 02:30 IN o1asbi09 4073 1000 - 10
Sat-Oct-25 03:00 IN o1asbi09 6186 1000 - 10
Sat-Oct-25 02:30 IN o1asbi09 7063 1000 - 12
...

S0 to find the column number of the field where the column header is "max-auto-s", the awk one liner would be:

$ awk '
END { if (!f++) print "pattern not found" }
/^#Fields/{ for(i=1;i<=NF;i++){
if ($i ~ "max-auto-s")
{print "[line="NR,"column="i-1"]", f++} }
}' rawfile.txt

Output:
[line=1 column=6] 0

Related post:

Print field based on header using awk

Add text in middle of file - awk bash

Input file:

$ cat details.txt
f:30:12:20
f:40:43:26
f:50:52:25
n:12:78:90
n:13:32:89
n:13:78:12
s:45:23:89
s:90:23:12

Output required: It was required to add a line "-----------", in the middle of the above file, so that the output should look like this,

f:30:12:20
f:40:43:26
f:50:52:25
n:12:78:90
----------
n:13:32:89
n:13:78:12
s:45:23:89
s:90:23:12

The bash script using awk to insert a line in the middle of a file:

$ cat midins.sh
#!/bin/sh
FILE=$1
lines=$(awk 'END {print NR}' $FILE)
mid=$((lines / 2))
awk -v pos="$mid" '{
if(NR<=pos)
print
}' $FILE
echo "----------"
awk -v pos="$mid" '{
if(NR>pos)
print
}' $FILE

Executing:
$ ./midins.sh details.txt
f:30:12:20
f:40:43:26
f:50:52:25
n:12:78:90
----------
n:13:32:89
n:13:78:12
s:45:23:89
s:90:23:12

Similar post:

Add/Change/Insert lines to a file using sed

Add column value based on condition - awk patterns

Input file release_3v2.txt contains the details of the tickets (CR) going in release 3.2. It contains the CR name, module name and "reported by".

$ cat release_3v2.txt
R92892|main_module|lneubuss
R92851|stiching|lneubuss
I92390|Integration|pshanes
R91234|mapper|lneubuss
I90876|gen|pshanes

Required: The ticket numbers starting with R is related to "regression test" and to be assigned to "lanish@jaduks.com", and integration related tickets (ticket numbers starting with I) is to be assigned to "minz@jaduks.com".
To be specific, we need to add the "Assigned to" column as the 4th column based on the ticket type(I or R).

Awk patterns to be used here:

pattern ? pattern : pattern

Meaning: If the first pattern is true then the pattern used for testing is the second pattern, other-wise it is the third. Only one of the second and third patterns is evaluated.

The awk solution:

$ awk '
BEGIN {FS=OFS="|"; print "Ticket|Module|LoggedBy|AssignedTo"}
($4=$1~/^R/?"lanish@jaduks.com":$1~/^I/?"minz@jaduks.com":"")||1
' release_3v2.txt

Output:

Ticket|Module|LoggedBy|AssignedTo
R92892|main_module|lneubuss|lanish@jaduks.com
R92851|stiching|lneubuss|lanish@jaduks.com
I92390|Integration|pshanes|minz@jaduks.com
R91234|mapper|lneubuss|lanish@jaduks.com
I90876|gen|pshanes|minz@jaduks.com

And if there is only two choices i.e. tickets starting with R to be assigned to "lanish@jaduks.com" and all others to "minz@jaduks.com"; the awk solution would be.

$ awk '
BEGIN {FS=OFS="|"; print "Ticket|Module|LoggedBy|AssignedTo"}
$4=$1~/^R/?"lanish@jaduks.com":"minz@jaduks.com"
' release_3v2.txt

Output:

Ticket|Module|LoggedBy|AssignedTo
R92892|main_module|lneubuss|lanish@jaduks.com
R92851|stiching|lneubuss|lanish@jaduks.com
I92390|Integration|pshanes|minz@jaduks.com
R91234|mapper|lneubuss|lanish@jaduks.com
I90876|gen|pshanes|minz@jaduks.com

system in awk - external command execution

Definition:
system(cmd-line) : Execute the command cmd-line, and return the exit status.

system() in awk returns the exit status of the command rather than its actual output. The command runs, writes its output to standard output, and its exit status ("0") is what gets returned and assigned to the variable.

Input file: My input file contains unix epoch time as the first field with geo_continent as the 2nd field.

$ cat g_details.txt
1219071600|AF
1219158000|AF
1220799600|AS
1220886000|AS
1220972400|EU
1221058800|OC

Required: Convert the unix epoch times (1st field) to human readable date time format.

I had one single line script (epochcnvrt) for converting the epoch time to human readable date time format.

$ cat epochcnvrt
date --date '1970-01-01 UTC '$1' seconds'

So this is how I can execute my script epochcnvrt on each line first field of the above file.

$ awk '{ system("sh epochcnvrt "$1)} {print $2}' FS="|" g_details.txt

The output:

Mon Aug 18 15:00:00 UTC 2008
AF
Tue Aug 19 15:00:00 UTC 2008
AF
Sun Sep 7 15:00:00 UTC 2008
AS
Mon Sep 8 15:00:00 UTC 2008
AS
Tue Sep 9 15:00:00 UTC 2008
EU
Wed Sep 10 15:00:00 UTC 2008
OC

Find min max of a column using awk - bash

I already discussed how to find min, max and average using awk in one of my earlier post titled Min,max and average using awk

Here is a simple one liner using awk to find the min and max of a column.

$ cat rank.txt
7.8|awk|S002
8|bash|S124
7.3|sed|S345
6.1|expect|S009
9.0|python|S008
4.1|tcl|S234

The awk one liners:

$ awk 'min=="" || $1 < min {min=$1} END{ print min}' FS="|" rank.txt
4.1

$ awk 'max=="" || $1 > max {max=$1} END{ print max}' FS="|" rank.txt
9.0

$ awk 'min=="" || $1 < min {min=$1; subject=$2}; END{ print min,subject}' FS="|" rank.txt
4.1 tcl

Generate xml from input file - bash script

Input file:

$ cat 21354.txt
1223850098 192.168.31.4
1223860353 172.22.22.12
1223860184 172.17.3.45

Requirement: We need to generate an XML from the above input file, which should look something like the below:

$ cat 21354.xml
<mck version="2">
       <ldb>
               <lock fromTime="1223850098" toTime="1223850398">
                        <lock ip="192.168.31.4" />
            <l_id="21354"/>
        </lock>
                       <lock fromTime="1223860353" toTime="1223860653">
                        <lock ip="172.22.22.12" />
            <l_id="21354"/>
        </lock>
                       <lock fromTime="1223860184" toTime="1223860484">
                        <lock ip="172.17.3.45" />
            <l_id="21354"/>
        </lock>
       </ldb>
</mck>

The bash script:

$ cat xmlgen.sh
#!/bin/sh
#Generate xml from input file in a predefined format
#

NOFILE=64
[ -z $1 ] &&  echo "xmlgen.sh <filename>" && exit $NOFILE

myfile=${1}
basefile=$(basename $myfile .txt)

#Construct the frame
header='<mck version="2">
  <ldb>'

fmt='    <lock fromTime="%d" toTime="%d">
            <lock ip="%s" />
            <l_id="%s"/>
        </lock>
  '

footer='</ldb>
</mck>'

#Printing
{
printf "%s\n" "$header"
  while read epoch ip
     do
        printf "$fmt" "$epoch" "$(( $epoch + 300 ))" "$ip" "$basefile"
     done < "$myfile"
printf "%s\n" "$footer"
} > $basefile.xml

echo "Output xml is $basefile.xml"

Executing:

$ ./xmlgen.sh 21354.txt
Output xml is 21354.xml

Start a new shell from vi editor - vim tips

Have you ever faced the situation when you edited a file in vim editor and the time you tried to save, you realized the file is read only for you, so what you do that time ?

a) you just exit without saving; change the permission of the file, then edit the file again in vim and then save

or

b) from another terminal you change the permission of the file, and do the save and exit

This is what I do in this situation:

There is an command in vi to create a new shell from vi , its called sh. So go to ex mode and type

:sh

sh looks up the user's default shell (e.g. the Bourne shell or the C shell) and starts a new shell. In that shell change the permission of the file, type exit, you will be in the same vi session; now save your file.

awk - update file based on another file

classvi.txt is the student info database file for class vi of "unstable international school".

$ cat classvi.txt
Student1
Name Anna K
ID VI45

Student2
Name Anil Pri
ID VI12

Student3
Name Xena Len
ID VI90

...
...

Recently HNU university introduced university serial ID for each and every student of "unstable international school". The mapping of the school ID number to University ID number is sent in a file classvi_UID.txt.

$ cat classvi_UID.txt
VI90 UID43434
VI13 UID21221
VI12 UID31322
VI45 UID26767
VI01 UID12323
...
...

Output required: We need to update classvi.txt with the corresponding University ID from classvi_UID.txt, so that the final output will be:

Student1
Name Anna K
ID VI45 (UID26767)

Student2
Name Anil Pri
ID VI12 (UID31322)

Student3
Name Xena Len
ID VI90 (UID43434)

...
...


The awk solution:

$ awk 'NR==FNR { UID[$1] = $2; next }
/^ID/ { print $0,"("UID[$2]")"; next }1' classvi_UID.txt classvi.txt

Related posts related to NR==FNR can be found here