Getting Data In

Why isn't my data sorting in ascending order?

NicoloPunzalan2
Engager

Hi All,

My dashboard is working fine and as expected for a month now. My dashboard is about incident management for customer update to be exact. My issue is that one of my column(need_to_update) is not sorting in an ascending order. It was working fine for all the values for the month of October but when there were new values for November, the November values are now at the top of the table which should not be because it is sorted in an +need_to_update.

| where breach_status="Not Breached" | sort +need_to_update |table ticket_number severity assignment_group additional need_to_update assigned_to | rename ticket_number as "Ticket Number" severity as "Priority" additional as "Last Update" assignment_group as "Assignment Group" need_to_update as "Should be updated" assigned_to as "Assigned To"

Example of the order of the values that is in my table:
01/11/2017 02:52:48
30/10/2017 05:19:39
30/10/2017 05:20:03
30/10/2017 05:20:34

Could anyone help me why do my data behave this way?
Hoping for your opinions. Thank you.

All the best,
Nicolo

0 Karma
1 Solution

HiroshiSatoh
Champion

I think you should have an epoch time for sorting.

eval sort_need_to_update=strptime(need_to_update,"%d/%m/%Y %H:%M:%S")

View solution in original post

0 Karma

HiroshiSatoh
Champion

I think you should have an epoch time for sorting.

eval sort_need_to_update=strptime(need_to_update,"%d/%m/%Y %H:%M:%S")
0 Karma

NicoloPunzalan2
Engager

Hi,
I have used this eval query for my column and this column is from this query.

| eval additional=strptime(comments,"%d/%m/%Y %H:%M:%S")

| eval need_to_update=if(severity=="P3", additional+86400, additional+172800)

| eval need_to_update=strftime(need_to_update, "%d/%m/%Y %H:%M:%S")
But it still not works.

Thanks,
Nicolo

0 Karma

HiroshiSatoh
Champion

Embedding in your query

| where breach_status="Not Breached" 
| eval sort_need_to_update=strptime(need_to_update,"%d/%m/%Y %H:%M:%S")
| sort +sort_need_to_update 
| table ticket_number severity assignment_group additional need_to_update assigned_to 
| rename ticket_number as "Ticket Number" severity as "Priority" additional as "Last Update" assignment_group as "Assignment Group" need_to_update as "Should be updated" assigned_to as "Assigned To"
0 Karma

NicoloPunzalan2
Engager

Hi Hiroshi,

Thank you so much!! This worked for me.
Hope you have a great day ahead.

All the best,
Nicolo

0 Karma

hardikJsheth
Motivator

The Splunk is taking need_to_update column as a STRING value and applying sorting rather than taking it as datetime. First create date time object using mktime function and then sort on that field. Following answer has good example of mktime funciton.
https://answers.splunk.com/answers/116338/convert-string-to-date.html

0 Karma

NicoloPunzalan2
Engager

Hi,
I have used this eval query for my column
| eval need_to_update=strftime(need_to_update, "%d/%m/%Y %H:%M:%S")
But it still not works.

Thanks,
Nicolo

0 Karma

NicoloPunzalan2
Engager

Hi,
I have used this eval query for my column and this column(need_to_update) is from this query.

| eval additional=strptime(comments,"%d/%m/%Y %H:%M:%S")
| eval need_to_update=if(severity=="P3", additional+86400, additional+172800)
| eval need_to_update=strftime(need_to_update, "%d/%m/%Y %H:%M:%S")
But it still not works.

Thanks,
Nicolo

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 ...