0

I have a requirement to read some parameters from log file and then use this data in an Insert statement to create records in a a database table.

Input is log file testapi.log which may consists of below lines or more-

[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

I need to apply some filters - paymentType as P2M and processor as ABD and retrieve the following values - amount, responseCode, acctNumLastFour etc.

Then, I need to use these values in Insert statement to create records in a database table in Oracle.

edit

I have tried below code -

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 

which is printing same records several times and also can't retrieve those values in to shell or in insert SQL queries

expected result (values should be available in bash variables so they can be used to insert in to SQL)-

responseCode="00", tranType="PAY", amount = "1.20", processor = "ABD", accountNumberLastFour="0277"

  • 1
    post the expected result for your current input content – RomanPerekhrest May 24 '18 at 19:16
  • actual is acctNumLastFour="0277" but not accountNumberLastFour="1234" – RomanPerekhrest May 24 '18 at 20:35
0

Awk solution:

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
  • Thanks a lot. I will verify this against my script and update. If i add more parameters to be retrieved, does this line $1=$2=... has to be changed as well? – ChilBud May 24 '18 at 21:54
  • as for adding more parameters: it would depend if those occur before tranType="PAY" parameter or after it. – RomanPerekhrest May 25 '18 at 5:45
  • This is what I am using now and values print differently with the above code as there are more params - awk -F',' \ '/paymentType="P2M"/ && /processor="BNC"/{ $1=$2=$3=$4=$5=$6=$7=$8=$9=$10; for (i=10; i<=NF; i++) if ($i ~ /(responseCode|tranType|systemTraceAuditNumber|custRefNum|amount|acctNumLastFour|cardAcceptorTerminalId|transactionLocalDateTime)=/){ – ChilBud May 25 '18 at 14:30
  • what you are using is wrong $1=$2=$3=$4=$5=$6=$7=$8=$9=$10. The above solution works fine for your current input fragment – RomanPerekhrest May 25 '18 at 15:07
  • Its printing like this after changing back to your code - frist record seems to be printing incorrect values responseCode: "00" tranType: "FUND" systemTraceAuditNumber: "TRNREF_20180524125323809" custRefNum: "1.20" amount: "0149" acctNumLastFour: cardAcceptorTerminalId: transactionLocalDateTime: responseCode: "00" tranType: "PAY" systemTraceAuditNumber: "351883" custRefNum: "TRNREF_20180524125323809" amount: "1.20" acctNumLastFour: "0277" cardAcceptorTerminalId: "ABCD1234" transactionLocalDateTime: "2017-09-22T13:22:11-05:30" – ChilBud May 25 '18 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.