Building for the Splunk Platform

Perform numerical calculations on field containing numbers with $ sign

mcastino
New Member

Hi all,

new to splunk so apologise for such a simple question.

I have a field called "options.deposit.amount" containing deposit amounts that Splunk is treating as string because value is preceded by $ sign. I'd like to be able to convert to numeric values so that I can perform calculations but am having trouble.

Data comes in the format of $XX.xx with no commas or spaces.

I have tried this with no success: search| eval Dep_Amt = substr(options.deposit.amount,2)

Any help with be greatly appreciated.

Thanks!

Tags (1)
0 Karma

adonio
Ultra Champion

hello there,

many ways to go about this one. here is an example with rex command:
run this search anywhere and apply

  | makeresults count=1
    | eval options.deposit.amount = "$100.01,$101.02,$12.90"
    | makemv delim="," options.deposit.amount
    | mvexpand options.deposit.amount
    | rex field=options.deposit.amount "\$(?<money>\d+.\d+)"
    | eventstats sum(money) as total

hope it helps

@mcastino, here are two more options:

1) Using replace() eval function

| eval options.deposit.amount_replace=replace('options.deposit.amount',"^\$","")

1) Using substr() eval function

| eval options.deposit.amount_substr=substr('options.deposit.amount',2,len('options.deposit.amount'))
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...