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 🙂 

Labels (1)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

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"

 

 

Happy Splunking!

View solution in original post

renjith_nair
SplunkTrust
SplunkTrust

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"

 

 

Happy Splunking!

ChioNeng
Explorer

@renjith_nair thanks for the answer 🙂 

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...