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!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...