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
Builder

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
Builder

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)

rksubbu
Explorer

Thanks for the answer. Worked great!

0 Karma
Get Updates on the Splunk Community!

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

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...