Getting Data In
×

Join the Conversation

Without signing in, you're just watching from the sidelines. Sign in or Register to connect, share, and be part of the Splunk Community.
Ask a Question

Help to extract the field from the JSON response

vijaysubramania
Path Finder
‎04-13-2020 10:11 PM

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

0 Karma
Reply

to4kawa
Ultra Champion
‎04-28-2020 04:59 PM 
| 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
Reply

tauliang
Communicator
‎04-28-2020 11:23 AM

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.

Reply
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Event Series: Splunk Observability Metrics Cost Optimization

Balancing Scale and Spend: Gaining Control Over High-Volume Metrics in Splunk Observability Cloud As ...

Kick the Tires Before You Commit: A Hands-On Tour of the Splunk Observability Cloud ...

Evaluating an enterprise observability platform usually goes like this: fill out a form, get a free trial with ...

Deep insights, no barriers: Splunk Observability Cloud Free Edition

As software delivery cycles continue to accelerate, observability shouldn’t be a luxury — it should be a ...