How to extract the element from a structure?

[Jouman]

Hi all,

I am in a trouble to extract values from a structure.

Here is the structure of a event:

Event{ 
ID: user_1 
data: { 
   c:[ 
       { 
        Case Name: case_A 
        Start Time: 2023.08.10 13:26:37.867787 
        Stop Time: 2023.08.10 13:29:42.159543
       } 
       { 
        Case Name: case_B 
        Start Time: 2023.08.10 13:29:42.159543 
        Stop Time: 2023.08.10 13:29:48.202143 
        } 
       { 
        Case Name: case_C 
        Start Time: 2023.08.10 13:29:48.202143 
        Stop Time: 2023.08.10 13:29:51.193276 
        } 
      ] 
    } 
 }

I tried to compose a table for lookup as below

ID	case_name	case_start_time	case_stop_time
user_1	case_A	2023.08.10 13:26:37.867787	2023.08.10 13:29:42.159543
user_1	case_B	2023.08.10 13:29:42.159543	2023.08.10 13:29:48.202143
user_1	case_C	2023.08.10 13:29:48.202143	2023.08.10 13:29:51.193276

but I fail to comose as my expectation, I can only compose a table like this:

ID	case_name	case_start_time	case_stop_time
user_1	case_A case_B case_C	2023.08.10 13:26:37.867787 2023.08.10 13:29:42.159543 2023.08.10 13:29:48.202143	2023.08.10 13:29:42.159543 2023.08.10 13:29:48.202143 2023.08.10 13:29:51.193276

Here is my code:

index="my_index"
| rename "data.c{}.Case Name" as case_name, "data.c{}.Start Time" as case_start_time, "data.c{}.Stop Time" as case_stop_time 
| table ID case_name case_start_time case_stop_time

Can anyone help to compose the output table I need? I hope to seperate each case_name with its own case_start_time and case_stop_time.

Thank you so much.

[ITWhisperer · Accepted solution]

It looks like your raw event might be JSON (although the way you have shown it is without double quotes and other punctuation so I am guessing!)

You could try something like this

| spath ID
| spath data.c{} output=c
| mvexpand c
| spath input=c
| fields - c _raw

[Jouman]

Thank you very much!

My issue is resolved so that I can go on the next step.