Splunk Search

How to use Foreach for multiple columns and multiple rows?

ChioNeng
Explorer

Hello, I'm having trouble figuring out how to use foreach + eval getting the difference of the fields.

I have something like this: 

have.PNG

 
You can use this search to obtained the above result: 

 

| makeresults
| eval Country="PH"
| eval "2020-01 Actual"=1
| eval "2020-01 Forecast"=2
| eval "2020-02 Actual"=5
| eval "2020-02 Forecast"=4
| eval "2020-03 Actual"=50
| eval "2020-03 Forecast"=20
| append
[| makeresults
| eval Country="IND"
| eval "2020-01 Actual"=3
| eval "2020-01 Forecast"=3
| eval "2020-02 Actual"=2
| eval "2020-02 Forecast"=2
| eval "2020-03 Actual"=40
| eval "2020-03 Forecast"=23
]
| append
[| makeresults
| eval Country="SG"
| eval "2020-01 Actual"=2
| eval "2020-01 Forecast"=4
| eval "2020-02 Actual"=1
| eval "2020-02 Forecast"=9
| eval "2020-03 Actual"=30
| eval "2020-03 Forecast"=53 ]
| fields - _time

 

And I'm trying to use foreach/eval to get this:

want.PNG

 

Thanks in advance 🙂 

0 Karma
1 Solution

renjith_nair
Legend

Try

| makeresults
| eval Country="PH"
| eval "2020-01 Actual"=1
| eval "2020-01 Forecast"=2
| eval "2020-02 Actual"=5
| eval "2020-02 Forecast"=4
| eval "2020-03 Actual"=50
| eval "2020-03 Forecast"=20
| append
[| makeresults
| eval Country="IND"
| eval "2020-01 Actual"=3
| eval "2020-01 Forecast"=3
| eval "2020-02 Actual"=2
| eval "2020-02 Forecast"=2
| eval "2020-03 Actual"=40
| eval "2020-03 Forecast"=23
]
| append
[| makeresults
| eval Country="SG"
| eval "2020-01 Actual"=2
| eval "2020-01 Forecast"=4
| eval "2020-02 Actual"=1
| eval "2020-02 Forecast"=9
| eval "2020-03 Actual"=30
| eval "2020-03 Forecast"=53 ]
| fields - _time
| eval prev=0
| foreach 20* [eval <<FIELD>>_DIFF='<<FIELD>>' - prev|eval prev='<<FIELD>>']
| fields - *Actual_DIFF,prev
| rename "* Forecast_DIFF" as "* Difference"

 

 

---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

renjith_nair
Legend

Try

| makeresults
| eval Country="PH"
| eval "2020-01 Actual"=1
| eval "2020-01 Forecast"=2
| eval "2020-02 Actual"=5
| eval "2020-02 Forecast"=4
| eval "2020-03 Actual"=50
| eval "2020-03 Forecast"=20
| append
[| makeresults
| eval Country="IND"
| eval "2020-01 Actual"=3
| eval "2020-01 Forecast"=3
| eval "2020-02 Actual"=2
| eval "2020-02 Forecast"=2
| eval "2020-03 Actual"=40
| eval "2020-03 Forecast"=23
]
| append
[| makeresults
| eval Country="SG"
| eval "2020-01 Actual"=2
| eval "2020-01 Forecast"=4
| eval "2020-02 Actual"=1
| eval "2020-02 Forecast"=9
| eval "2020-03 Actual"=30
| eval "2020-03 Forecast"=53 ]
| fields - _time
| eval prev=0
| foreach 20* [eval <<FIELD>>_DIFF='<<FIELD>>' - prev|eval prev='<<FIELD>>']
| fields - *Actual_DIFF,prev
| rename "* Forecast_DIFF" as "* Difference"

 

 

---
What goes around comes around. If it helps, hit it with Karma 🙂

ChioNeng
Explorer

@renjith_nair thanks for the answer 🙂 

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...