Splunk Search

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

yograjpatel
New Member

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

0 Karma
1 Solution

cpetterborg
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

hardikJsheth
Motivator

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
0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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.

0 Karma

yograjpatel
New Member

Thanks much, this solved my problem

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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!

0 Karma

yograjpatel
New Member

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.

0 Karma

cpetterborg
SplunkTrust
SplunkTrust

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?

0 Karma

yograjpatel
New Member

my main output needs the accountNumber13, siteId and totalARBalance.

Rest i can update later.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...