Tranform Log to Row Base Text

Shell Programming and Scripting
1

Greeting Everyone,
Anyone has tested to transform a log file seemingly as following

  Text     : SELECT DISTINCT Block_Code FROM   DSSBDW.CC_PL_TRX
  -------------------------------------------
  Start Time: 04/18/2017 20:08:20.840551
  Stop Time:  04/18/2017 20:08:20.846921
  Elapsed Execution Time:  0.006370 seconds
  Number of Agents created: 1
  User CPU: 0.001788 seconds
  System CPU: 0.000168 seconds

  Text     : Select * from DSSBDW.RI  where UNQ_ID_SRC_STM = '2114999''
  -------------------------------------------
  Start Time: 04/18/2017 14:47:50.065624
  Stop Time:  04/18/2017 14:47:50.065662
  Elapsed Execution Time:  0.000038 seconds
  Number of Agents created: 1
  User CPU: 0.000020 seconds
  System CPU: 0.000001 seconds

Expected Output as following

Text                                   ElapsedTime  UserCPU   SystemCPU
----                                    ----------       -------   ---------
SELECT DISTINCT Block_Code FROM   DSSBDW.CC_PL_TRX     0.006370    0.001788   0.000168
Select * from DSSBDW.RI  where UNQ_ID_SRC_STM = '2114999'  0.000038    0.000020   0.000001

Thanks Everyone.

2

Without knowing the maximum width of the text in your real data, without knowing what operating system or shell you're using, without knowing the name of the log file you want to process, assuming that the text in your input file should be copied verbatim (not dropping any characters as was done in your sample output), nor if you really want seemingly random spacing in the output you produce, you might trying using something like the following as a 1st guess at something that might work:

awk '
BEGIN {	fmt = "%-60s %-12s %-10s %s\n"
	printf(fmt, "Text", "ElapsedTime", "UserCPU", "SystemCPU")
	printf(fmt, "----", "-----------", "-------", "---------")
}
$1 == "Text" {
	text = substr($0, index($0, ":") + 2)
	next
}
$1 == "Elapsed" || $1 == "User"  || $1 == "System" {
	times[$1] = $(NF - 1)
}
$1 == "System" {
	printf(fmt, text, times["Elapsed"], times["User"], times["System"])
}' log

which, if the sample input you provided in post #1 in this thread is in a text file named log , produces the following output:

Text                                                         ElapsedTime  UserCPU    SystemCPU
----                                                         -----------  -------    ---------
SELECT DISTINCT Block_Code FROM   DSSBDW.CC_PL_TRX           0.006370     0.001788   0.000168
Select * from DSSBDW.RI  where UNQ_ID_SRC_STM = '2114999''   0.000038     0.000020   0.000001

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .

3

Try

awk -F":"       '
BEGIN           {HD = "Text     ElapsedExecutionTime    UserCPU SystemCPU"
                 for (MX=n=split (HD, HDArr, OFS); n>0; n--) SRCH[HDArr[n]]
                 print HD
                 gsub (/[^\t]/, "-", HD)
                 print HD
                }

!NF             {for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr], (i == MX)?ORS:OFS
                 delete RES
                }

                {gsub (/^[ "]*| *$/, "", $1)
                }

$1 in SRCH      {RES[$1] = $0
                 sub ($1, "", RES[$1])
                 sub (/ *seconds *$/, "", RES[$1])
                 sub (/^[ \t]*/, "", RES[$1])
                }

END             {for (i=1; i<=MX; i++) printf "%s%s", RES[HDArr], (i == MX)?ORS:OFS
                }

' OFS="\t" file
Text    ElapsedExecutionTime    UserCPU    SystemCPU
----    --------------------    -------    ---------
SELECT DISTINCT Block_Code FROM   DSSBDW.CC_PL_TRX    0.006370    0.001788    0.000168
Select * from DSSBDW.RI  where UNQ_ID_SRC_STM = '2114999''    0.000038    0.000020    0.000001
4

Thanks Don and RudiC for your contribution, my environment is AIX server Power 8. The above log was generated by database event monitor file and I would like to tabular it into row base report for easy traceability purpose.

I will try your awesome script. Thanks again guys.