How to extract the fields from JSON output and display as table

[yograjpatel]

{
"ERROR_CODE" : "XXX-XXX-00000",
"ERROR_DESC" : "Success."
},
"accountBalances" : {
"accountNumber13" : "22222222222",
"siteId" : "200001005",
"siteCode" : "HRD",
"customerName" : "LiXX XXXXXX",
"serviceAddress" : "XXXXXXXXXX, VA XXXXX-4849 ",
"streetNumber" : "XXX",
"streetName" : "XXXXXX",
"city" : "CHESAPEAKE",
"state" : "VA",
"zip5" : "23320",
"homeTelephoneNumber" : "XXX 000-0000",
"acceptChecks" : "True",
"acceptCreditCards" : "True",
"pendingWODepositAmount" : "0.0",
"statementInfo" : [ {
"statementCode" : 1,
"currentBalance" : "0.0",
"serviceCategories" : [ "INTERNET", "CABLE", "TELEPHONE" ],
"amountBilled" : "577.71",
"minimumDue" : "270.6",
"billDay" : "8",
"statementDueDate" : "20171029",
"totalARBalance" : "577.71",
"ar1To30" : "307.11",
"ar31To60" : "198.89",
"ar61To90" : "71.71",
"ar91To120" : "0.0",
"ar121To150" : "0.0",
"arOver150Days" : "0.0",
"writeOffAmount" : "0.0",
"totalUnappliedPayment" : "0.0",
"totalUnappliedAdjustment" : "0.0",
"depositDue" : "0.0",
"depositPaid" : "0.0",
"depositInterest" : "0.0",
"totalMonthlyRate" : "174.23",
"lastStatementDate" : "20171009"
} ]
}

[hardikJsheth]

In case you get an access to props.conf /transforms.conf, following configurations can help you in extracting all the fields within your JSON string .

Props.conf
[yoursource type]
REPORT-json = report-json

Transforms.conf
[report-json]
REGEX = "(\w+)":["}]*([\w\-\:\+\/\.\@]*)
FORMAT = $1::$2

[cpetterborg · Accepted solution]

Given the information in the comments above, try:

... | rex "\"accountNumber13\"\s:\s\"(?P<accountNumber13>\d+)\"[\s\S]*\"siteId\"\s:\s\"(?P<siteId>\d+)\"[\s\S]*\"totalARBalance\"\s:\s\"(?P<totalARBalance>[\d\.]+)\""

It can also be a basis for your future modifications. This worked on the data using regex101.com, so it should work in the rex command as I have provided. If you need it to run faster, there is a version that I did that needs the data to be pretty similar to the data you provided, but it only shaves of 20% of the steps, so it probably isn't worth it to make that modification.

[yograjpatel]

Thanks much, this solved my problem

[cpetterborg]

What do you see in Splunk for this type of data already? If the data was on-boarded correctly, you should already have the fields extracted and you can do the table easily enough. So if you can give us an ides of the data that you are currently seeing, then we can probably help you. If you don't have access to fixing the data problem (you aren't a Splunk admin), then we can help with that, too, but please give us some more information.

Thanks!

[yograjpatel]

Hello,
We dont see the data extracted and I dont have admin access. So looking to extract data using spath which i'm not successful.

[cpetterborg]

Yeah, path won't work if it isn't already extracted.

Do you care about the hierarchy of the data? That makes it a bit more difficult.

And is this a complete list of the fields?

And do you need all the fields extracted, or just some of them?

[yograjpatel]

my main output needs the accountNumber13, siteId and totalARBalance.

Rest i can update later.