Extract the fields from below mentioned log in json response

vijaysubramania — Tue, 14 Apr 2020 15:47:33 GMT

Hi,

Need help in extracting the values from the below mentioned tags
divisionID - Value: ABC.202
accountNumber Value: 111122222
accountStatus Value: Active
ppvCreditLimit Value: 0.00
ppvRemainingCreditLimit Value: 0.00

":"{
\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
}

Re: Extract the fields from below mentioned log in json response

DalJeanis — Tue, 14 Apr 2020 17:10:53 GMT

Use the spath command.

Re: Extract the fields from below mentioned log in json response

richgalloway — Tue, 14 Apr 2020 17:12:26 GMT

This should do the job at search time.

... | rex "divisionID\\\"\s:\s\\\"(?<divisionID>[^\\]+)" 
| rex "accountNumber\\\"\s:\s\\\"(?<accountNumber>[^\\]+)"
| rex "accountStatus\\\"\s:\s\\\"(?<accountStatus>[^\\]+)"
| rex "ppvCreditLimit\\\"\s:\s(?<ppvCreditLimit>[^\\]+)"
| rex "ppvRemainingCreditLimit\\\"\s:\s(?<ppvRemainingCreditLimit>[^\\]+)"

Re: Extract the fields from below mentioned log in json response

vijaysubramania — Tue, 14 Apr 2020 21:14:26 GMT

Thanks Rich. I tried your query but giving the below mentioned error for all search
| rex "divisionID\\"\s:\s\\"(?[^\]+)"
| rex "accountNumber\\"\s:\s\\"(?[^\]+)"
| rex "accountStatus\\"\s:\s\\"(?[^\]+)"
| rex "ppvCreditLimit\\"\s:\s(?[^\]+)"
| rex "ppvRemainingCreditLimit\\"\s:\s(?[^\]+)" | table ppvCreditLimit ppvRemainingCreditLimit divisionID accountNumber accountStatus

Error in 'rex' command: Encountered the following error while compiling the regex 'divisionID\"\s:\s\"(?[^]+)': Regex: missing terminating ] for character class.

Could not load lookup=lookup_severity

Re: Extract the fields from below mentioned log in json response

vijaysubramania — Tue, 14 Apr 2020 21:16:19 GMT

Thanks. Tried the option but getting the below response for all search

Error in 'rex' command: Encountered the following error while compiling the regex 'divisionID\"\s:\s\"(?[^]+)': Regex: missing terminating ] for character class.

Could not load lookup=lookup_severity

Re: Extract the fields from below mentioned log in json response

richgalloway — Tue, 14 Apr 2020 23:28:19 GMT

You're probably missing some \ . Try \\\\" in place of each \\\".

Re: Extract the fields from below mentioned log in json response

vijaysubramania — Wed, 15 Apr 2020 15:59:45 GMT

Will check and let you know. Thank you.

topic Extract the fields from below mentioned log in json response in Splunk Search

Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response

Re: Extract the fields from below mentioned log in json response