[awk] combine and convert time from log files

dear all,

an awk newbie need your help.... i have log files with this format:

mylog1a.log:

"08/10/2012","5:05 PM"
"Hostname","Device Address","Count"
"","10.10.10.18","10234"

mylog2a.log:

"08/11/2012","5:05 PM"
"Hostname","Device Address","Count"
"","10.10.10.18","12543"

mylog1b.log:

"08/10/2012","5:05 PM"
"Hostname","Device Address","Count"
"myserver","10.10.10.19","13467"

mylog2b.log:

"08/11/2012","5:05 PM"
"Hostname","Device Address","Count"
"myserver","10.10.10.19","9362"

and i want to combine them into one log file with this result:

start time,end time,hostname,device address,count
08/10/2012 17:05,08/11/2012 17:05,,10.10.10.18,12543
08/10/2012 17:05,08/11/2012 17:05,myserver,10.10.10.19,13467

explanation for combine log:

• start time is taken from date first log
• end time is taken from date on second log
• time need to be convert to 24 hours format
• all quote characters should be removed

And from which log file does one take the values of Hostname, Device Address and Count? first or second? Your output seems to be confusing in this aspect.

hi elixir,

actually, they are daily logs. you can assume that mylog1a.log is day 1 log and mylog2a.log is day 2 log for device 10.10.10.18. and it same for mylog1b.log and mylog2b for device 10.10.10.19

mylog1a.log is related with mylog2a.log, so hostname is taken from these files. but in this case hostname is not specified on log files so it should be empty ",,"

How do you get the values which have been highlighted in red and in bold face:

start time,end time,hostname,device address,count
08/10/2012 17:05,08/11/2012 17:05,,10.10.10.18,12543
08/10/2012 17:05,08/11/2012 17:05,myserver,10.10.10.19,13467

they are just taken from log, no calculation needed.

the idea is pulling data from daily logs and put them into a log file that will be send to other application.

You are not getting what I am asking:

last line of mylog1a.log --> "","10.10.10.18","10234"
last line of mylog2a.log --> "","10.10.10.18","12543"

How does your output, corresponding to this log, have 12543 and not 10234?

last line of mylog1b.log --> "myserver","10.10.10.19","13467"
last line of mylog2b.log --> "myserver","10.10.10.19","9362"

How does your output, corresponding to this log, have 13467 and not 9362?

oh, ok...
when we execute the script on day 08/11 the result is:

08/10/2012 17:05,08/11/2012 17:05,,10.10.10.18,10234
08/10/2012 17:05,08/11/2012 17:05,myserver,10.10.10.19,13467

and when we execute the script on day 08/12

08/11/2012 17:05,08/12/2012 17:05,,10.10.10.18,12543
08/11/2012 17:05,08/12/2012 17:05,myserver,10.10.10.19,9362

where "08/12/2012 17:05" value is retrive from a log that created on 08/12/2012 17:05

but if it is difficult to solve, maybe we can remove/eliminate the end_time field so the result:

when we execute the script on day 08/11 the result is:

08/10/2012 17:05,,10.10.10.18,10234
08/10/2012 17:05,myserver,10.10.10.19,13467

and when we execute the script on day 08/12

08/11/2012 17:05,,10.10.10.18,12543
08/11/2012 17:05,myserver,10.10.10.19,9362

i hope this makes my question clear...

one log file will contain one hostname/ip

thank you raj_saini20 for helping me to explain my issue

just found a sed command to remove quote (") characters from a file

sed 's/\x22//g' filename.txt

• thanks to pspad for provide me a hint for this character

all,

i have create a script

#!/bin/sh
 DATE="`date +%d`_`date +%m`_`date +%y`_`date +%H`_`date +%M`_`date +%S`.log"
 ls -lrt *.log | awk '{f=$NF};END{print f}' > latestfile.tmp
 awk 'BEGIN { RS = "" ; FS = "\n" } ; { print $2 ",changeme," $4 }' *.csv > result.tmp
 latesta=$(/bin/cat latestfile.tmp)
 awk 'BEGIN { RS = "" ; FS = "\n"} ; { print $2 }' $latesta  > mytime.tmp
 latestb=$(/bin/cat mytime.tmp)
 sed -e "s/changeme/$latestb/g" result.tmp > final.tmp
 mv final.tmp $DATE

but it give me an error for sed command:

sed: -e expression #1, char 17: unknown option to `s'

UPDATE: i just found out that it because i "changeme" with "08/10/2012,4:11 PM"

so what is the proper way to change "changeme" with "08/10/2012,4:11 PM" ?

thank you

The variable latestb must be containing "/" characters. This is confusing sed as you have used "/" as the delimiters for s, pattern, replacement and flags. Try:

sed -e "s:changeme:$latestb:g" result.tmp > final.tmp

oh... i see...

thank you

now my homework is how to modify time format from 12 Hours to 24 Hours

from : 08/10/2012,4:11 PM
to 08/10/2012,16:11

:wall:

Try

awk 'BEGIN {FS="[/,: ]"}; $6=="PM" {$4=$4+12; $6=""} {print $1"/"$2"/"$3","$4":"$5}'

There may be better ways to produce the output format...

What about 12 AM/PM?

I was thinking about that; but requestor wants to convert PM times to 24hr times - I'm not aware of 12 PM being actually used, AFAIK it will be 0 AM next day. 12 AM is no problem; it will stay as is. The AM suffix will implicitly go away.

12 PM --> 12 noon.

Which would mean: just remove the "PM" if 12.

awk 'BEGIN {FS="[/,: ]"}{$4=($6=="PM"?($4==12?$4:$4+12):($4==12?0:$4)); $6=""} {print $1"/"$2"/"$3","$4":"$5}'

for 24Hrs conversion

i just back from long holiday...

thank you for all your assistant, guys...