Splunk Search

How do I sort by date when using strftime?

caffein
Path Finder

I would like to group a bunch of data by date, but splunk doesn't seem to have a function to do this explicitly. So, I used this workaround:

index=foo
|eval date=strftime(_time, "%Y-%m-%d")
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country
|table country, date, BarAvg, StuffAvg

Now, as far as grouping all the data appropriately this works fine. The problem is that I can't change the sort when I'm in table view. I click date and the little arrow will change direction, but all the rows stay in the same place. Sorting on all the other fields works fine though.

So, with all this in mind:

1) How do make the date column sortable?

2) Is the way I'm doing this correct, or is there a better method that I can use?

Tags (3)
1 Solution

caffein
Path Finder

Ok, I was able to solve this by combining both yannK's and Genti's answers. Here's what I did:

index=foo 
|eval date=strftime(_time, "%Y-%m-%d")
|convert timeformat="%Y-%m-%d" mktime(date) AS date
|fieldformat date=strftime(date,"%Y-%m-%d")  
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country 
|table country, date, BarAvg, StuffAvg

Basically, this pulls out just the date from _time, converts the date into epoch time, then makes it look nice for viewing. I can then use the date epoch time for grouping and sorting, but when I view it, it's in a readable format.

View solution in original post

caffein
Path Finder

Ok, I was able to solve this by combining both yannK's and Genti's answers. Here's what I did:

index=foo 
|eval date=strftime(_time, "%Y-%m-%d")
|convert timeformat="%Y-%m-%d" mktime(date) AS date
|fieldformat date=strftime(date,"%Y-%m-%d")  
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country 
|table country, date, BarAvg, StuffAvg

Basically, this pulls out just the date from _time, converts the date into epoch time, then makes it look nice for viewing. I can then use the date epoch time for grouping and sorting, but when I view it, it's in a readable format.

caffein
Path Finder

lol. Unfortunately, splunk is a great robot and I still need to use date for grouping the data. However, this won't work because fieldformat doesn't alter the underlying data only how it's displayed. From what I can tell, your suggestion would be like saying "group by _time, but only show the date portion of _time in the results".

0 Karma

yannK
Splunk Employee
Splunk Employee

Epoch time is readable for robots
http://www.quickmeme.com/meme/3p89ty/

Maybe you only need to do the time to date conversion once, not 3 times.

| fieldformat date=strftime(_time,"%Y-%m-%d")

0 Karma

Genti
Splunk Employee
Splunk Employee

Instead of using eval, you should use fieldformat.

Here's an example search:

index=_internal | head 100 | eval raw=_raw | eval Time = _time | fieldformat Time=strftime(Time, "%D %H:%M:%S %p") |  table Time raw

yannK
Splunk Employee
Splunk Employee

there is a typo in your eval, please use | eval field=function(blah)

Sorting by date works fine, to do a presorting use try |sort -date
Also a workaround is to convert the date to seconds and use it to sort before defining the table columns

index=foo 
|eval date=strftime(_time, "%Y-%m-%d")
|stats avg(bar) AS BarAvg, avg(stuff) AS StuffAvg BY date,country 
| convert timeformat="%Y-%m-%d" mktime(date) AS date_epoch
|sort -date_epoch  |table country, date, BarAvg, StuffAvg

caffein
Path Finder

I've got about 40 rows aggregated from about 7 million logs. If I set the number of rows per page to 50, everything will be on one page. At some point I might want to do a larger number of values, which when this could become an issue.

0 Karma

yannK
Splunk Employee
Splunk Employee

I am surprised that the dymanic sorting using the date column is not working. the date is Y-m-d so alphabetically sortable.

How many pages of results to you have, are you on the first one when you sort ?

0 Karma

caffein
Path Finder

Ok, well this kind of worked. By inserting date_epoch into the table I can view the pretty print date, but dynamically sort using date_epoch. Part of what I wanted is to be able to sort by clicking on the column headers in the table view rather than having the sort set by the query. Although, thanks for letting me know how to do the presort, it will be nice to set the default sort. I still can't change the sort by clicking the date column header though.

0 Karma
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...