What is the best way to do calculations for individual values in multiple columns?

[yvassilyeva]

Hi! i have the following table:

Com-Jun	Com-Jul	Com-Aug	Jun	Jul	Aug
3	3	1	2	5	7
1	1	3	2	5	3

Can i add the values of each column starting with Com to the values of month. (Com-Jun plus June, Com-Jul plus July etc.) So the values in the first 3 columns should be added to the values of the last three columns so that my table looks like this:

Com-Jun	Com-Jul	Com-Aug	June	July	August	Sum-Jun	Sum-Jul	Sum-Aug
3	3	1	2	5	7	5	8	8
1	1	3	2	5	3	3	6	6

Thank you!

[niketn]

@yvassilyeva please add the following to your existing search and it should do a template foreach for all months

<yourCurrentSearch>
| foreach Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec [| eval "Sum-<<FIELD>>"='<<FIELD>>'+'Com-<<FIELD>>']

Also more specific answer can be provided based on whether your SPL is calculating these columns or do these exist in your event.

[yvassilyeva]

@niketn 

I'm not getting the results. What if i have a table like this:

Comp_Jun	Comp_Jul	Comp_Aug	Tar_Jun	Tar_Jul	Tar_Aug
3	3	1	2	5	7
1	1	3	2	5	3

and need to get the sum of Comp_Jun and Tar_Jun, Comp_Jul and Tar_Jul, Comp_Aug and Tar_Aug. My current code is this:

| foreach Tar_Jan,Tar_Feb,Tar_Mar,Tar_Apr,Tar_May,Tar_Jun,Tar_Jul,Tar_Aug,Tar_Sep,Tar_Oct,Tar_Nov,Tar_Dec [| eval "Sum_<<FIELD>>"='<<FIELD>>'+'Comp_<<FIELD>>']

[niketn]

@yvassilyeva thanks for adding the details. I wished you mentioned in your question and your SPL could have been easier... Do read the foreach command Splunk Documentation if the interpretation of the template is not clear. It has solid examples and that is where I learnt these from. https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Foreach

| foreach "Com-*"
    [| eval "Sum-<<MATCHSTR>>"='Tar-<<MATCHSTR>>'+'<<FIELD>>']

Following is a run anywhere search based on sample data provided so that you can play around yourself.

| makeresults 
| eval data="3,3,1,2,5,7;1,1,3,2,5,3" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="," 
| eval Com-Jun=mvindex(data,0),Com-Jul=mvindex(data,1),Com-Aug=mvindex(data,2),Tar-Jun=mvindex(data,3),Tar-Jul=mvindex(data,4),Tar-Aug=mvindex(data,5) 
| table *Jun, *Jul, *Aug 
| foreach "Com-*"
    [| eval "Sum-<<MATCHSTR>>"='Tar-<<MATCHSTR>>'+'<<FIELD>>']
| fields Com-* Tar-* Sum-*

[yvassilyeva]

Thank you so much, @niketn !

[gcusello · Accepted solution]

Hi @yvassilyeva,

you have to use the eval command:

| eval "Sum-Jun"="Com-Jun"+June, "Sum-Jul"="Com-Jul"+July, "Sum-Aug"="Com-Aug"+August

One hint: don't use "-" in the field names, use "_".

Ciao.

Giuseppe

[gcusello]

Hi @yvassilyeva,

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[yvassilyeva]

Thank you!

[yvassilyeva]

Giuseppe,

Thank you for a quick response. Is there a way to calculate that for all three columns at once, instead of going month by month? It would be something like:

eval totals=Comp*+months

[gcusello]

Hi @yvassilyeva,

not in my knowledge!

Ciao.

Giuseppe