Addtotals / Convert String To Number

[HeinzWaescher]

Hi,

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.

Best

Heinz

[lguinn2]

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.

[HeinzWaescher]

Hi,

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.

[HeinzWaescher]

But I would need a unique identifier for each event to do this?!

And I'm really wondering why eventstats can handle the value types of my "Amount" field and addtotals cannot

[Ayn]

Not if you tell it not to. You can split the stats as you want.

[HeinzWaescher]

But eventstats will calculate to total amount over all events and write this value to each event, right?

[HeinzWaescher]

I don't think there are added spaces. This doesn't help:

| eval n=trim(Amount)
| eval m=tostring(n, "commas")
| convert num(m) AS z
| addtotals fieldname=sum z

[HeinzWaescher]

Hey,

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")

[aelliott]

You could convert them all to string using convert tostring() , then convert num()

Perhaps this post can assist:
http://answers.splunk.com/answers/103101/convert-a-string-into-a-number