Parse nested JSON where the object names are different

[randy_moore]

I have this application log that is made up of nested JSON

{
  "status": "OK",
  "next": null,
  "data": {
    "Event1": {
      "Time": "2020-04-21 11:28:22",
      "Username": "[email protected]",
      "IP_Address": "127.0.0.1",
      "Action": "Log in",
      "Data": "12.34.56.78"
    },
    "Event2": {
      "Time": "2020-04-21 11:26:41",
      "Username": "[email protected]",
      "IP_Address": "127.0.0.1",
      "Action": "Log in",
      "Data": "23.45.67.89"
    },
    "Event3": {
      "Time": "2020-04-21 11:25:37",
      "Username": "[email protected]",
      "IP_Address": "127.0.0.1",
      "Action": "Log in",
      "Data": "34.123.56.78"
    }
  }
}

I want to try to search on Username (or really any of the fields under the "Event x" key field).
My issue is that the application increments the Event number so I can't do a normal spath path=data.Event{}.Username statement.

I am not skilled enough in the ways of spath or the rename|mvzip|mvexpand|mvindex combonations to figure this out. I feel the answer is to do something like that, but that is just from reading all of the other Answers that had JSON parsing issues. Please help.

[darrenfuller · Accepted solution]

What about something like this:

| makeresults 
| eval testdata="{
   \"status\": \"OK\",
   \"next\": null,
   \"data\": {
     \"Event1\": {
       \"Time\": \"2020-04-21 11:28:22\",
       \"Username\": \"[email protected]\",
       \"IP_Address\": \"127.0.0.1\",
       \"Action\": \"Log in\",
       \"Data\": \"12.34.56.78\"
         },
     \"Event2\": {
       \"Time\": \"2020-04-21 11:26:41\",
       \"Username\": \"[email protected]\",
       \"IP_Address\": \"127.0.0.1\",
       \"Action\": \"Log in\",
       \"Data\": \"23.45.67.89\"
         },
     \"Event3\": {
       \"Time\": \"2020-04-21 11:25:37\",
       \"Username\": \"[email protected]\",
       \"IP_Address\": \"127.0.0.1\",
       \"Action\": \"Log in\",
       \"Data\": \"34.123.56.78\"
         }
   }
 }" 
| rename testdata AS _raw 
| rex field=_raw "\"Event(?<eventcount>\d+)\"\:\s\{[^\}]+\}\s+\}\s+\}" 
| rex field=_raw max_match=0 "\"Event(\d+)\"\:\s(?<events>\{[^\}]+\})" 
| spath 
| table _raw events eventcount status next 
| mvexpand events
| spath input=events

Which puts each event on its own to enumerate by itself .

[randy_moore]

Thanks @darrenfuller ! That was a great help. I had to modify the rex a little bit to account for my exact specific data. That gave me the opportunity to dive into regex and figure out what you wrote. Once I did that, I was able to get it working just like your example.

Thanks again!

Randy

[to4kawa]

...
| spath path=data{} output=data
| spath path=status
| mvexpand data
| eval data=replace(data, "Event\d", "Events")
| spath input=data
| rename Events{}.* as *
| fields - Events

same way