awk to format data into table

shanul_karim · April 30, 2020, 2:48am

Dear team,

Here is the input file from which in need to get output table with values from each blocks

as BC, DATE & TIME(Apr 29, 08:10),NSMSSMRLTOT ,NSMSSRSUCC ,NSMSCMTOT , NSMSCMRSUCC

more SHMSGSERV_FRMT
Thu Apr 30, 08:13 +03+0300 2020
GJ15_183_IPA03_EPA49

BC0 --------------------------------  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 08:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1       75718  NSMSSMRLTOT       BC0
     2       69284  NSMSSRSUCC        BC0
     3       71264  NSMSCMTOT         BC0
     4       70947  NSMSCMRSUCC       BC0
     5        4785  NSMSRDOTOT        BC0
     6         380  NSMSRAOSUCC       BC0
     7        4292  NSMSCDOTOT        BC0
     8         383  NSMSCAOSUCC       BC0
     9          28  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 09:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      132640  NSMSSMRLTOT       BC0
     2      117741  NSMSSRSUCC        BC0
     3      121283  NSMSCMTOT         BC0
     4      120752  NSMSCMRSUCC       BC0
     5        4414  NSMSRDOTOT        BC0
     6         391  NSMSRAOSUCC       BC0
     7        3913  NSMSCDOTOT        BC0
     8         393  NSMSCAOSUCC       BC0
     9          29  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 10:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      136221  NSMSSMRLTOT       BC0
     2      120417  NSMSSRSUCC        BC0
     3      124147  NSMSCMTOT         BC0
     4      123444  NSMSCMRSUCC       BC0
     5        4717  NSMSRDOTOT        BC0
     6         557  NSMSRAOSUCC       BC0
     7        4316  NSMSCDOTOT        BC0
     8         560  NSMSCAOSUCC       BC0
     9          43  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 11:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      148035  NSMSSMRLTOT       BC0
     2      131664  NSMSSRSUCC        BC0
     3      135324  NSMSCMTOT         BC0
     4      134636  NSMSCMRSUCC       BC0
     5        4914  NSMSRDOTOT        BC0
     6         684  NSMSRAOSUCC       BC0
     7        4483  NSMSCDOTOT        BC0
     8         686  NSMSCAOSUCC       BC0
     9          64  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0

From below awk command ,the output as below. for first line only date and time printed whereas it should have all four values

aawk '/-/{print a["s"] " " a["t"] " " a["o"] " " a["p"] " " a["q"] " " a["r"];delete a}/NSMSRDOTOT/{a["s"]=$4;next}/OK/{a["t"]=substr($0,4,14)}/NSMSSMRLTOT/{a["o"]=$2;next}/NSMSSRSUCC/{a["p"]=$2;next}/NSMSCMTOT/{a["q"]=$2;next}/NSMSCMRSUCC/{a["r"]=$2;next}END{print a["s"] " " a["t"] " " a["s"] " " a["o"] " " a["p"] " " a["q"] " " a["r"];}' SHMSGSERV_FRMT > SHMSGSERV_ST1

eshaqur@SA-00001256 ~
$ more SHMSGSERV_ST1

Apr 29, 08:10
BC0 Apr 29, 09:10 75718 69284 71264 70947
BC0 Apr 29, 10:10 132640 117741 121283 120752
BC0 Apr 29, 11:10 136221 120417 124147 123444
BC0 Apr 29, 12:10 148035 131664 135324 134636
BC0 Apr 29, 13:10 152827 135744 140179 139368
BC0 Apr 29, 14:10 150850 133703 138401 137207
BC0 Apr 29, 15:10 163147 145137 150564 149213
BC0 Apr 29, 16:10 155973 139644 144903 143648
BC0 Apr 29, 17:10 128839 113721 118061 116733
BC0 Apr 29, 18:10 109105 94244 98666 97336
BC0 Apr 29, 19:10 275173 253999 261961 260140
BC0 Apr 29, 20:10 117569 103414 107990 107031
BC0 Apr 29, 21:10 154075 140012 145490 143989
BC0 Apr 29, 22:10 147183 132533 138084 136705
BC0 Apr 29, 23:10 116536 103502 108550 107136
BC0 Apr 30, 00:10 90627 81116 84834 83888
BC0 Apr 30, 01:10 91603 81704 85411 84404
BC0 Apr 30, 02:10 48717 41932 44387 43707
BC0 Apr 30, 03:10 25568 19609 21621 21069
BC0 Apr 30, 04:10 24134 18141 20369 19818
BC0 Apr 30, 05:10 27281 21039 23085 22602
BC0 Apr 30, 06:10 20197 14548 16425 16006
BC0 Apr 30, 07:10 64339 57536 59944 59421
BC0 Apr 30, 08:10 67924 60928 63454 62923
BC0 75084 66988 69505 69025
Apr 29, 08:10
BC1 Apr 29, 09:10 73217 66623 68597 68300
BC1 Apr 29, 10:10 132352 117378 120546 120090
BC1 Apr 29, 11:10 136173 120310 123935 123225
BC1 Apr 29, 12:10 149633 132913 136477 135684
BC1 Apr 29, 13:10 155227 137945 142211 141269
BC1 Apr 29, 14:10 151757 134944 139330 138133
BC1 Apr 29, 15:10 163520 145789 150753 149422
BC1 Apr 29, 16:10 156308 140660 145262 144054
BC1 Apr 29, 17:10 128118 113240 117500 116231
BC1 Apr 29, 18:10 107601 92917 97077 95934
BC1 Apr 29, 19:10 275424 254697 262130 260353
BC1 Apr 29, 20:10 116571 103111 107399 106415
BC1 Apr 29, 21:10 151778 137831 143047 141578
BC1 Apr 29, 22:10 144230 130270 135890 134411
BC1 Apr 29, 23:10 116580 103719 108494 107286
BC1 Apr 30, 00:10 90498 81319 84828 83850
BC1 Apr 30, 01:10 90167 81645 84790 84000
BC1 Apr 30, 02:10 47141 40665 42801 42239
BC1 Apr 30, 03:10 26517 20543 22467 21901
BC1 Apr 30, 04:10 22754 16956 18901 18460
--More--(18%)

RudiC · April 30, 2020, 3:41am

That's because if your first printout trigger /-/ is hit, only that array element is set yet. On top, all further date/time stamps do not match the data printed with them; the data are shifted one line down.
Try finding another print trigger.

aigles · April 30, 2020, 12:00pm

A possible solution :

$ cat ./eshaqur.sh
awk '
    function printValues() {
        if (Values) {
            print  S, Date,   Value["NSMSSMRLTOT"],
                            Value["NSMSSRSUCC"],
                            Value["NSMSCMTOT"],
                            Value["NSMSCMRSUCC"];
        }
        delete Value;
    }
    $7=="(OK)" {
        printValues()
        Date = $2 " " $3 " " $4;
        S = $NF;
        next;
    }
    $1+0 {
        Value[$3] = $2;
        Values++;
        next ;
    }
    END {
        printValues()
    }
' SHMSGSERV_FRMT
$ ./eshaqur.sh
BC0 Apr 29, 08:10 75718 69284 71264 70947
BC0 Apr 29, 09:10 132640 117741 121283 120752
BC0 Apr 29, 10:10 136221 120417 124147 123444
BC0 Apr 29, 11:10 148035 131664 135324 134636

jpgir@LAPTOP-KJOA2D44 ~
$

shanul_karim · May 1, 2020, 6:56am

Thanks a lot jgir

This one very quick/fast one liner.

After this awk i used seperate awk to process it further in order to insert new column which has values equal to ($6/$5)*100 or so on. Is it possible to include this as well in your one liner.

1-9 ↩︎
1-9 ↩︎
1-9 ↩︎

shanul_karim · May 1, 2020, 8:38am

Dear RudC,

Got it, After changing /-/ hit to /=/ issue resolved.
Thanks for really great support

Shanul