Splunk Search

How to extract value with dynamic key name

shimada-k
Explorer

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

Ethernet110
Ethernet220

 

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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| 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

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| 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
Path Finder

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

🙂

 

0 Karma

shimada-k
Explorer

Thank you very much, ITWhispere. That works.

0 Karma

gargantua
Path Finder

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 :

Ethernet110
Ethernet220

 

0 Karma

gargantua
Path Finder

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

\"

??

0 Karma

catdadof3
Explorer

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?

catdadof3_0-1742684754431.png

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.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...