topic Re: How to extract the fields from JSON output and display as table in Splunk Search

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

yograjpatel — Tue, 29 Sep 2020 16:14:27 GMT

{
"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"
} ]
}

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

cpetterborg — Thu, 12 Oct 2017 18:36:53 GMT

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!

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

yograjpatel — Thu, 12 Oct 2017 18:38:53 GMT

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.

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

cpetterborg — Thu, 12 Oct 2017 18:44:39 GMT

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?

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

yograjpatel — Thu, 12 Oct 2017 20:00:45 GMT

my main output needs the accountNumber13, siteId and totalARBalance.

Rest i can update later.

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

cpetterborg — Fri, 13 Oct 2017 00:13:24 GMT

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.

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

hardikJsheth — Fri, 13 Oct 2017 06:03:53 GMT

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

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

yograjpatel — Fri, 13 Oct 2017 13:52:51 GMT

Thanks much, this solved my problem