topic Re: Parse nested JSON where the object names are different in Getting Data In

Parse nested JSON where the object names are different

randy_moore — Wed, 22 Apr 2020 13:38:08 GMT

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": "testuser11@test.com",
      "IP_Address": "127.0.0.1",
      "Action": "Log in",
      "Data": "12.34.56.78"
    },
    "Event2": {
      "Time": "2020-04-21 11:26:41",
      "Username": "testuser2@test.com",
      "IP_Address": "127.0.0.1",
      "Action": "Log in",
      "Data": "23.45.67.89"
    },
    "Event3": {
      "Time": "2020-04-21 11:25:37",
      "Username": "testuser3@test.com",
      "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.

Re: Parse nested JSON where the object names are different

darrenfuller — Wed, 22 Apr 2020 15:24:04 GMT

What about something like this:

| makeresults 
| eval testdata="{
   \"status\": \"OK\",
   \"next\": null,
   \"data\": {
     \"Event1\": {
       \"Time\": \"2020-04-21 11:28:22\",
       \"Username\": \"testuser11@test.com\",
       \"IP_Address\": \"127.0.0.1\",
       \"Action\": \"Log in\",
       \"Data\": \"12.34.56.78\"
         },
     \"Event2\": {
       \"Time\": \"2020-04-21 11:26:41\",
       \"Username\": \"testuser2@test.com\",
       \"IP_Address\": \"127.0.0.1\",
       \"Action\": \"Log in\",
       \"Data\": \"23.45.67.89\"
         },
     \"Event3\": {
       \"Time\": \"2020-04-21 11:25:37\",
       \"Username\": \"testuser3@test.com\",
       \"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 .

Re: Parse nested JSON where the object names are different

randy_moore — Wed, 22 Apr 2020 21:28:40 GMT

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

Re: Parse nested JSON where the object names are different

to4kawa — Wed, 22 Apr 2020 21:56:23 GMT

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