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):
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.
@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 _raw
are 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)
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.
@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 _raw
are 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)
Try something like this
|spath input=_raw | stats values(*) as by error(which is unique identifier)
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