How to create table from JSON?

[Khanu89]

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

[Khanu89]

@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.

[ITWhisperer]

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?

[Khanu89]

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

[ITWhisperer]

Try something like this

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

[Khanu89]

I tried and it didn't extract any fields.

[ITWhisperer]

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) 😀

[ITWhisperer]

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)?

[Khanu89]

@ITWhisperer  hope this helps.

Here is my JSON file 

[ITWhisperer]

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