Splunk Search

Addtotals / Convert String To Number

HeinzWaescher
Motivator

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

Tags (2)

lguinn2
Legend

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.

0 Karma

HeinzWaescher
Motivator

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.

0 Karma

HeinzWaescher
Motivator

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

0 Karma

Ayn
Legend

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

0 Karma

HeinzWaescher
Motivator

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

0 Karma

HeinzWaescher
Motivator

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
Motivator

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

aelliott
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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 ...