Splunk Search

Adding Event Count to Table

bburns2122
Explorer

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

Labels (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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

 

---
If this reply helps you, Karma would be appreciated.
0 Karma

bburns2122
Explorer

bburns2122_0-1596550626297.png

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.

bburns2122
Explorer

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

bburns2122_0-1596718204769.png

 

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

bburns2122
Explorer

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!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...