Help to extract the field from the JSON response

[vijaysubramania]

Hi,
I need to extract the values for the below-mentioned keys from the below-mentioned log.
I used spath but it's not working. Please assist in the same.
divisionID
accountNumber
ppvCreditLimit
ppvRemainingCreditLimit
accountStatus

• Note: some of the data has been masked for security

  ":"{
  \n\"getAccountResponse\" : {\n \"account\" : {\n \"divisionID\" : \"ABC.202\",\n \"accountNumber\" : \"111122222\",\n \"customerNumber\" : \"19118902\",\n \"locationNumber\" : \"191189\",\n \"billingStationLevel0Code\" : \"202\",\n \"billingStationLevel1Code\" : \"50\",\n \"billingStationLevel2Code\" : \"05\",\n \"sourceFTACode\" : \"005\",\n \"accountType\" : {\n \"billerCode\" : \"R\",\n \"enterpriseCode\" : \"RESIDENTIAL\",\n \"description\" : \"Residential\",\n \"longDescription\" : null\n },\n \"accountStatus\" : \"Active\",\n \"billerAccountStatus\" : \"A\",\n \"connectDate\" : \"2019-10-31\",\n \"classification\" : \"SFU\",\n \"name\" : {\n \"last\" : \"raft\",\n \"first\" : \"xxx\"\n },\n \"serviceAddress\" : {\n \"line1\" : \" SAGE ST\",\n \"city\" : \"COL\",\n \"state\" : \"SC\",\n \"postalCode\" : \"211051132\"\n },\n \"phone\" : [ {\n \"number\" : \"9999999999\",\n \"type\" : \"Home\"\n } ],\n \"lineOfBusinessDetail\" : [ {\n \"type\" : {\n \"billerCode\" : \"C\",\n \"enterpriseCode\" : \"VIDEO\",\n \"description\" : \"Video\",\n \"longDescription\" : \"Video\"\n },\n \"status\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"LOBCONNECTED\",\n \"description\" : \"LOB Connected\",\n \"longDescription\" : \"LOB Connected\"\n }\n }, {\n \"type\" : {\n \"billerCode\" : \"D\",\n \"enterpriseCode\" : \"HSD\",\n \"description\" : \"HSD\",\n \"longDescription\" : \"HSD\"\n },\n \"status\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"LOBCONNECTED\",\n \"description\" : \"LOB Connected\",\n \"longDescription\" : \"LOB Connected\"\n }\n } ],\n \"experianPin\" : \"xxxxx140299\",\n \"accountDetail\" : {\n \"totalCurrentBalance\" : 212.31,\n \"totalPendingAmount\" : 0.00,\n \"totalLastPayment\" : -27.00,\n \"totalAmountDue\" : 312.31,\n \"ppvCreditLimit\" : 0.00,\n \"ppvRemainingCreditLimit\" : 0.00,\n \"language\" : {\n \"billerCode\" : \"ENGL\",\n \"enterpriseCode\" : \"ENGLISH\",\n \"description\" : \"English\",\n \"longDescription\" : \"English\"\n },\n \"auditCreationDate\" : \"2019-10-31\",\n \"locationType\" : {\n \"billerCode\" : \"J\",\n \"enterpriseCode\" : \"RSSINFAM\",\n \"description\" : \"Single Family Home\",\n \"longDescription\" : \"Single Family Home\"\n },\n \"bulkFlag\" : \"N\",\n \"vipCode\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"OWNER\",\n \"description\" : \"Owner\",\n \"longDescription\" : \"Owner\"\n },\n \"billingDetails\" : [ {\n \"currentStatementId\" : \"151441\",\n \"statementCode\" : \"1\",\n \"cycleDay\" : \"1\",\n \"fromDate\" : \"2020-04-01\",\n \"thruDate\" : \"2019-04-30\",\n \"amountDue\" : 312.31,\n \"frequency\" : {\n \"billerCode\" : \"M\",\n \"enterpriseCode\" : \"MONTHLY\",\n \"description\" : \"Monthly Billing\",\n \"longDescription\" : \"Monthly Billing\"\n },\n \"dunningGroup\" : \"0\",\n \"futureDatedFlag\" : \"N\",\n \"paperlessFlag\" : \"N\",\n \"currentBalance\" : 312.31,\n \"lastPaymentDate\" : \"2020-03-09\",\n \"lastPaymentAmount\" : -27.00,\n \"paymentDueDate\" : \"2020-04-18\",\n \"pendingPayment\" : 0.00,\n \"cycle1Amount\" : 156.08,\n \"cycle2Amount\" : 0.00,\n \"cycle3Amount\" : 0.00,\n \"delinquencyAmount\" : 156.08,\n \"delinquencyStatus\" : {\n \"billerCode\" : \"_\",\n \"enterpriseCode\" : \"NORMAL\",\n \"description\" : \"Normal\",\n \"longDescription\" : \"Normal\"\n },\n \"daysDelinquent\" : 44,\n \"billToName\" : {\n \"first\" : \"xxx raft\"\n },\n \"billToAddress\" : {\n \"line1\" : \"SAGE ST\",\n \"city\" : \"COL\",\n \"state\" : \"SC\",\n \"postalCode\" : \"211051132\"\n },\n \"statementHold\" : {\n \"billerCode\" : \"P\",\n \"enterpriseCode\" : \"PAPERONLY\",\n \"description\" : \"Paper Bill Only\",\n \"longDescription\" : \"Paper Bill Only\"\n },\n \"promiseAmount\" : 0.00,\n \"promiseActivityCode\" : \"41\",\n \"billingCurrentBalance\" : 312.31,\n \"statementBalance\" : 312.31,\n \"electronicFlag\" : \"N\",\n \"adjustedDelinquencyAmount\" : 156.08\n } ]\n },\n \"accountCategory\" : \"Re\",\n \"accountSegment\" : \"Re\"\n },\n \"sourceSystemTimeZone\" : \"-04:00\"\n }\n}",
  "responseTime": 551

[to4kawa]

| makeresults
| eval _raw="\":\"{
       \n\"getAccountResponse\" : {\n \"account\" : {\n \"divisionID\" : \"ABC.202\",\n \"accountNumber\" : \"111122222\",\n \"customerNumber\" : \"19118902\",\n \"locationNumber\" : \"191189\",\n \"billingStationLevel0Code\" : \"202\",\n \"billingStationLevel1Code\" : \"50\",\n \"billingStationLevel2Code\" : \"05\",\n \"sourceFTACode\" : \"005\",\n \"accountType\" : {\n \"billerCode\" : \"R\",\n \"enterpriseCode\" : \"RESIDENTIAL\",\n \"description\" : \"Residential\",\n \"longDescription\" : null\n },\n \"accountStatus\" : \"Active\",\n \"billerAccountStatus\" : \"A\",\n \"connectDate\" : \"2019-10-31\",\n \"classification\" : \"SFU\",\n \"name\" : {\n \"last\" : \"raft\",\n \"first\" : \"xxx\"\n },\n \"serviceAddress\" : {\n \"line1\" : \" SAGE ST\",\n \"city\" : \"COL\",\n \"state\" : \"SC\",\n \"postalCode\" : \"211051132\"\n },\n \"phone\" : [ {\n \"number\" : \"9999999999\",\n \"type\" : \"Home\"\n } ],\n \"lineOfBusinessDetail\" : [ {\n \"type\" : {\n \"billerCode\" : \"C\",\n \"enterpriseCode\" : \"VIDEO\",\n \"description\" : \"Video\",\n \"longDescription\" : \"Video\"\n },\n \"status\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"LOBCONNECTED\",\n \"description\" : \"LOB Connected\",\n \"longDescription\" : \"LOB Connected\"\n }\n }, {\n \"type\" : {\n \"billerCode\" : \"D\",\n \"enterpriseCode\" : \"HSD\",\n \"description\" : \"HSD\",\n \"longDescription\" : \"HSD\"\n },\n \"status\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"LOBCONNECTED\",\n \"description\" : \"LOB Connected\",\n \"longDescription\" : \"LOB Connected\"\n }\n } ],\n \"experianPin\" : \"xxxxx140299\",\n \"accountDetail\" : {\n \"totalCurrentBalance\" : 212.31,\n \"totalPendingAmount\" : 0.00,\n \"totalLastPayment\" : -27.00,\n \"totalAmountDue\" : 312.31,\n \"ppvCreditLimit\" : 0.00,\n \"ppvRemainingCreditLimit\" : 0.00,\n \"language\" : {\n \"billerCode\" : \"ENGL\",\n \"enterpriseCode\" : \"ENGLISH\",\n \"description\" : \"English\",\n \"longDescription\" : \"English\"\n },\n \"auditCreationDate\" : \"2019-10-31\",\n \"locationType\" : {\n \"billerCode\" : \"J\",\n \"enterpriseCode\" : \"RSSINFAM\",\n \"description\" : \"Single Family Home\",\n \"longDescription\" : \"Single Family Home\"\n },\n \"bulkFlag\" : \"N\",\n \"vipCode\" : {\n \"billerCode\" : \"1\",\n \"enterpriseCode\" : \"OWNER\",\n \"description\" : \"Owner\",\n \"longDescription\" : \"Owner\"\n },\n \"billingDetails\" : [ {\n \"currentStatementId\" : \"151441\",\n \"statementCode\" : \"1\",\n \"cycleDay\" : \"1\",\n \"fromDate\" : \"2020-04-01\",\n \"thruDate\" : \"2019-04-30\",\n \"amountDue\" : 312.31,\n \"frequency\" : {\n \"billerCode\" : \"M\",\n \"enterpriseCode\" : \"MONTHLY\",\n \"description\" : \"Monthly Billing\",\n \"longDescription\" : \"Monthly Billing\"\n },\n \"dunningGroup\" : \"0\",\n \"futureDatedFlag\" : \"N\",\n \"paperlessFlag\" : \"N\",\n \"currentBalance\" : 312.31,\n \"lastPaymentDate\" : \"2020-03-09\",\n \"lastPaymentAmount\" : -27.00,\n \"paymentDueDate\" : \"2020-04-18\",\n \"pendingPayment\" : 0.00,\n \"cycle1Amount\" : 156.08,\n \"cycle2Amount\" : 0.00,\n \"cycle3Amount\" : 0.00,\n \"delinquencyAmount\" : 156.08,\n \"delinquencyStatus\" : {\n \"billerCode\" : \"_\",\n \"enterpriseCode\" : \"NORMAL\",\n \"description\" : \"Normal\",\n \"longDescription\" : \"Normal\"\n },\n \"daysDelinquent\" : 44,\n \"billToName\" : {\n \"first\" : \"xxx raft\"\n },\n \"billToAddress\" : {\n \"line1\" : \"SAGE ST\",\n \"city\" : \"COL\",\n \"state\" : \"SC\",\n \"postalCode\" : \"211051132\"\n },\n \"statementHold\" : {\n \"billerCode\" : \"P\",\n \"enterpriseCode\" : \"PAPERONLY\",\n \"description\" : \"Paper Bill Only\",\n \"longDescription\" : \"Paper Bill Only\"\n },\n \"promiseAmount\" : 0.00,\n \"promiseActivityCode\" : \"41\",\n \"billingCurrentBalance\" : 312.31,\n \"statementBalance\" : 312.31,\n \"electronicFlag\" : \"N\",\n \"adjustedDelinquencyAmount\" : 156.08\n } ]\n },\n \"accountCategory\" : \"Re\",\n \"accountSegment\" : \"Re\"\n },\n \"sourceSystemTimeZone\" : \"-04:00\"\n }\n}\",
       \"responseTime\": 551"
| rex mode=sed "s/\\\n//g s/(?ms)^.*?({.*}).*$/\1/"
| spath

props.conf

SEDCMD-trim = s/\\\n//g s/(?ms)^.*?({.*}).*$/\1/
KV_MODE = json

[tauliang]

Here is what I did with your JSON:

...
|spath 
|rename getAccountResponse.account.divisionID as divisionId
|rename getAccountResponse.account.accountNumber as accountNumber
|rename getAccountResponse.account.accountDetail.ppvCreditLimit as ppvCreditLimit
|rename getAccountResponse.account.accountDetail.ppvRemainingCreditLimit as ppvRemainingCreditLimit
|rename getAccountResponse.account.accountStatus as accountStatus
|table divisionId, accountNumber, ppvCreditLimit, ppvRemainingCreditLimit, accountStatus

You might want to do some preprocessing to get rid of the extra white spaces and "\n" etc.

And I was able to get results back. Refer to spath reference for more details if needed.