How to extract value with dynamic key name

[shimada-k]

Hi Experts,

I have the following data. 

{
"TIMESTAMP": 1742677200,
"SYSINFO": "{\"number_of_notconnect_interfaces\":0,\"hostname\":\"test\",\"number_of_transceivers\":{\"10G-LR\":10,\"100G-CWDM4\":20},\"number_of_bfd_peers\":10,\"number_of_bgp_peers\":10,\"number_of_disabled_interfaces\":10,\"number_of_subinterfaces\":{\"Ethernet1\":10,\"Ethernet2\":20},\"number_of_up_interfaces\":1}"
}

I would like to create the table as below, but Ethernet1 or Ethernet2 is a dynamic key (it can be like Ethernet3 or Ethernet4).

Ethernet1	10
Ethernet2	20

Could someone tell me how to write query to achieve this?

[ITWhisperer · Accepted solution]

| makeresults | eval json="{\"TIMESTAMP\": 1742677200,\"SYSINFO\": \"{\\\"number_of_notconnect_interfaces\\\":0,\\\"hostname\\\":\\\"test\\\",\\\"number_of_transceivers\\\":{\\\"10G-LR\\\":10,\\\"100G-CWDM4\\\":20},\\\"number_of_bfd_peers\\\":10,\\\"number_of_bgp_peers\\\":10,\\\"number_of_disabled_interfaces\\\":10,\\\"number_of_subinterfaces\\\":{\\\"Ethernet1\\\":10,\\\"Ethernet2\\\":20},\\\"number_of_up_interfaces\\\":1}\"}"
| spath input=json
| spath input=SYSINFO number_of_subinterfaces
| fields number_of_subinterfaces
| eval keys=json_array_to_mv(json_keys(number_of_subinterfaces),false())
| mvexpand keys
| eval value=json_extract(number_of_subinterfaces,keys)
| fields - _time number_of_subinterfaces

[gargantua]

Or even that :

| makeresults | eval json="{\"TIMESTAMP\": 1742677200,\"SYSINFO\": \"{\\\"number_of_notconnect_interfaces\\\":0,\\\"hostname\\\":\\\"test\\\",\\\"number_of_transceivers\\\":{\\\"10G-LR\\\":10,\\\"100G-CWDM4\\\":20},\\\"number_of_bfd_peers\\\":10,\\\"number_of_bgp_peers\\\":10,\\\"number_of_disabled_interfaces\\\":10,\\\"number_of_subinterfaces\\\":{\\\"Ethernet1\\\":10,\\\"Ethernet2\\\":20},\\\"number_of_up_interfaces\\\":1}\"}"

|fromjson json
|fromjson SYSINFO
|fields number_of_subinterfaces
|fromjson number_of_subinterfaces
|fields - number_of_subinterfaces _time
|transpose header_field=column
|rename "row 1" as value, column as keys

🙂

[shimada-k]

Thank you very much, ITWhispere. That works.

[gargantua]

Is this doing what you're trying to do ?

| makeresults | eval json="{\"TIMESTAMP\": 1742677200,\"SYSINFO\": \"{\\\"number_of_notconnect_interfaces\\\":0,\\\"hostname\\\":\\\"test\\\",\\\"number_of_transceivers\\\":{\\\"10G-LR\\\":10,\\\"100G-CWDM4\\\":20},\\\"number_of_bfd_peers\\\":10,\\\"number_of_bgp_peers\\\":10,\\\"number_of_disabled_interfaces\\\":10,\\\"number_of_subinterfaces\\\":{\\\"Ethernet1\\\":10,\\\"Ethernet2\\\":20},\\\"number_of_up_interfaces\\\":1}\"}"

|fromjson json
|fromjson SYSINFO
|fields number_of_subinterfaces
|fromjson number_of_subinterfaces
|fields - number_of_subinterfaces _time

Results :

Ethernet1	10
Ethernet2	20

[gargantua]

Is your data is exactly like this samble, with all those :

\"

??

[catdadof3]

Just to clarify - This how the data is coming into your index/source and you're trying to manipulate it via SPL?

It looks something like this only an event vs a table?

Just checking because I can't tell if this is one long string or an event that has multiple attributes. If it's an event, could you possibly take a screen shot?

It looks like you'd have something where SYSINFO is the top level and each category below is another level. If that's the case, ideally you'd just have SYSINFO.number_of_subinterfaces as a field name you can table.