topic Re: Sum values from a table in Splunk Search

Sum values from a table

matansocher — Thu, 31 Aug 2017 08:31:37 GMT

Hi,

I have created a table in splunk and 1 of the fields is numeric('sloc').
I would like to sum the values for each 'core'

I was trying to write something like:

index=testeda_p groupID=sloc_data 
| table core sloc_date sloc
| stats sum(sloc) as sumForCore by core

But there are no results in the new field I created (sumForCore)
My result:

I have also tried:

| stats sum(tonumber(sloc)) as sumForCore by core

and nothing
what am I doing wrong?

Thanks

Re: Sum values from a table

gcusello — Thu, 31 Aug 2017 09:07:10 GMT

Hi
did you tried

index=testeda_p groupID=sloc_data 
| stats values(sloc_date) AS sloc_date sum(sloc) as sumForCore by core

?
Bye.
Giuseppe

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 09:14:57 GMT

Just tried and still no result in the sumForCore field

Re: Sum values from a table

gcusello — Tue, 29 Sep 2020 15:34:15 GMT

two stupid tests
index=testeda_p groupID=sloc_data
| stats sum(sloc) by core

index=testeda_p groupID=sloc_data
| stats count by core

Bye.
Giuseppe

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 09:42:35 GMT

the count works just fine but the sum return no value

Re: Sum values from a table

gcusello — Thu, 31 Aug 2017 09:50:59 GMT

This means that sloc has a text format and you have to convert it in number.
try with

index=testeda_p groupID=sloc_data 
| eval sloc=tonumber(sloc)
| stats values(sloc_date) AS sloc_date sum(sloc) as sumForCore by core

Bye.
Giuseppe

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 10:25:39 GMT

Still no results in sumForCore
count, min, max
only avg and sum doesn't

Re: Sum values from a table

gcusello — Thu, 31 Aug 2017 10:39:03 GMT

yes: count min and max don't use numbers, infact if you verify 2 is greater that 15!
if you try index=_internal kb=* | head 100 | stats sum(kb) AS kb by host you can see that the method is correct.
you should verify format of sloc because there's some problem in format, maybe decimals.
try using

index=testeda_p groupID=sloc_data 
| convert num(sloc) AS sloc2
| stats values(sloc_date) AS sloc_date sum(sloc2) as sumForCore by core

Bye.
Giuseppe

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 10:57:17 GMT

the last suggestion does not work either.
I will try to figure out the problem with the format
thanks

Re: Sum values from a table

niketn — Thu, 31 Aug 2017 11:08:28 GMT

How are you getting yout sloc? Is it possible there are whitespaces (before and/or after)?

 index=testeda_p groupID=sloc_data
 | eval sloc=trim(sloc) 
 | stats sum(sloc) as sumForCore by core

Or extract only digits through rex (if you have decimal values your rex will change). Following rex is based on your sample data.

 index=testeda_p groupID=sloc_data
 | rex field=sloc "(?<sloc>\d+)"
 | stats sum(sloc) as sumForCore by core

Please try out and confirm.

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 11:38:02 GMT

Thank you!
The trim function did solve my problem.

Re: Sum values from a table

cmerriman — Thu, 31 Aug 2017 11:40:27 GMT

do you ever have any null values is sloc?

try

 index=testeda_p groupID=sloc_data 
 | table core sloc_date sloc
 |fillnull sloc value=0
 | stats sum(sloc) as sumForCore by core

if that doesn't work, add |eval sloc=tonumber(sloc) before the fillnull command.

Re: Sum values from a table

matansocher — Thu, 31 Aug 2017 12:33:19 GMT

cmerriman, it didnt work for me. I think the problem was with the format of the numbers.
the trim function solve my problem.

Re: Sum values from a table

niketn — Thu, 31 Aug 2017 12:58:20 GMT

Anytime! Glad it worked 🙂