Splunk Enterprise

foreach problem, sum of several fields conditioned to the name of the fields

antonio147
Communicator

Hi all,
I have a problem that I cannot solve.
I have data that is a result of a loadjob where the fields are named 0_PREVIOUS_MONTH, 1_PREVIOUS_MONTH, 2_PREVIOUS_MONTH, ..... 12_PREVIOUS_MONTH.
I would like to add the values of the fields starting from 1/4 up to the current month ..
Let me explain with an example:
today we are in November, I need the sum of a line that starts from April 1st until today.
So if I do the current month -4 + 1 = 8, I have to add:
4_PREVIOUS_MONTH + 5_PREVIOUS_MONTH + .... + 11_PREVIOUS_MONTH which is exactly 8 months.

I thought of a foreach with this syntax:
| foreach * _PREVIOUS_MONTH [eval TOTAL = TOTAL + if (* _ PREVIOUS_MONTH> = 4, <<FIELD>>, 0)]

but it does not work.
You can help me? I'm going crazy to find a solution 🙂
Tks
Bye
Antonio

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You still need to initialise TOTAL (otherwise it remains null)

| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")
| eval TOTAL=0
| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]

|table TOTAL

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

0_previous_month is November, 1_previous_month is October, 7_previous_month is April so you might want to try something like this

| foreach * _PREVIOUS_MONTH [eval TOTAL = if (<<MATCHSTR>> < 8, TOTAL +'<<FIELD>>', TOTAL)]

antonio147
Communicator

Hi ITWishperer,

Thank you for your answer

Does not work 😞
it does not return anything.

| eval TOTAL = TOTAL + '<<FIELD>>' does not work.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try initialising TOTAL prior to the foreach

| eval TOTAL=0

antonio147
Communicator

in this case it returns me TOTAL = 0

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please share your current search query

0 Karma

antonio147
Communicator

My query:

| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")

| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]

|table TOTAL

 

the result of the loadjob :

antonio147_0-1637329473385.png

 

0 Karma

antonio147
Communicator

obviously I have translated *_PREVIOUS_MONTH in  "*_MESE_PRECEDENTE" 🙂

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You still need to initialise TOTAL (otherwise it remains null)

| loadjob savedsearch="antonio:enterprise:20211025_PASSAGGIO_AGGREGATO_DATE"
|where TIPOLOGIA="fs_ampliamenti_ip" AND OFFERTA="DIRETTA" AND NOT like(STATO,"KO%")
| eval TOTAL=0
| foreach *_PREVIOUS_MONTH [|eval TOTAL = if (<<MATCHSTR>> < ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]

|table TOTAL

antonio147
Communicator

I had initialized TOTAL but a few lines higher.
While now it works.

I also inserted these lines:
     | eval CURRENT MONTH = strftime (relative_time (now (), "-0d @ d"), "% m")
     | eval YEARLY = CURRENT MONTH + 1-4
and changed the foreach:
| foreach * _PREVIOUS_MONTH [| eval TOTAL = if (<<MATCHSTR>> <ANNOFISCALE, TOTAL + '<<FIELD>>', TOTAL)]
You are a Great !!!

Thank you so much

0 Karma
Get Updates on the Splunk Community!

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

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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