Splunk Search

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

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

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

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

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

New Member

Thanks much, this solved my problem

0 Karma

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

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

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

New Member

my main output needs the accountNumber13, siteId and totalARBalance.

Rest i can update later.

0 Karma