Splunk Search

How to use foreach to extract value from another json object?

GaryZ
Path Finder

 

I am trying to get the values from one json object using the keys from another json array.  

 

| makeresults
| eval limits=json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), total=0
| eval processes = json_array("process1", "process2")
| eval new_data_limits = json_object()
| foreach processes
[ | eval new_data_limits = json_set(new_data_limits, <<FIELD>>, json_extract(limits, <<FIELD>>))]

 

1) How do I capture the limits into the new_data_limits array?

2) If there's multiple events similar to 'limits', how do I get the average of similar process? (i.e "process1", "process2")

 

TIA....

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

By default, foreach uses multifield mode.  What you need is its json_array mode.  (If you have Splunk 9, that is.  Before Splunk 9, foreach only has multifield mode.)

 

| makeresults
| eval limits=json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), total=0
| eval processes = json_array("process1", "process2")
| eval new_data_limits = json_object()
| foreach processes mode=json_array
[ | eval new_data_limits = json_set(new_data_limits, <<ITEM>>, json_extract(limits, <<ITEM>>))]

 

To get average, on the other hand, I would lose that JSON array for processes because you need separate columns.

| makeresults
| eval limits=mvappend(json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)),
json_object("process1", json_array(345), "process2", json_array(678), "process3", json_array(0.12))), total=0
| mvexpand limits
```data emulation above```
| foreach process1 process2
[ eval new_<<FIELD>>_limit = json_array_to_mv(json_extract(limits, "<<FIELD>>"))]
| stats sum(total) as total avg(*) as *
0 Karma

GaryZ
Path Finder

@abi2023 

 

I understand your code, and I believe that it should work.  however when I run the search, I get the following.

I don't see new_data_limits capturing the limit values.  I've tried rerunning the search, and have also stepped through it without the foreach loop.  I do see the results.  However when it's used in the foreach loop, the limits values aren't in the new_data_limits variable. 

GaryZ_1-1683068282273.png

 

0 Karma

abi2023
Path Finder

| makeresults
| eval limits=json_object("process1", json_array(123), "process2", json_array(234), "process3", json_array(0.12)), total=0
| eval processes = json_array("process1", "process2")
| eval new_data_limits = json_object()
| foreach processes
[ | eval key = tostring(<<FIELD>>), value = json_extract(limits, key), new_data_limits = if(isnull(new_data_limits), json_object(key, value), json_set(new_data_limits, key, value)) ]

get average 

base search | stats avg(*) as * by process1, process2

 

0 Karma
Get Updates on the Splunk Community!

SplunkTrust Application Period is Officially OPEN!

It's that time, folks! The application/nomination period for the 2025 SplunkTrust is officially open! If you ...

Splunk Answers Content Calendar, June Edition II

Get ready to dive into Splunk Dashboard panels this week! We'll be tackling common questions around ...

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...