topic Re: Adding Event Count to Table in Splunk Search

Adding Event Count to Table

bburns2122 — Mon, 03 Aug 2020 20:56:36 GMT

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

Re: Adding Event Count to Table

richgalloway — Tue, 04 Aug 2020 00:38:34 GMT

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

Re: Adding Event Count to Table

bburns2122 — Tue, 04 Aug 2020 14:17:56 GMT

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.

Re: Adding Event Count to Table

richgalloway — Tue, 04 Aug 2020 16:36:07 GMT

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

Re: Adding Event Count to Table

bburns2122 — Thu, 06 Aug 2020 12:51:09 GMT

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

Re: Adding Event Count to Table

richgalloway — Thu, 06 Aug 2020 13:21:19 GMT

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.

Re: Adding Event Count to Table

bburns2122 — Thu, 06 Aug 2020 14:28:21 GMT

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!

Re: Adding Event Count to Table

richgalloway — Thu, 06 Aug 2020 16:35:15 GMT

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