Splunk Search

How to arrange by month/year chronological order

NicoloPunzalan2
Engager

Hi All,

Im creating a table in which it will count the ticket that was logged per month and I need to do it for the last three months.
Im finding a hard time to sort my table by month/year. I need to place them in chronological order with this format month/year.
I tried sorting them by %m (but it only sort them in numerical order) and %b(but it sort them in alphabetical order. My table is looking like this.

Month count
Dec/2017 38

Feb/2018 2829

Jan/2018 933

Mar/2018 590

My query:
.......search query
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval Month=strftime(created_at,"%b/%Y")
| eval epoch = strptime(opened_at,"%d/%m/%Y %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400
| stats count(ticket_number) by Month

Could anyone help me on this. Thanks in advance.

Tags (2)
0 Karma
1 Solution

mayurr98
Super Champion

Try this

.......search query
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval Month=strftime(created_at,"%b/%Y")
| eval date_month=strftime(created_at,”%m")
| eval date_year=strftime(created_at,"%Y")
| eval epoch = strptime(opened_at,"%d/%m/%Y %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400
| stats count(ticket_number) by Month date_month date_year
| sort- date_year date_month | fields- date_year date_month

Let me know if this helps!

View solution in original post

0 Karma

mayurr98
Super Champion

Try this

.......search query
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval Month=strftime(created_at,"%b/%Y")
| eval date_month=strftime(created_at,”%m")
| eval date_year=strftime(created_at,"%Y")
| eval epoch = strptime(opened_at,"%d/%m/%Y %H:%M:%S") | eval diff = (now() - epoch)/60 | where diff < 131400
| stats count(ticket_number) by Month date_month date_year
| sort- date_year date_month | fields- date_year date_month

Let me know if this helps!

0 Karma

NicoloPunzalan2
Engager

Thanks mayurr98.

It worked! Really appreciate it.

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...