I have a json like this format
{
"id":"123412341234",
"actions": [
{
"type":"a",
"status":"b",
"amount": 1,
"time_updated": "2013-10-14T11:00"
},
{
"type":"c",
"status":"d",
"amount": 1,
"time_updated": "2013-10-14T12:00"
}
]
}
I want to know how to split the array into multiple new events like
time_updated id type status amount
2013-10-14T11:00 123412341234 a b 1
2013-10-14T12:00 123412341234 c d 2
Thanks!
Trying with a different approach to making search easy. Just using the required portion of JSON content in spath
, kv
and rocking.
| makeresults
| eval _raw="{
\"id\":\"123412341234\",
\"actions\": [
{
\"type\":\"a\",
\"status\":\"b\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T11:00\"
},
{
\"type\":\"c\",
\"status\":\"d\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T12:00\"
}
]
}" | spath
| spath path=actions{} output=actions | mvexpand actions | eval _raw=actions | kv | table time_updated, id, type, status, amount
UPDATED: Replacing mvexpand
with stats
command.
| makeresults
| eval _raw="{
\"id\":\"123412341234\",
\"actions\": [
{
\"type\":\"a\",
\"status\":\"b\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T11:00\"
},
{
\"type\":\"c\",
\"status\":\"d\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T12:00\"
}
]
}" | spath
| spath path=actions{} output=actions | stats count by id,actions | eval _raw=actions | kv | table time_updated, id, type, status, amount
Thanks
This was a fantastic solution all around, can I ask how you would get the stats version of this to work in a props & transforms?
Although these two different approaches yield the same results; the underlying mechanism is different that using "stats" could push too much data to the search head(s) and results in an auto finalized search due to search disk quota.
mvexpand is a distributed streaming command (done at indexing layer) whereas stats is transforming commands (done at search head layer).
https://docs.splunk.com/Documentation/Splunk/8.1.2/Search/Typesofcommands
best solution on this page. thanks!
Thanks @twinspop 🙂
The answers here were not helping me. The expanded examples in the spath doc were helpful, but as an exercise I wanted to work through this. SO came up with this that seems to be what you want:
| makeresults
| eval _raw="{
\"id\":\"123412341234\",
\"actions\": [
{
\"type\":\"a\",
\"status\":\"b\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T11:00\"
},
{
\"type\":\"c\",
\"status\":\"d\",
\"amount\": 1,
\"time_updated\": \"2013-10-14T12:00\"
}
]
}"
| spath
| rename "actions{}.type" AS type
| rename "actions{}.status" AS status
| rename "actions{}.time_updated" AS time_updated
| rename "actions{}.amount" AS amount
| eval zipped=mvzip( time_updated, mvzip( type, mvzip( status, amount ) ))
| mvexpand zipped
| eval zipped=split( zipped, "," )
| eval time_updated=mvindex( zipped, 0 )
| eval type=mvindex( zipped, 1 )
| eval status=mvindex( zipped, 2 )
| eval amount=mvindex( zipped, 3 )
| table time_updated, id, type, status, amount
Cheers.
Awesome, precisely what I needed!
how about the dynamic field name?
such as :
{"Troj/DocDl-QUA": 4, "CXmail/OleDl-AU": 44, "CXmail/EncDoc-B": 6, "Troj/DocDl-QVV": 10, "Troj/DocDl-QVQ": 10, "Troj/DocDl-QWV": 7, "CXmail/OleDl-AD": 341, "Troj/DocDl-QWN": 25, "Troj/DocDl-QVF": 4, "Troj/DocDl-QVC": 8}
{"Troj/DocDl-QUA": 4, "CXmail/OleDl-AU": 591, "Troj/DocDl-QOQ": 10, "Troj/DocDl-QUE": 9, "Troj/RtfExp-EQ": 1, "Troj/DocDl-QTD": 5, "Troj/DocDl-QUI": 21, "CXmail/OleDl-AD": 868, "CXmail/OleDl-V": 9, "Troj/PDFDown-X": 4}
I want to transfer it like:
Troj/DocDl-QUA 4
CXmail/OleDl-AU 44
CXmail/EncDoc-B 6
Troj/DocDl-QVV 10
Troj/DocDl-QVQ 10
Troj/DocDl-QWV 7
CXmail/OleDl-AD 341
Troj/DocDl-QWN 25
Troj/DocDl-QVF 4
Troj/DocDl-QVC 8
Troj/DocDl-QUA 4
CXmail/OleDl-AU 591
Troj/DocDl-QOQ 10
Troj/DocDl-QUE 9
Troj/RtfExp-EQ 1
Troj/DocDl-QTD 5
Troj/DocDl-QUI 21
CXmail/OleDl-AD 868
CXmail/OleDl-V 9
Troj/PDFDown-X 4
so I can sum the same virus type and draw chart. Thanks!
Hi wood1986,
have a look at the spath search command, its purpose is to have a straightforward means for extracting information from structured data formats like XML and JSON.
hope this helps ...
cheers, MuS
I downvoted this post because a link to documentation does not answer the question. including an example would be more helpful.
Maybe this example http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath#Basic_examples or this http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath#Extended_examples is more suitable as example?
Can someone at least confirm whether this is possible or not? (The question was: how to turn a single JSON event with an array of N sub-items into N events, during parsing, not at search time.)
yes it works as documented. You really have to read the documentation; and it works
naisanza, were you referring to the use of spath works? Or, do you have hints/comment/method on what I need to do?
You can do similar thing for JSON arrays as well using spath.
your base search | spath | rename actions{}.* as * | |mvexpand id | dedup id | eval temp=mvzip(mvzip(mvzip(type,status,"#"),time_updated,"#"),amount,"#") | mvexpand temp | rex field=temp "(?<type>.*)#(?<status>.*)#(?<time_updated>.*)#(?<amount>.*)" | fields - temp | dedup id amount status time_updated type
It works.. but strange.. we need to copy as it is...
if we give any spaces, its not working...
This example does not address the question. The example describes how to turn an event that has a field with multiple values into multiple events. It does not describe how to turn an event with a JSON array into multiple events. The difference is this:
{ var : val1, var : val2, var : val3 }
vs this
var : [val1, val2, val3].
The example covers the first, the question concerns the second. Does anyone know how to turn a single JSON event with an array of N sub-items into N events, each
with one sub-item?
examples you want....so you probably did not read the docs then 😉 there are examples on how to use spath on XML and JSON -> http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Spath#Examples
examples will pretty nice
Note, you might have to use spath to get multi-value fields, then mvexpand to get events from each distinct set.