Getting Data In

how to split the json array into multiple new events

wood1986
Explorer

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!

Tags (3)

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

dmacgill
Loves-to-Learn Lots

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? 

0 Karma

jenipherc
Splunk Employee
Splunk Employee

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

 

0 Karma

twinspop
Influencer

best solution on this page. thanks!

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Thanks @twinspop 🙂

0 Karma

tdepuy
Path Finder

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.

piotrjustyna
Engager

Awesome, precisely what I needed!

0 Karma

haoban
Path Finder

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!

0 Karma

MuS
SplunkTrust
SplunkTrust

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

TonyLeeVT
Builder

I downvoted this post because a link to documentation does not answer the question. including an example would be more helpful.

0 Karma

MuS
SplunkTrust
SplunkTrust
0 Karma

madstop99
Explorer

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

0 Karma

naisanza
Path Finder

yes it works as documented. You really have to read the documentation; and it works

0 Karma

madstop99
Explorer

naisanza, were you referring to the use of spath works? Or, do you have hints/comment/method on what I need to do?

0 Karma

somesoni2
Revered Legend

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
0 Karma

skota524
Explorer

It works.. but strange.. we need to copy as it is...
if we give any spaces, its not working...

0 Karma

prokopowicz
Explorer

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?

MuS
SplunkTrust
SplunkTrust

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

0 Karma

tcoq
Path Finder

examples will pretty nice

sowings
Splunk Employee
Splunk Employee

Note, you might have to use spath to get multi-value fields, then mvexpand to get events from each distinct set.

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!