ログファイルからいくつかのパラメータを読み取り、Insertステートメントでこのデータを使用してデータベーステーブルにレコードを作成する必要があります。
入力は、次の行以上で構成できるログファイル testapi.log です。
[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
P2MでPaymentType、ABDとしてプロセッサなど、いくつかのフィルタを適用し、amount、responseCode、acctNumLastFourなどの値を取得する必要があります。
次に、Insertステートメントでこの値を使用してOracleデータベース表にレコードを作成する必要があります。
編集する
私は次のコードを試しました -
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
同じレコードを複数回印刷し、シェルから値を取得したりSQLクエリを挿入したりすることはできません。
予想される結果（SQLへの挿入に使用できるようにbash変数で値を使用できる必要があります） -
responseCode="00", tranType="PAY", amount="1.20", processor="ABD", accountNumberLastFour="0277"
ベストアンサー1
Awk解決策：
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