Using sprintf and system command in awk

EAGL · March 29, 2016, 9:26am

Hello Friends,

I'm trying something hard (for me) to create a report script,normally the following script works:

Echos are just for cosmetic touch,

echo -n "\n-----\t----------\t-------------\t\t--------------\t\t--------\n
COUNT\tEVENT_TYPE\tRESPONSE_CODE\t\tINTERNAL_ERROR\t\tFLOWNAME
\n-----\t----------\t-------------\t\t--------------\t\t--------------------------------\n" 
&& gzcat *2016-03-29_*gz | gawk -F"|" '
BEGIN{format = "%-10s\t%-8s\t%-14s\t%-32s\n"}{if(( $5 == 2001 )&&( $8 != 0 )&&( $9 != 0 )&&( $12 ~ "fulfillService" )) 
printf format, "\t"$5"\t",$8"\t\t",$9"\t",$(NF-1)}' | sort | uniq -c 
&& echo -n "-----\t----------\t-------------\t\t--------------\t\t--------------------------------\n"

Output:

-----   ----------      -------------           --------------          --------
COUNT   EVENT_TYPE      RESPONSE_CODE           INTERNAL_ERROR          FLOWNAME
-----   ----------      -------------           --------------          --------------------------------
4506    2001            -3                      114                     refillVoucherless               
11472   2001            -3                      115                     refillVoucherless               
  12    2001            -4                      11111                   addAndActivateService           
   4    2001            -4                      13                      setHLRSub                       
-----   ----------      -------------           --------------          --------------------------------

I need to add up extra field to output as "OFFER NAME"
this output will come from 14th field of input files (pipe seperated) after stripping them by using SED and when I run the following command it works; it fetches what I want:

nawk -F\| '{if(( $5 == 2001 ) && ( $8 != 0 ) && ( $9 != 0 ) && ( $12 ~ "fulfillService" ) print $14}' infile 
| sed 's#.*\(externalofferid":"\)\(.*\)\(","receiversubscriber\).*#\2#'

gives something like:

ABC_OFFER

Desired Outout is :

-----   ----------	----------      -------------           --------------          --------
COUNT   OFFER_NAME 	EVENT_TYPE      RESPONSE_CODE           INTERNAL_ERROR          FLOWNAME
-----   ----------	----------      -------------           --------------          ---------------------
4506    AAA			2001            -3                      114                     refillVoucherless               
11472   BBB			2001            -3                      115                     refillVoucherless               
  12    CCC			2001            -4                      11111                   addAndActivateService           
   4    			2001            -4                      13                      setHLRSub                       
-----   ----------	----------      -------------           --------------          -----------------------

However when I want to add it to the whole command above it fails
I though I had to use a system command (but i'm not sure)
could you please suggest/correct the command?

I tried (did not show echoes part; its already confusing):

gzcat *2016-03-29_*gz | gawk -F"|" '
BEGIN{format = "%-20s\t%-10s\t%-8s\t%-14s\t%-32s\n"}{if(( $5 == 2001 )&&( $8 != 0 )&&( $9 != 0 )&&( $12 ~ "fulfillService" )) 
sprintf offer==system("sed 's@.*\(externalofferid":"\)\(.*\)\(","receiversubscriber\).*@\2@'"$14) ; 
printf format, "\t"offer"\t"$5"\t",$8"\t\t",$9"\t",$(NF-1)}' | sort | uniq -c

I believe I don't need sprintf as it prints the stripped string..

error:

gawk:      
 ^ syntax error

Thanks in advance.

RudiC · March 29, 2016, 10:20am

Not clear. You want to print WHAT as the second output field? Where does it come from? Field 14 of the input stream? Any conditions to apply?

EAGL · March 29, 2016, 10:30am

I want to print Offernames like AAA, BOB.. Which I can strip from 14th field of gunzipped input files by using the SED command I have displayed above
14th field is actually complicated json field
I used see to strip what I want I know there could be awk scripts to strip Jason inputs..Briefly I need to add a field as an alphanumeric string which I can strip with sed
shown

RudiC · March 29, 2016, 10:39am

Why don't you post a line or two of the original (unzipped!) input file?

EAGL · March 29, 2016, 11:38am

It was long input and was on my phone not computer thats why didnt have input, thanks for asking for it Rudic..

36087|20160309115933427|20160309115933481|54|2001|||-3|114|Buiness exception|u:so1-750564413014575139599421000:ffe33-7505644130-14575139734171001-0001-01|fulfillService|ID-SSCM-ESB1-56218-1455665881255-13-319406599|{"interval":{"attributes":{"attribute":[{"name":"INITIAL_CHARGE_OPTION","value":"0"},{"name":"FULFILL_ON_RESERVE","value":"1"}]},"service":{"serviceid":800857,"externalserviceid":"EcRefillService","servicetype":"REFILL","serviceprofile":"REFILL","attributes":{"attribute":[{"name":"REFILL_AMOUNT","value":"600"},{"name":"REFILL_ID","value":"21"},{"name":"DA_UNIT_TYPE","value":"1"},{"name":"UPDATE_SERVICE","value":"0"}]},"grantedservicestartdate":"20160309115920","grantedserviceenddate":"20160309120020","requestedduration":1,"requesteddurationtype":5,"grantedduration":60000,"actualprice":"0.0","ratedprice":"0.0","prorationenabled":false},"subscriptionintervalid":61446288,"enddate":"20160408115920","startdate":"20160309115920","payersubscriber":{"msisdn":"9647505644130","paymentmethod":2,"imsi":"418400205051815","preferredlanguage":2},"campaignid":2000458,"elligibilityoption":1,"status":6,"sequence":1,"requestchannel":"USSD","transactionid":"u:so1-750564413014575139599421000","initialfeeperiodduration":0,"initialfeeperiodtype":0,"chargingperiodduration":1,"chargingperiodtype":6,"chargingperiodnumber":1},"attribute":[{"name":"QUERY_SUBSCRIPTION_HISTORY","value":"false","actiontype":"NEW"},{"name":"FRP_RETRY_REJECT","value":"false","actiontype":"NEW"},{"name":"QUERY_TARIFF_PLAN","value":"false","actiontype":"NEW"},{"name":"INITIAL_CHARGE_OPTION","value":"0","actiontype":"NEW"},{"name":"FULFILL_ON_RESERVE","value":"1","actiontype":"NEW"},{"name":"REFILL_AMOUNT","value":"600","actiontype":"NEW"},{"name":"REFILL_ID","value":"21","actiontype":"NEW"},{"name":"DA_UNIT_TYPE","value":"1","actiontype":"NEW"},{"name":"UPDATE_SERVICE","value":"0","actiontype":"NEW"},{"name":"EXT_DATA_1","value":"EC:Emergency Credit"},{"name":"EXT_DATA_2","value":"Emergency Credit:EC Plan"},{"name":"CURRENCY","value":"IQD"},{"name":"AMOUNT","value":"600000"}],"fulfillservice":{"subscription":{"attributes":{"attribute":[{"name":"QUERY_SUBSCRIPTION_HISTORY","value":"false"},{"name":"FRP_RETRY_REJECT","value":"false"},{"name":"QUERY_TARIFF_PLAN","value":"false"}]},"esdpofferid":"2000457","externalofferid":"EmergencyCreditOffer","receiversubscriber":{"msisdn":"9647505644130","paymentmethod":2,"imsi":"418400205051815","preferredlanguage":2},"startdate":"20160309115920","status":6,"externalsubscriptionid":"64750564413053192904","subscriptionid":60985319},"fulfillment":0,"fulfillmentreason":1,"requesttime":"20160309115933","transactionid":"u:so1-750564413014575139599421000:ffe33-7505644130-14575139734171001-0001-01","previousfulfillmentreason":0},"nodeidentifier":"REFILL","msisdn":"9647505644130","transactionid":"u:so1-750564413014575139599421000:ffe33-7505644130-14575139734171001-0001-01","imsi":"418400205051815","fulfill_reason":"PROV"}||{ "NodeResponse": {"status":"ERROR", "responseCode":"-3", "responseDescription":" Business exception", "internalError":"114","jObject":{} }   }|SscmAirService|refillVoucherless|

I need the red highlighted one and its in 14th field; a json input.. SED part of my command gives it.. but could not connect it to main code,

RudiC · March 29, 2016, 1:06pm

OK, how about

awk -F"|" '
BEGIN           {cfmt   = "%-10s"
                 format = "\t%-10s\t%-20s\t%-14s\t%-14s\t%-32s\n"
                 UL     = sprintf (cfmt format, _, _, _, _, _, _)
                 gsub (" ", "-", UL)
                 printf "%s", UL
                 printf cfmt format, "COUNT", "EVENT_TYPE", "OFFER_NAME", "RESPONSE_CODE", "INTERNAL_ERROR", "FLOWNAME"
                 printf "%s", UL
                 cmd    =  "sort | uniq -c"
                }

END             {close (cmd)
                 printf "%s", UL
                }

$12 ~  "fulfillService" &&
$5  == 2001 &&
$8  != 0    &&
$9  != 0        {match ($14, /externalofferid[^,]*/)
                 printf format, $5, substr ($14, RSTART+18, RLENGTH-19), $8, $9, $(NF-1) | cmd
                }
' file
----------      ----------      --------------------    --------------  --------------  --------------------------------
COUNT           EVENT_TYPE      OFFER_NAME              RESPONSE_CODE   INTERNAL_ERROR  FLOWNAME
----------      ----------      --------------------    --------------  --------------  --------------------------------
      6         2001            EmergencyCreditOffer    -3              114             refillVoucherless
      2         2001            EmergencyCreditOffer    -4              114             refillVoucherless
      1         2001            EmergencyCreditOffer    -5              114             refillVoucherless
      2         2001            EmergencyCreditOffer    -6              114             refillVoucherless
----------      ----------      --------------------    --------------  --------------  --------------------------------

EAGL · March 29, 2016, 2:25pm

Thanks a lot Rudic,

When I tested I saw that slightly the columns are shifted as the following (Event Types must be 2001; and all columns should be shifted to right side similarly):

-bash-4.1$ ./tst.sh 
----------      ----------      --------------------    --------------  --------------  --------------------------------
COUNT           EVENT_TYPE      OFFER_NAME              RESPONSE_CODE   INTERNAL_ERROR  FLOWNAME                        
----------      ----------      --------------------    --------------  --------------  --------------------------------
   4    2001            Blackberry Social       -2              320001          deactivateBBService             
8328    2001            EmergencyCreditOffer    -3              114             refillVoucherless               
21431   2001            EmergencyCreditOffer    -3              115             refillVoucherless               
   5    2001            Internet Daily          -2              320001          setHLRSub                       
  13    2001            Internet Daily          -4              11111           addAndActivateService           
   8    2001            Internet Daily          -4              13              setHLRSub                       
   5    2001            Internet Weekly 500     -2              320001          setHLRSub                       
   7    2001            Internet Weekly 500     -4              11111           addAndActivateService           
   9    2001            Internet Weekly 500     -4              13              setHLRSub                       
   2    2001            WhatAppDaily            -4              13              setHLRSub                       
----------      ----------      --------------------    --------------  --------------  --------------------------------

RudiC · March 30, 2016, 2:58am

Looks like your uniq -c count output differs from mine. Try playing around with <TAB> chars in the format string(s).