topic Re: How to create table from JSON? in Splunk Search

How to create table from JSON?

Khanu89 — Fri, 20 May 2022 04:25:27 GMT

Hello - Thank you in advance for the help. I am getting following raw data in Splunk events which I'd like to pull into a table format.

I would like to pull the following: Host, Success, and Error field as columns for my table.

I tried this query but no success:

| makeresults
| eval _raw="{\"host\"},{\"success\",{\"error\"}"
| spath path=host{} output=temp
| mvexpand temp
| spath input=temp
| fillnull value="None"
| table host,success,error

Re: How to create table from JSON?

ITWhisperer — Fri, 20 May 2022 05:12:17 GMT

It looks like (from the graphic) that host, success and error are elements of an object, which is part of a collection of similar objects. Is this right? Can you share a larger example of the whole event in a code block rather than a graphic (redacting any sensitive information of course)?

Re: How to create table from JSON?

Khanu89 — Fri, 20 May 2022 05:17:51 GMT

@ITWhisperer Here you are. So my code is pinging remote machines and the response is in JSON file. I would like to table the host and success from this response file.

Re: How to create table from JSON?

ITWhisperer — Fri, 20 May 2022 05:20:06 GMT

It looks like from your graphic that the JSON has been split across multiple events - is that right?

If so, will host, success and error always be in the same event?

Re: How to create table from JSON?

Khanu89 — Fri, 20 May 2022 05:26:34 GMT

yeah I do not know why they are split up in multiple events but the host, success and error will be in same event.

Re: How to create table from JSON?

Khanu89 — Fri, 20 May 2022 05:32:04 GMT

@ITWhisperer hope this helps.

Here is my JSON file

Re: How to create table from JSON?

ITWhisperer — Fri, 20 May 2022 05:33:29 GMT

Try something like this

| rex "\"host\":\s(?<host>[^,]*),.*?\"success\":\s(?<success>[^,]*),.*?\"error\":\s(?<error>[^,]*),"

Re: How to create table from JSON?

Khanu89 — Fri, 20 May 2022 05:36:30 GMT

I tried and it didn't extract any fields.

Re: How to create table from JSON?

ITWhisperer — Fri, 20 May 2022 05:39:10 GMT

It looks like your file is being broken into events by the presence of timestamps rather than the end of the array element

Re: How to create table from JSON?

ITWhisperer — Fri, 20 May 2022 05:42:26 GMT

Try this

| rex "(?ms)\"host\":\s(?<host>[^,]*),.*?\"success\":\s(?<success>[^,]*),.*?\"error\":\s(?<error>[^,]*),"

I haven't tested this because I don't have an example to use (which is why I asked for the event to be in a code block, and I am not about to type it all in) 😀