Splunk Search

sort show wrong result

Paul1896
Path Finder

Hello,

I hope anyone can help me.

My search

eval epochtime=strptime(DATUM,"%d.%m.%Y") | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15 | sort datefield

I converted the string DATUM to a dateformat and I thought it will be handled as a dateformat, but it doesn't work correctly.

Output:

01.10.2016  0   0    
17.09.2016  0   0   0
18.09.2016  0   0   0
19.09.2016  576     183     0
20.09.2016  0   0   0
21.09.2016  194     0   0
22.09.2016  317126  193     0
23.09.2016  30376   1127    0
24.09.2016  0   0   0
25.09.2016  0   0   0
26.09.2016  1725    18965   0
27.09.2016  21292   390     0
28.09.2016  26605   0   2895
29.09.2016  3324    21580   0
30.09.2016  85292   1352    0 

Why is the first date 1.10.2016? It should be the last one. It looks like that the field "datefield" isn't a dateformat.

Can anybody help me that the sorting is working correctly.

Thank you very much!

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

You're correct. The "datefield" field is not a date. It's a string and it's being sorted correctly as a string. The solution is to sort on a hidden integer. Try this:

eval epochtime=strptime(DATUM,"%d.%m.%Y") | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15 | sort epochtime | fields - epochtime
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're correct. The "datefield" field is not a date. It's a string and it's being sorted correctly as a string. The solution is to sort on a hidden integer. Try this:

eval epochtime=strptime(DATUM,"%d.%m.%Y") | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15 | sort epochtime | fields - epochtime
---
If this reply helps you, Karma would be appreciated.
0 Karma

Paul1896
Path Finder

I also tried this one

| eval datefield=strftime(strptime(DATUM,"%d.%m.%Y") ,"%Y.%m.%d") | chart list(VOL_RO) by datefield,host useother=f | tail 15 | sort +datefield

2016.09.17  0   4189590     52690
2016.09.18  0   4234501     52681
2016.09.19  1668    4262328     52668
2016.09.20  0   4286605     50382
2016.09.21  1349    4222331     50392
2016.09.22  6790    4218210     50392
2016.09.23  380     5412165     50392
2016.09.24  0   5200690     993555
2016.09.25  0   5094012     938512
2016.09.26  1128    4230430     50854
2016.09.27  0   5987518     50378
2016.09.28  1253    4259481     50392
2016.09.29  597     4547325     50392
2016.09.30  0   4624354     50392
2016.10.01  0   0    

At the moment this is the best solution, but it isn't the optimal result for me. If anyone have further ideas, it would be very nice!

Thanks a lot for your great help!

0 Karma

davebrooking
Contributor

Hi

Given what you have so far I think you need to include epochtime in your chart command, so it becomes

 eval epochtime=strptime(DATUM,"%d.%m.%Y") | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by epochtime, datefield,host useother=f | tail 15 | sort epochtime | fields - epochtime

I'm not sure why you're initially evaluating datefield as that is just the same value as DATUM isn't it?

Dave

0 Karma

Paul1896
Path Finder

Hello Rich,

thanks for your fast answer! It is a very nice community.

I got the same result 😞

I also tried to sort between the two eval commands.

I still get the wrong sorting.

0 Karma

inventsekar
SplunkTrust
SplunkTrust

Try to sort on the epoch time and then do the time conversions

0 Karma

Paul1896
Path Finder

You mean this way?

| eval epochtime=strptime(DATUM,"%d.%m.%Y") | sort epochtime  | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15  | fields - epochtime

I get the same result back.

Perhaps the chart-command overwrite all sort-commands?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try this untested query.

eval epochtime=strptime(DATUM,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by epochtime,host useother=f | tail 15 | fieldformat epochtime=strftime(epochtime,"%d.%m.%Y")
---
If this reply helps you, Karma would be appreciated.
0 Karma

cmerriman
Super Champion

it's because 01 is less than 17, I'd bet. Can you reformat the date to be Month/Day/Year? Or create a separate date field for Month/Day/Year that you sort by and then do a fields - MDY to hide that field in the output?

0 Karma

cmerriman
Super Champion

or create a separate field and convert DATNUM to an epoch field and just sort by that.

0 Karma

Paul1896
Path Finder

Hello cmerriman,

thanks for your answer and your help!

I reformated the date to Month/Day/year but now I only get the 01.10.2016 back.

Search:

| eval epochtime=strptime(DATUM,"%m.%d.%Y")  | eval datefield=strftime(epochtime,"%m.%d.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15 | sort datefield

Result:

01.10.2016  0   0 

I also tried:

| eval epochtime=strptime(DATUM,"%m.%d.%Y")  | eval datefield=strftime(epochtime,"%m.%d.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15 | sort epochtime | fields - epochtime

and

| eval epochtime=strptime(DATUM,"%m.%d.%Y") | sort epochtime | eval datefield=strftime(epochtime,"%m.%d.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f | tail 15  | fields - epochtime

I only get the one value back.

0 Karma

cmerriman
Super Champion

could you do

|convert mktime(DATNUM) as epochtime timeformat="%d.%m.%Y"| chart list(VOL_DDC_OUT) by epochtime,host useother=f | tail 15 | sort epochtime|convert ctime(epochtime) as datefield timeformat="%m/%d/%Y"|fields - epochtime
0 Karma

inventsekar
SplunkTrust
SplunkTrust

Please try the query on my answer

0 Karma

inventsekar
SplunkTrust
SplunkTrust
tail 15 | sort DATUM | eval epochtime=strptime(DATUM,"%d.%m.%Y") | eval datefield=strftime(epochtime,"%d.%m.%Y") | chart list(VOL_DDC_OUT) by datefield,host useother=f 
0 Karma

Paul1896
Path Finder

Hello inventsekar,

thanks for your fast answer!

Here is the result:

01.10.2016  0   0    
17.09.2016  0   0   0
18.09.2016  0   0   0
19.09.2016  576     183     0
20.09.2016  0   0   0
21.09.2016  194     0   0
22.09.2016  317126  193     0
23.09.2016  30376   1127    0
24.09.2016  0   0   0
25.09.2016  0   0   0
26.09.2016  1725    18965   0
27.09.2016  21292   390     0
28.09.2016  26605   0   2895
29.09.2016  3324    21580   0
30.09.2016  85292   1352    0 
0 Karma

inventsekar
SplunkTrust
SplunkTrust

check what happens with this one

tail 15 | sort DATUM | chart list(VOL_DDC_OUT) by DATUM,host useother=f

0 Karma

gcusello
SplunkTrust
SplunkTrust

Sort command works on datefield field as a string so 01.10.2016 comes before 17.09.2016.
Insert the sort command between the two eval commands and sort by epochtime.
Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

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