Getting Data In

Why isn't my data sorting in ascending order?

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(needtoupdate) 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 +needtoupdate.

| where breachstatus="Not Breached" | sort +needtoupdate |table ticketnumber severity assignmentgroup additional needtoupdate assignedto | rename ticketnumber as "Ticket Number" severity as "Priority" additional as "Last Update" assignmentgroup as "Assignment Group" needtoupdate 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

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

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

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 needtoupdate=if(severity=="P3", additional+86400, additional+172800)

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

Thanks,
Nicolo

0 Karma

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

Hi Hiroshi,

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

All the best,
Nicolo

0 Karma

Motivator

The Splunk is taking needtoupdate 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

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

Thanks,
Nicolo

0 Karma

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

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

Thanks,
Nicolo

0 Karma