Splunk Search

How to sum numbers with commas

rksubbu
Explorer

I would like to calculate the total for the following sample. These are numbers but have comma.

122 
3,871
17,896
33,011
112,345
1,112,345

My query looks like this and the values appear in DEL_JOBS

... | rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)"

I was trying isnumber("X",Y), but I get this

[EventsViewer module] Error in 'eval'
command: The 'tonumber' function is
unsupported.

What would be the best way to do this simple arithmetic?
Thanks.

Tags (3)
1 Solution

dwaddle
SplunkTrust
SplunkTrust

Maybe not the best but one that works quickly and easily is to use rex's sed mode to strip off the commas:

... | rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)"
| rex field=DEL_JOBS mode=sed "s/,//g"

I just noticed that the convert command has an rmcomma operation. So, this is probably the BEST way to do it.

... | rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)" | convert rmcomma(DEL_JOBS)

View solution in original post

rksubbu
Explorer

This was the query I used.

...| rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)" | rex field=DEL_JOBS mode=sed "s/,//g" | stats sum(DEL_JOBS) as Total_Jobs_Deleted
0 Karma

joshd
SplunkTrust
SplunkTrust

why not use replace on every occurrence of a comma then perform your calculations on the new field?

ie. | eval amount=replace(DEL_JOBS, ",", "")

joshd
SplunkTrust
SplunkTrust

Agree with you totally! I actually read your question wrong initially and thought you had commas where you wanted periods, hence why I immediately recommended the replace command then revised the usage of it, dwaddle beat me to the punch with sed, figured Id leave mine as an option anyhow 🙂 You also dont have to create a new field you can do: eval DEL_JOBS=replace(DEL_JOBS, ",","")

0 Karma

rksubbu
Explorer

This also worked great. But, I am marking the first answer as accepted because it came in earlier and it was one field less. 🙂

Thanks for the answer. Appreciate it.

0 Karma

dwaddle
SplunkTrust
SplunkTrust

Maybe not the best but one that works quickly and easily is to use rex's sed mode to strip off the commas:

... | rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)"
| rex field=DEL_JOBS mode=sed "s/,//g"

I just noticed that the convert command has an rmcomma operation. So, this is probably the BEST way to do it.

... | rex field=_raw  "Total Jobs Deleted: (?<DEL_JOBS>.*)" | convert rmcomma(DEL_JOBS)

View solution in original post

rksubbu
Explorer

Thanks for the answer. Worked great!

0 Karma