Perform numerical calculations on field containing numbers with $ sign

[mcastino]

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!

[adonio]

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

[niketn]

@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'))