Adding Event Count to Table

[bburns2122]

I am trying to get the Date (altering _time in a specific format shown below), number of events (which I am using stats count to count the number of occurrences of "EXAMPLE" and renaming as Transactions), and the sum of a value from different events (which I have to trim USD and quotes in order to make it register as a number). I can get the results separately but when I try to get all three columns to show in one table, it will only give me the number of events. All fields come from the same log. I want it to look like this:
------------------------------------------------
Date                                 Transactions                                  entryAmountDay

08-02-2020                      7                                                          5000.00
-------------------------------------------------

What works separately:

source=example  "EXAMPLE"
| stats count
| rename count AS Transactions

-------------------------
source=example "EXAMPLE"
| eval Date = strftime(_time, "%m-%d-%y") | fields - _time
| eval entryAmount = trim(replace(entryAmount, "'USD", "")), eval entryAmount = trim(replace(entryAmount, "'", "")), eval entryAmount=trim(entryAmount)
| stats sum(entryAmount) as entryAmountDay by Date

I have tried many different combinations and commands but can't get anything to work. Please help!

Thank you

[richgalloway]

It would help if you said how the current query is failing you.  Have you tried this?

source=example "EXAMPLE"
| eval Date = strftime(_time, "%m-%d-%y") | fields - _time
| eval entryAmount = trim(replace(entryAmount, "'USD", "")), eval entryAmount = trim(replace(entryAmount, "'", "")), eval entryAmount=trim(entryAmount)
| stats sum(entryAmount) as entryAmountDay, count as Transactions by Date

[bburns2122]

It looks like that query fails with the same issues I was having. The date and transaction field get populated but not the entryAmountDay. The entryAmountDay gets left blank.

[richgalloway · Accepted solution]

Splunk could be treating entryAmount as a string which it can't add up.  Try converting it to a number.

source=example "EXAMPLE"
| eval Date = strftime(_time, "%m-%d-%y") | fields - _time
| eval entryAmount = trim(replace(entryAmount, "'USD", "")), eval entryAmount = trim(replace(entryAmount, "'", "")), eval entryAmount=tonumber(trim(entryAmount), 10)
| stats sum(entryAmount) as entryAmountDay, count as Transactions by Date

[bburns2122]

@richgalloway  It looks like that one gives me the same output that does not include the entryAmount. This is the only field in the log that includes the price amount so I'm not sure how else to 'transform' this to a usable number.

[richgalloway]

It's time to debug the query by peeling it back.
Remove the stats command and verify the entryAmount field contains a number for every event. If any of them are null then that would cause the stats command to fail. We can fix that with fillnull value=0 entryAmount, but let's see what the data looks like, first.

[bburns2122]

Your first suggestion was correct with one issue, there didn't need to be the extra eval in front of the other trims. 

source=example "EXAMPLE" | eval Date = strftime(_time, "%m-%d-%y") | fields - _time | eval entryAmount = trim(replace(entryAmount, "'USD", "")), eval entryAmount = trim(replace(entryAmount, "'", "")), eval entryAmount=tonumber(trim(entryAmount), 10) | stats sum(entryAmount) as entryAmountDay, count as Transactions by Date

This search worked:

source=example "EXAMPLE" | eval Date = strftime(_time, "%m-%d-%y") | fields - _time | eval entryAmount = trim(replace(entryAmount, "'USD", "")), entryAmount = trim(replace(entryAmount, "'", "")), entryAmount=tonumber(trim(entryAmount), 10) | stats sum(entryAmount) as entryAmountDay, count as Transactions by Date

Thank you so much for your help with this!

[richgalloway]

If your problem is resolved, then please click an "Accept as Solution" button to help future readers.