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