topic Re: How to create a table with months along with two other types of data summed? in Splunk Search

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

larswu — Tue, 29 Sep 2020 20:40:15 GMT

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"

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

kmaron — Fri, 27 Jul 2018 19:37:51 GMT

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

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

larswu — Fri, 27 Jul 2018 19:43:12 GMT

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

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

kmaron — Fri, 27 Jul 2018 19:47:45 GMT

yup just add it to the 'by'

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

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

richgalloway — Fri, 27 Jul 2018 19:48:20 GMT

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

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

kmaron — Fri, 27 Jul 2018 19:50:08 GMT

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

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

larswu — Fri, 27 Jul 2018 19:53:16 GMT

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

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

kmaron — Fri, 27 Jul 2018 19:57:28 GMT

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.

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

somesoni2 — Fri, 27 Jul 2018 20:33:54 GMT

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"