Reducing number of awk passes

Question

I work on Teradata database and have a space check script. Script is supposed to raise flag as CRITICAL or WARNING depending on space usage values defined at start of the script.

My sample SQL file output is (DatabaseOutput.log) file is as below, this file is used as input to awk block.

### Some multiline Database query, resulting in Database Space usage

 *** Query completed. 11 rows found. 4 columns returned.
 *** Total elapsed time was 11 seconds.

## Output of the query, I am interested in DatabaseName, Perc2, MaxPerm

DatabaseName                                          Perc                    Perc2      MaxPerm
----------------------------------------------------  ----------------------  -------  -----------
AAA                                                   9.21899768137583E-001    92.19     10102320
BBB                                                   9.19923819717036E-001    91.99       524160
CCC                                                   9.17517791271651E-001    91.75      1687440
DDD                                                   9.15820363471060E-001    91.58       816720
EEE                                                   9.09293748338489E-001    90.93       149760
FFF                                                   9.07840905921109E-001    90.78      6934080
GGG                                                   9.04946085680591E-001    90.49      7273440
HHH                                                   8.54498111733230E-001    85.45      2538960
III                                                   8.22783559253080E-001    82.28      7598400
JJJ                                                   8.02181524253446E-001    80.22      8077680

+---------+---------+---------+---------+---------+---------+---+---------+-

Required output is

WARNING                           AAA     92.19  10102320
WARNING                           BBB     91.99    524160
WARNING                           CCC     91.75   1687440
WARNING                           DDD     91.58    816720
WARNING                           EEE     90.93    149760
WARNING                           FFF     90.78   6934080
WARNING                           GGG     90.49   7273440

I have working awk code which takes three passes.. Can it be reduced to one awk pass?

Working awk code :

cLvlCRIT=95
cLvlWARN=90

cat DatabaseOutput.log |
    awk '/-------------------/,/^$/' | # captures output block; it excludes query, logon, logoff information and header line but keeps separator's line.
        awk '{if (NR >= 2) {print}}' | # removes separator line, prints all lines from line 2 to EOF
            awk -v lLvlCRIT=$cLvlCRIT -v lLvlWARN=$cLvlWARN ' {
            if ( $1 != "StartCapture" && $3 >= lLvlCRIT ) {
                printf("%11s%30s%10s%10s\n", "CRITICAL",$1,$3,$4)
               }
            if ( $1 != "StartCapture" && $3 >= lLvlWARN && $3 < lLvlCRIT ) {
               printf("%11s%30s%10s%10s\n", "WARNING",$1,$3,$4)
              }
} '

Thanks in advance !

For improving _working_ code, your best best is https://codereview.stackexchange.com/ — kojiro, Jan 02 '18 at 16:13

dawg · Answer 1 · 2018-01-02T16:55:30.337

You can use flags to get a block in awk:

awk -v lLvlCRIT="$cLvlCRIT" -v lLvlWARN="$cLvlWARN" '
/^----------------------------------------------------/ {block=1; next}
/^$/ && block {exit}    # if there is only one data block pattern - exit
                        # otherwise just reset block to 0 to find next block
block { your code on the block }'

So to reproduce your example:

awk -v lLvlCRIT="$cLvlCRIT" -v lLvlWARN="$cLvlWARN" '
/^----------------------------------------------------/ {block=1; next}
/^$/ && block {exit}
block {if ( $3 >= lLvlCRIT )
     printf("%11s%30s%10s%10s\n", "CRITICAL",$1,$3,$4)       
else if ( $3 >= lLvlWARN )
     printf("%11s%30s%10s%10s\n", "WARNING",$1,$3,$4)  }' file
WARNING                           AAA     92.19  10102320
WARNING                           BBB     91.99    524160
WARNING                           CCC     91.75   1687440
WARNING                           DDD     91.58    816720
WARNING                           EEE     90.93    149760
WARNING                           FFF     90.78   6934080
WARNING                           GGG     90.49   7273440

score 3 · Answer 2 · answered Jan 02 '18 at 17:19

3

Google UUOC and never use a range expression as they make trivial tasks very slightly briefer but then require a complete rewrite or duplicate conditions for anything even slightly more interesting:

awk -v lLvlCRIT="$cLvlCRIT" -v lLvlWARN="$cLvlWARN" '
inBlock {
    if      ( $3 >= lLvlCRIT ) { level = "CRITICAL" }
    else if ( $3 >= lLvlWARN ) { level = "WARNING" }
    else if (NF)               { next }
    else                       { exit }
    printf "%11s%30s%10s%10s\n", level, $1, $3, $4
}
/-------------------/ { inBlock=1 }
' DatabaseOutput.log

answered Jan 02 '18 at 17:19

Ed Morton

188,023
17
78
185

@EdMortan I am not using cat in my code, I was re-directing my database query output directly to awk. I surely made mistake of adding it in my working code though. – Pankaj K Jan 03 '18 at 09:13
1

I am not using cat in my code, I was re-directing my database query output directly to awk. Thanks for your suggestion both on cat and duplicate conditions. Your's is most readable of the solutions I received! – Pankaj K Jan 03 '18 at 09:25

PesaThe · Accepted Answer · 2018-01-02T17:00:53.960

2

Your awk could look like this:

awk -v lLvlCRIT="$cLvlCRIT" -v lLvlWARN="$cLvlWARN" '
/^---/,/^$/ {
   if ( $0 ~ "^---" || $0 ~ "^$" ) next
   if ( $3 >= lLvlCRIT )
       printf("%11s%30s%10s%10s\n", "CRITICAL",$1,$3,$4)       
   else if ( $3 >= lLvlWARN )
       printf("%11s%30s%10s%10s\n", "WARNING",$1,$3,$4)               
}' DatabaseOutput.lo

Specifying pattern ranges in awk can be tricky and flags are the preferred approach. For more information, see Specifying Record Ranges with Patterns.

edited Jan 02 '18 at 17:00

answered Jan 02 '18 at 16:19

PesaThe

7,259
1
19
43

You still need the `NR>=2{print}` bit. You probably need to implement your own line counter based on the offset from the NR when `/----/,/^$/` is first matched. (In this case, that's just to discard the very first matched line.) – kojiro Jan 02 '18 at 16:21
Might quote the expansions on the first line, just to be sure we're demonstrating best practices for other situations where it *does* matter. – Charles Duffy Jan 02 '18 at 16:44
@CharlesDuffy True, thanks. I just copied the first line without thinking and posted first solution that came to my mind...I like the other answers more, I might even delete this one :) – PesaThe Jan 02 '18 at 16:48
Don't delete -- it is a credible answer and the first. You may want to add what `/^---/,/^$/` is doing.It *can* be tricky using the sed-like range operator in awk, but this is not one of them. As muscle memory, with awk, using flags is usually a little less troublesome than a range operator. See [example](https://stackoverflow.com/a/43983147/298607) – dawg Jan 02 '18 at 16:54
Never use a range expression. See the duplicate tests for `^---` and `^$` in your code. – Ed Morton Jan 02 '18 at 17:21
@PesaThe Thanks, I am using your code in my script now. – Pankaj K Jan 03 '18 at 09:07
@CharlesDuffy Thanks for your input about best practices. I am learning awk and where I can learn about best practises writing awk ? – Pankaj K Jan 03 '18 at 09:09

Matias Barrios · Answer 4 · 2018-01-02T17:48:41.320

1

This will do according to your sample input.

 #!/bin/bash

    cLvlCRIT=95
    cLvlWARN=90

   grep -E '^[a-zA-Z]+[ ]+[0-9.]+' DatabaseOutput.log |
         awk -v lLvlCRIT=$cLvlCRIT -v lLvlWARN=$cLvlWARN ' {
                if ( $1 != "StartCapture" && $3 >= lLvlCRIT ) {
                    printf("%11s%30s%10s%10s\n", "CRITICAL",$1,$3,$4)
                   }
                if ( $1 != "StartCapture" && $3 >= lLvlWARN && $3 < lLvlCRIT ) {
                   printf("%11s%30s%10s%10s\n", "WARNING",$1,$3,$4)
                  }
    } '

Regards!

edited Jan 02 '18 at 17:48

answered Jan 02 '18 at 16:29

Matias Barrios

4,674
3
22
49

`cat foo | grep ...` has a performance penalty over `grep ... – Charles Duffy Jan 02 '18 at 17:11
@CharlesDuffy Done! :) – Matias Barrios Jan 02 '18 at 17:48
Matias, Charles Thanks for inputs guys. I wanted to reduce number of commands used and number of awk passes.. Hence I am not using your solution, none the less it does intended work well :) – Pankaj K Jan 03 '18 at 09:30

score 1 · Answer 5 · answered Jan 02 '18 at 17:18

1

Following awk may also help you in same.

awk -v cLvlCRIT="$cLvlCRIT" -v cLvlWARN="$cLvlWARN" -v space="                           " '
/^$/||/^+/{
  flag="";
  next
}
/^----------/{
  flag=1;
  next
}
flag && $3>=cLvlWARN{
  val=$1 OFS $3 OFS $4;
  printf("%s"space"%s\n",$3>=cLvlCRIT?"CRITICAL":($3>=cLvlWARN && $3<cLvlCRIT?"WARNING":""),val)
}
'   Input_file

answered Jan 02 '18 at 17:18

RavinderSingh13

130,504
14
57
93

1

Thanks Ravinder, Your anwer gives me desired results. It was ternary operator was cool too. I found answers by PesaThe and Ed Mortan easier to understand, hence will use their answer. – Pankaj K Jan 03 '18 at 09:39

sjsam · Answer 6 · 2018-01-02T17:01:38.937

The primary concern here is what makes a valid input for awk. Below solution using flags is one way of doing it. This also takes into consideration the specific pattern of your input.

crit=95
warn=90
awk -v crit=$crit -v warn=$warn '
/^DatabaseName/{flag=1;next}
{$2=""}!flag{next}
$3>crit{printf "Critical\t\t%s%s",$0,ORS;next}
$3>warn{printf "Warning \t\t%s%s",$0,ORS}' DatabaseOutput.lo

Warning          AAA  92.19 10102320
Warning          BBB  91.99 524160
Warning          CCC  91.75 1687440
Warning          DDD  91.58 816720
Warning          EEE  90.93 149760
Warning          FFF  90.78 6934080
Warning          GGG  90.49 7273440

^{Sidenote: Pretty sure, though, that awk approach will be slow for a terabyte size file.}

Reducing number of awk passes

6 Answers6