Splunk Search

Why is stats count query not working as expected?

PaulaCom
Path Finder

Hi All 

I have a csv file in splunk that i am searching on

i am looking to get the total monthly figure spend on each account number and then the total spend per month (of all account number) 

Here my query i've created 

..... main search 
| rename Order_Date as Month
| stats count by Account_Number, Total_Sum, Month
| eventstats sum(Total_Sum) as Monthly_Total by Month
| sort - Total_Sum
| sort Account_Number
| stats list(Account_Number) as Account_Number, list(Total_Sum) as Total_Sum, values(Monthly_Total) as Monthly_Total by Month

it looks good but then i noticed that i haven't added each Account_Number up per month (see table below as 6210 appears 3 times.  Can anyone help? 

Month Account_Number Total_Sum Monthly_Total

April
5751
5756
6201
6203
6205
6210
6210
6210
6340
1986
23423.42
139
17003.09
7107.98
1395.12
50
18
8.05
51130.66

 

Also i'd like to make the figures in Euros

I've tried below but it wont work - any ideas 
| fieldformat Total="€".tostring(Total, "commas")

finally Splunk has put the month April at the top, i've tried to use the eval command to move January to the top using below code but this hasn't helped - any ideas? 

| eval M=case(Month=="April","04", Month=="January","01", Month=="February", "02", Month=="March","03", Month=="May", "05", Month=="June", "06", Month=="July","07", Month=="August","08", Month=="September", "09")
| sort M
| fields - M

 

Many thanks

 

Labels (5)
Tags (1)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

The Account_Number values are repeated because they are repeated in the data.  That's the nature of the list function.  To show unique values, use the values function.

... main search 
| rename Order_Date as Month
| stats count by Account_Number, Total_Sum, Month
| eventstats sum(Total_Sum) as Monthly_Total by Month
| sort - Total_Sum
| sort Account_Number
| stats values(Account_Number) as Account_Number, list(Total_Sum) as Total_Sum, values(Monthly_Total) as Monthly_Total by Month

You didn't what "it wont work" means, but I suspect the fieldformat command is failing because the Total field doesn't exist.

The eval trick to sort fields as desired is a proven method, but how well it works depends on where it's placed in the query.  Please show the full query with the code that "hasn't helped".

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...