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!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...