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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...