How to extract events from multi-level json?

[mgallacher]

Please believe me 🙂 that I have searched for an answer until my index finger bled (pun intended, but seriously...I have searched).

I have the JSON (below) already indexed. (Re-indexing is a very last resort.) The fields appear as:

testId, results{}.*, and results{}.steps{}.\

I need one event per result with the following fields:

 testId, runId, startTime, result, name

The runId value is unique within results{}. The name value is unique within results{}.steps{} (and the names are repeated with each run, ie "CASE1", ..., "CASE9")

I understand the basics of mvzip and mvexpand. I'm open to a different approach if possible because (1) I can't get it to work with multiple levels and (2) there are actually 14 other fields not shown below, which are spread through each level.

Is using crazy long mvzip and eval commands my only option? If so, how does it work with multiple levels?

Thanks!

---

Sample JSON

{
  "testId": "perfScenario1",
  "results": [
    {
      "runId": "GUID1",
      "steps": [
        {
          "startTime": "2018-01-20T00:00:00.000Z",
          "result": "1.3",
          "name": "CASE1"
        },
        {
          "startTime": "2018-01-20T00:02:00.000Z",
          "result": "0.2",
          "name": "CASE2"
        },
        ...
      ]
    },
    {
      "runId": "GUID2",
      "steps": [
        {
          "startTime": "2018-01-20T06:00:00.000Z",
          "result": "1.3",
          "name": "CASE1"
        },
        {
          "startTime": "2018-01-20T06:00:00.000Z",
          "result": "0.2",
          "name": "CASE2"
        },
        ...
      ]
    },
    ...
  ]
}

[maciep]

I'm not sure I have a better approach for you, but I think I have something at least. I wonder if spath can do more than I know how to use it, but might be worth looking into.

I know when I ingest json like this, I try to put each result in its own event, but that may not be an option or may not make sense with this data.

That said, I'm trying to put them in their own event with spl instead. I use sed to create a delimiter (|) where I want it, and then split on the delimiter. That will get me an array of runs. Making sure I have the testId field extracted as well, i can then use mvexpand to put each run in its own event.

Then I can use spath to pull that run apart, zip all of the step data together in one array and expand one more time. A simple rex to separate the step data, and then table everything together.

Not sure if this will work for you, especially if you have a ton more data to tie together, but maybe it least give you some ideas.

source="test.json" sourcetype="_json"
| rex "testId\":\s*\"(?<testId>[^\"]+)"
| rex mode=sed "s/(\{[^\"]*\"runId)/|\1/g"
| eval runs=split(_raw,"|")
| eval runs=mvindex(runs,1,mvcount(runs)-1)
| mvexpand runs
| fields testId, runs
| spath input=runs
| rename steps{}.* as *
| eval myevent = mvzip(name,startTime), myevent=mvzip(myevent,result)
| fields testId, runId, myevent
| mvexpand myevent
| rex field=myevent "^(?<name>[^,]+),(?<startTime>[^,]+),(?<result>.+)"
| table testId, runId, name, startTime,result