How to create a table with months along with two other types of data summed?

[larswu]

I would like to create a table out of a search with months (date_month) on the first column, then the sum of all unique website cart numbers (ODE_CART_NUMBER) in the next column, then the third column would be a sum of all of the files associated with all of the carts in each month (file).

The table would look like this:

Month              #of Carts                Total Files 
Jan                     12                            150
Feb                     27                           265
March               16                             200

I'm still getting to know Splunk. I tried a few variations of piping tables and sums but I cannot get it to give me what I want. My current base search is below. I created a custom field to extract the ODE_CART_NUMBER field

host="ASPERA-2" op="send" status="success"

[richgalloway]

You should be able to do that with the stats command. I used the sum function because that's the term used in the question, but you may want to use count, instead.

... | stats sum(ODE_CART_NUMBER) sum(file) by date_month

[kmaron · Accepted solution]

| stats dc(ODE_CART_NUMBER) as "#of Carts" count(file) as "Files" by date_month

[larswu]

That works, now how would I sort the table by year and then by month?

[somesoni2]

Try like this

host="ASPERA-2" op="send" status="success"
| timechart span=1mon dc(ODE_CART_NUMBER) as "#of Carts" count(file) as "Files" 
| rename COMMENT as "Comment only, remove this line. Above line will automatically sort the ouput in ascending order of time. Next just get your formatted year/month"
| eval Year=strftime(_time,"%Y") | eval Month=strftime(_time,"%B") 
| table Year Month  "#of Carts"  "Files" 

[kmaron]

just a straight sort would be

| sort date_year date_month   

for ascending
or

| sort - date_year - date_month   

for descending

If you want the months in chronological order instead of alphabetical order you'll need to do some finagling with assigning numbers to the months. I liked another answers post in the previous comment that can help you with that.

[larswu]

That's great! Would it be possible to add the year (date_year) as a column and then sort by year and then month?

[kmaron]

yup just add it to the 'by'

 | stats dc(ODE_CART_NUMBER) as "#of Carts" count(file) as "Files" by date_month date_year

[kmaron]

now when you sort it's going to sort your months in alphabetical order. If you don't want that you should check out this post that helps fix that: https://answers.splunk.com/answers/170706/how-to-sort-data-in-chronological-order-by-month-n.html