in my event the field Amount can appear several times. The value is an amount of products. Sometimes Splunk identifies field Amount as a string, but it should always be a number.
In the end, I want to create a new field that shows the total Amount for each event:
| addtotals fieldname=totalamount Amount
I've already tried out to convert the values:
| convert num(Amount) | convert auto(Amount)
But this didn't solve the problem.
First - are all the Amount fields spelled the same - no spelling or capitalization variations?
Second - if Splunk sees this as a multi-valued field, it is a single field with multiple values and
addtotals can't do that. So you could do this:
yoursearchhere | eval id=splunk_server . index . _cd | mvexpand Amount | eval amount=tonumber(Amount) | stats sum(amount) by id
Every event can be uniquely identified, so I used that in my solution. Of course, after you run this, you just get the amounts and id numbers, which isn't very useful. But you can change the stats command to include additional fields.
And if you really want to go crazy with it:
yoursearchhere | eval id=splunk_server . index . _cd | join id [ search yoursearchhere | eval id=splunk_server . index . _cd | mvexpand Amount | eval amount=tonumber(Amount) | stats sum(amount) as TotalAmount by id ] | fields - id
At the end of this search, you will have the same events that you started with - but with one additional field called TotalAmount. It will cost you a subsearch and a join, though.
thanks for your input.
All fields are spelled the same. Just using
| stats sum(Amount)
to get the TotalAmount is no problem (your first solution). But I need it as field in the event to calculate more stats later.
Wouldn't it be easier to use something like this, after creating the eventid:
my search | eval id=splunk_server . index . _cd | eventstats sum(Amount) AS TotalAmount by id
As far as I understand it, this will create a TotalAmount field per event, which can be used in further stats commands.
Thanks for your answer.
Unfortunately the solutions from the linked post don't help and converting to a string before doesn't work. The field n is empty.
| eval n=tostring(Amount,"commas")