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.
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!
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!
Thanks mayurr98.
It worked! Really appreciate it.