Script AWK para ler alguns parâmetros do arquivo de log e inserir esses dados na tabela do banco de dados Oracle

Question

Script AWK para ler alguns parâmetros do arquivo de log e inserir esses dados na tabela do banco de dados Oracle

#1 resposta do (0 votos)

0

Eu tenho um requisito para ler alguns parâmetros do arquivo de log e, em seguida, usar esses dados em uma instrução Insert para criar registros em uma tabela de banco de dados.

A entrada é um arquivo de log testapi.log que pode consistir em linhas abaixo ou mais -

[2018-05-20T12:59:06,911] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [pool-8-thread-7] - DEBUG - com.test.webapp.services.external.BaseExecuteExternalCall - Line 207 - Cut off date in Update statement 2018-05-25
[2018-05-20T12:59:06,915] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [pool-8-thread-7] - DEBUG - com.test.webapp.services.external.BaseExecuteExternalCall - Line 323 - Settlement serv ID column position37 null 
[2018-05-20T12:59:06,915] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [pool-8-thread-7] - DEBUG - com.test.webapp.services.external.BaseExecuteExternalCall - Line 328 - TEST_ID column position 39 588712469 
[2018-05-20T12:59:06,915] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [pool-8-thread-7] - DEBUG - com.test.webapp.services.external.BaseExecuteExternalCall - Line 330 - UPDATE_CRTE_DT column position 40 1 
[2018-05-20T12:59:06,918] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [588712469] - INFO - com.test.webapp.services.external.TransactionProcessing - Line 121 - Service thread was notified ExecuteExternalCall processing completed; proceeding, timeOutStatus=GATEWAY_RESPONSE_RECEIVED
[2018-05-20T12:59:06,919] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [588712469] - INFO - com.test.webapp.services.functions.PaymentTransactionService - Line 823 - requestType="PAYMENT",partnerName="RegPartner4MPQRIND",partnerId="2853",lob="PERSON_TO_MERCHANT",tranType="PAY",paymentType="P2M",amount="1.20",currency="356",processor="ABD",network="TestNetwork",cardNetworkBinRangeId="938889",responseCode="00",transactionLocalDateTime="2017-09-22T13:22:11-05:30",systemTraceAuditNumber="351893",cardAcceptorTerminalId="ABCD1234",gatewayTime="15",cardBrand="MASTERCARD",cardbin="529992",acctNumLastFour="0277",issuer="MTF INTERNAL MEMBER ID - INDIA",binCountry="IND",binCurr="INR",fundAvailability="IMMEDIATE",status="APPROVED",reqRefId="rqst_4C82-BAF7-F8A7-D31E",custRefNum="TRNREF_20180524125902781",senderCountry="IND",senderState="MO",senderCity="OFallon",recipientCountry="IND",recipientState="TX",recipientCity="Dallas",mccUsedForTransaction="6536",statementDescriptor="testmerchant",reconDataCustomFieldName="Paymentid:123|tranid:456|reference:789"
[2018-05-20T12:59:06,919] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [588712469] - INFO - com.test.webapp.services.APIsServices - Line 1059 - Transaction Execution Response: {"sc":200,"transaction":{"transactionID":588712469},"transaction":{"transactionID":588712469,"cardNetworkBinRangeId":938889,"networkMerchantCategoryCode":"6536","status":"AVAILABLE","statusSource":"PROCESSED","statusDestination":"PROCESSED","networkSendTime":"24 05 2018, 12:59:06.895 PM","networkReceiveTime":"24 05 2018, 12:59:06.910 PM","network":"MoneySend","retrievalReferenceNumber":"814412351893","systemTraceAuditNumber":"351893","moneysendUniqueTransactionReference":"0000000000588712467","createdTime":"24 05 2018, 05:59:06.910 PM","processedTime":"24 05 2018, 05:59:06.910 PM","networkReferenceNum":"392694744","authorizationId":"0087C5","walletIdentifier":"","paymentAccountReference":"","mappedCardId":"","mappedCardExpiry":"","tokenRequestorId":"","paymentUid":"","fpid":"","rc":"00","responseCodeCategory":"APPROVED","responseDesc":"Approved","processor":"B","fundsAvailability":"Immediate"},"ep":0} , request TRNREF_20180524125902781
[2018-05-20T12:59:06,924] [RQST: rqst_4C82-BAF7-F8A7-D31E | HC: 2853] - [588712469] - INFO - com.test.webapp.services.APIsServices - Line 1084 - Completed request for method omney-depositFunds-oneTimeTrans ReferenceId: TRNREF_20180524125902781
[2018-05-20T12:59:14,522] [RQST:  | HC: ] - [Thread-85] - DEBUG - com.test.webapp.listener.reversal.AutoReversalManager - Line 109 - updated testparm size = 12
[2018-05-20T12:59:15,167] [RQST:  | HC: ] - [588712465] - DEBUG - com.test.webapp.services.APIsServices - Line 195 - 2:/checkstatus
[2018-05-20T12:59:15,169] [RQST:  | HC: ] - [588712465] - INFO - com.test.webapp.services.APIsServices - Line 1059 - Transaction Execution Response: {"sc":200,"checkStatus":{"status":200,"hostName":"cjb4stl20","containerName":"Accel","availSw":"Y"},"ep":0} , request 
[2018-05-20T12:59:15,169] [RQST:  | HC: ] - [588712465] - INFO - com.test.webapp.services.APIsServices - Line 1084 - Completed request for method null ReferenceId: 
[2018-05-20T12:59:16,798] [RQST:  | HC: ] - [Thread-86] - DEBUG - com.test.webapp.listener.clearing.epx.TestManager - Line 128 - updated testparm size = 12
[2018-05-20T12:59:16,799] [RQST:  | HC: ] - [Thread-86] - DEBUG - com.test.webapp.listener.clearing.epx.TestManager - Line 129 - Using batch for clearing = true
[2018-05-20T12:59:24,545] [RQST:  | HC: ] - [Thread-85] - DEBUG - com.test.webapp.listener.reversal.AutoReversalManager - Line 109 - updated testparm size = 12
[2018-05-20T12:59:34,216] [RQST:  | HC: ] - [588712465] - DEBUG - com.test.webapp.services.APIsServices - Line 195 - 2:/checkstatus
[2018-05-20T12:59:34,217] [RQST:  | HC: ] - [588712465] - INFO - com.test.webapp.services.APIsServices - Line 1059 - Transaction Execution Response: {"sc":200,"checkStatus":{"status":200,"hostName":"cjb4stl20","containerName":"Accel","availSw":"Y"},"ep":0} , request 
[2018-05-20T12:59:34,217] [RQST:  | HC: ] - [588712465] - INFO - com.test.webapp.services.APIsServices - Line 1084 - Completed request for method null ReferenceId: 
[2018-05-20T12:59:34,568] [RQST:  | HC: ] - [Thread-85] - DEBUG - com.test.webapp.listener.reversal.AutoReversalManager - Line 109 - updated testparm size = 12
[2018-05-20T12:59:44,591] [RQST:  | HC: ] - [Thread-85] - DEBUG - com.test.webapp.listener.reversal.AutoReversalManager - Line 109 - updated testparm size = 12
[2018-05-20T12:59:46,800] [RQST:  | HC: ] - [Thread-86] - DEBUG - com.test.webapp.listener.clearing.epx.TestManager - Line 128 - updated testparm size = 12
[2018-05-20T12:59:46,800] [RQST:  | HC: ] - [Thread-86] - DEBUG - com.test.webapp.listener.clearing.epx.TestManager - Line 129 - Using batch for clearing = true
[2018-05-20T12:59:50,030] [RQST:  | HC: ] - [588712465] - DEBUG - com.test.webapp.services.APIsServices - Line 195 - 2:/checkstatus
[2018-05-20T12:59:50,031] [RQST:  | HC: ] - [588712465] - INFO - com.test.webapp.services.APIsServices - Line 1059 - Transaction Execution Resp

Eu preciso aplicar alguns filtros - paymentType como P2M e processador como ABD e recuperar os seguintes valores - amount, responseCode, acctNumLastFour etc.

Depois, preciso usar esses valores na instrução Insert para criar registros em uma tabela de banco de dados no Oracle.

editar

Eu tentei abaixo do código -

awk -F' - ' '{
    n = split($NF,a,",");
    for(i=1; i<=n; i++) {
            split(a[i],b,"=");
            kv[b[1]]=b[2]
    }
}

kv["processor"]=="\"ABD\"" && kv["paymentType"]=="\"P2M\""
{ 
print "responseCode:\t" kv["responseCode"], "tranType:\t" kv["tranType"],  "amount:\t", kv["amount"], "accountNumberLastFour:\t" kv["acctNumLastFour"]}' testapi.log

que está imprimindo os mesmos registros várias vezes e também não pode recuperar esses valores no shell ou em inserir consultas SQL

resultado esperado (os valores devem estar disponíveis em variáveis bash para que possam ser usados para inserir no SQL) -

responseCode="00", tranType="PAY", quantia="1.20", processador="ABD", accountNumberLastFour="0277"

sql awk shell-script

por ChilBud 24.05.2018 / 20:55

1 resposta

Tags sql awk shell-script

Rastreando campos dinâmicos em Munin Perl sendmail Atributo aprovado - o que é equivalente ao correio do Linux

score 0 · Accepted Answer

Awk solução:

awk -F',' \
'/paymentType="P2M"/ && /processor="ABD"/{
     $1=$2=$3=$4=$5;
     for (i=6; i<=NF; i++) 
         if ($i ~ /(paymentType|processor|amount|responseCode|acctNumLastFour|tranType)=/){
             split($i, item, "="); 
             arr[item[1]] = item[2]
         }
          print arr["processor"], arr["paymentType"], arr["responseCode"], \
                arr["tranType"], arr["amount"], arr["acctNumLastFour"]
}' testapi.log \
| while read -r processor paymentType responseCode tranType amount acctNumLastFour; do
    # processing all obtained variables
    echo "$paymentType";
    # ... preparing SQL statement
done