topic Addtotals / Convert String To Number in Splunk Search

Addtotals / Convert String To Number

HeinzWaescher — Thu, 16 Jan 2014 16:30:09 GMT

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

Re: Addtotals / Convert String To Number

aelliott — Thu, 16 Jan 2014 16:33:23 GMT

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

Re: Addtotals / Convert String To Number

HeinzWaescher — Thu, 16 Jan 2014 16:45:40 GMT

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

Re: Addtotals / Convert String To Number

HeinzWaescher — Thu, 16 Jan 2014 16:58:46 GMT

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

Re: Addtotals / Convert String To Number

HeinzWaescher — Thu, 16 Jan 2014 17:04:19 GMT

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

Re: Addtotals / Convert String To Number

Ayn — Thu, 16 Jan 2014 17:20:29 GMT

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

Re: Addtotals / Convert String To Number

HeinzWaescher — Thu, 16 Jan 2014 17:22:18 GMT

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

Re: Addtotals / Convert String To Number

lguinn2 — Thu, 16 Jan 2014 19:12:46 GMT

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.

Re: Addtotals / Convert String To Number

HeinzWaescher — Fri, 17 Jan 2014 08:54:36 GMT

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.