How can I turn this JSON event into a table with various fields?

[hsingams2]

Hello, a beginner question. I've a search query that produces a single JSON event such as this:
{
Error/type/0 : type_value0
Error/type/1 : type_value1
Error/type/2 : type_value2
Error/desc/0 : desc_value0
Error/desc/1 : desc_value1
Error/desc/2 : desc_value2
Error/logfile/0 : file_value0
Error/logfile/1 : file_value1
Error/logfile/2 : file_value2
}

I want to transform this into a table like this (end output):

type desc logfile

0 type_value0 desc_value0 file_value0
1 type_value1 desc_value1 file_value1
2 type_value2 desc_value2 file_value2

I'm thinking splitting the input JSON event into multiple small events could help me get to the end result. Or may be there is a better way.

How do I achieve this?

Thanks in advance for your help.

[lfedak_splunk]

Hey @hsingams2, glad you were able to solve your question. If the other users helped make your search more efficient, please "√Accept" their answer to award karma points 🙂 You can also upvote their comments.

[niketn · Accepted solution]

@hsingams2 if the events will have type, desc and logfiles in the same sequence, then you can try the following search:
PS: first two pipes below i.e. makeresults and eval _raware used to mock data as per the question:

| makeresults
| eval _raw="{
Error/type/0 : type_value0
Error/type/1 : type_value1
Error/type/2 : type_value2
Error/desc/0 : desc_value0
Error/desc/1 : desc_value1
Error/desc/2 : desc_value2
Error/logfile/0 : file_value0
Error/logfile/1 : file_value1
Error/logfile/2 : file_value2
Error/type/3 : type_value3
Error/desc/3 : desc_value3
Error/logfile/3 : file_value3
}"
| rex max_match=0 field=_raw "Error/(?<key>\w+)/(?<num>\d+)\s\:\s(?<value>.+)"
| eval num_value=mvzip(num,value)
| mvexpand num_value
| eval num_value=split(num_value,",")
| eval num=mvindex(num_value,0)
| eval value=mvindex(num_value,1)
| table num value
| stats list(value) as value by num
| eval type=mvindex(value,0)
| eval desc=mvindex(value,1)
| eval logfile=mvindex(value,2)
| fields - value

On a similar lines rex and mvzip commands can be replaced with eval with replace and makemv commands.

| eval num_value=replace(replace(replace(jsonData,"Error/\w+/(\d+)\s\:\s(.+)","\1,\2;"),"{",""),"}","")
| makemv delim=";" num_value

This would require one additional pipe in the end after fields - value i.e.

| fields - value
| where isnotnull(type)

[naidusadanala]

Try something like this

|spath input=_raw | stats values(*) as by error(which is unique identifier)

[hsingams2]

Ok, after a bit of trial and error, I was able to solve this.

However, I am not sure if it's an efficient way. I'm hoping splunk experts on this group weigh in on the solution.

... | rex field=_raw "Error/type/(?\d+)\s*:\s*(?\S+)" max_match=100
| rex field=_raw "Error/desc/\d+\s*:\s*(?\S+)" max_match=100

| rex field=_raw "Error/logfile/\d+\s*:\s*(?\S+)" max_match=100

| eval fields = mvzip(mvzip(mvzip(num,type_value),desc_value),log_value)
| mvexpand fields
| rex field=fields "(?\S+),(?\S+),(?\S+),(?\S+)"
| table _time num type desc logfile