Splunk Search

How to create table from JSON?

Khanu89
Explorer

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
Explorer

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

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
Explorer

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
Explorer

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

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...