Why isn't my data sorting in ascending order?

[NicoloPunzalan2]

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

[HiroshiSatoh · Accepted solution]

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")

[NicoloPunzalan2]

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

[HiroshiSatoh]

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"

[NicoloPunzalan2]

Hi Hiroshi,

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

All the best,
Nicolo

[hardikJsheth]

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

[NicoloPunzalan2]

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

[NicoloPunzalan2]

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