Splunk Search

How to create table from JSON?

Khanu89
Path Finder

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.

Screen Shot 2022-05-19 at 10.06.55 PM.png

 

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

Labels (3)
0 Karma

Khanu89
Path Finder

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

Screen Shot 2022-05-20 at 12.13.28 AM.png

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

Khanu89
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

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

Khanu89
Path Finder

I tried and it didn't extract any fields.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Khanu89
Path Finder

@ITWhisperer  hope this helps.

Screen Shot 2022-05-20 at 12.27.06 AM.png

 

Here is my JSON file 

Screen Shot 2022-05-20 at 12.30.06 AM.png

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Security Professional: Sharpen Your Defenses with These .conf25 Sessions

Sooooooooooo, guess what. .conf25 is almost here, and if you're on the Security Learning Path, this is your ...

First Steps with Splunk SOAR

Our first step was to gather a list of the playbooks we wanted and to sort them by priority.  Once this list ...

How To Build a Self-Service Observability Practice with Splunk Observability Cloud

If you’ve read our previous post on self-service observability, you already know what it is and why it ...