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!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...