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
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.
Put your search query within <![CDATA[ your search query here ]]> tag. Alternatively, replace '<' with "<".
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?
Amt = substr(Amount,2)
keeps the $ if the value is negative.
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.