Splunk Search

How to convert a dollar amount field with $ sign, commas and decimals to a number so I can do avg or sum, etc.?

bandit
Motivator

latest funciton works but avg function does not. I believe splunk is treating my Amount field as a text string. Any way to convert a dollar amount to a numeric value?

Thanks,

Rob

Working:
sourcetype="financials-2" | stats latest(Amount) by Account

    Account     latest(Amount)
1   Advertising $232.62
2   Annual Report   $10.00
3   Electric    $367.73
4   Gazebo Maintenance  $660.00
5   General Maintenance $1,200.00
6   Insurance   $1,638.00
7   Lawn Maint - Extra  $9,200.00
8   Lawn Maintenance    $1,331.00
9   Legal   $231.25
10  Mailing Supplies    $54.62

Not working - returns null values for avg(Amount): 
sourcetype="financials-2" | stats avg(Amount) by Account
Tags (4)
0 Karma

jonuwz
Influencer

Strip all the non-numerical characters out 1st. Splunk is treating the values as a string, not a number.

sourcetype="financials-2" 
| rex mode=sed field=Amount "s/[^\d\.-]//g"
| stats avg(Amount) as AvgAmount by Account
| eval AvgAmount=if(AvgAmount < 0, "-$".trim(AvgAmount,"-"),"$".AvgAmount)

The last line just reintroduces the $ symbol.

somesoni2
SplunkTrust
SplunkTrust

Put your search query within <![CDATA[ your search query here ]]> tag. Alternatively, replace '<' with "&lt;".

0 Karma

_gkollias
SplunkTrust
SplunkTrust

This works great, however in simple xml, Splunk doesn't like the <. I am getting this error message - Encountered the following error while trying to update: In handler 'views': Error parsing XML on line 67: StartTag: invalid element name. Am I missing something?

0 Karma

jonuwz
Influencer

Amt = substr(Amount,2) keeps the $ if the value is negative.

0 Karma

kristian_kolb
Ultra Champion

I think you should keep the decimal dot. Otherwise you'll end up with the the wrong figures.

I suggest the slightly simpler;

... | eval Amt = substr(Amount,2) | convert rmcomma(Amt) | stats avg(Amt) by Account

EDIT: my bad. you did keep it. ... and I didn't remove the comma....

EDIT2: updated above, now without commas.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...