topic Re: overall sum and aggregate sum in Splunk Search

overall sum and aggregate sum

yuming1127 — Wed, 14 Apr 2021 03:45:02 GMT

command:

search....

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122)
| table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122

how can i get effort = sum of field in same row instead of overall sum.

expected output:

effort

Re: overall sum and aggregate sum

scelikok — Wed, 14 Apr 2021 04:13:27 GMT

Hi @yuming1127,

You can use addtotals command;

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122) | table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122 | addtotals

Re: overall sum and aggregate sum

yuming1127 — Wed, 14 Apr 2021 04:33:45 GMT

Great one, really appreciate your solution. Quick and easy

Re: overall sum and aggregate sum

bowesmana — Thu, 15 Apr 2021 06:14:52 GMT

@yuming1127

There's something odd in your SPL

In the eval line you are adding the following numbers together

| eval effort=exact(21+31+61+1103+7306+7505+15105+15106+15122)

so here effort evaluates to the number 61360

BUT in this statement

| table tag,effort,16910,21,31,61,1103,7306,7505,15105,15106,15122

you are using these 'numbers' as fields and in your image they are actually field names containing only numbers.

So, the problem is that your eval statement is wrong in that it is adding up numbers not fields. In order to treat those fields as fields not numbers, you need to wrap them in single quotes, '. So your eval should be

| eval effort=('21'+'31'+'61'+'1103'+'7306'+'7505'+'15105'+'15106'+'15122')

BUT that will not give you your result for all rows, as the '21' field in row 1 in your example has no value, so will make 'effort' have no value.

You should use addtotals as @scelikok suggests and that will handle ALL fields or just the ones you specify and also handle the null value case.

Re: overall sum and aggregate sum

yuming1127 — Wed, 21 Apr 2021 07:47:14 GMT

ya, found a way to replace the null value with 0 and follow up with eval function, that worked too. Thanks!