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!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...