topic Re: sort show wrong result in Splunk Search

sort show wrong result

Paul1896 — Thu, 13 Oct 2016 14:21:37 GMT

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!

Re: sort show wrong result

gcusello — Thu, 13 Oct 2016 17:01:26 GMT

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

Re: sort show wrong result

inventsekar — Thu, 13 Oct 2016 17:03:03 GMT

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

Re: sort show wrong result

cmerriman — Thu, 13 Oct 2016 17:06:53 GMT

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?

Re: sort show wrong result

cmerriman — Thu, 13 Oct 2016 17:07:56 GMT

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

Re: sort show wrong result

richgalloway — Thu, 13 Oct 2016 17:08:44 GMT

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

Re: sort show wrong result

Paul1896 — Fri, 14 Oct 2016 05:40:12 GMT

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.

Re: sort show wrong result

inventsekar — Fri, 14 Oct 2016 05:44:33 GMT

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

Re: sort show wrong result

Paul1896 — Fri, 14 Oct 2016 05:45:17 GMT

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.

Re: sort show wrong result

Paul1896 — Fri, 14 Oct 2016 05:48:05 GMT

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?

Re: sort show wrong result

inventsekar — Fri, 14 Oct 2016 05:48:14 GMT

Please try the query on my answer

Re: sort show wrong result

Paul1896 — Fri, 14 Oct 2016 05:52:25 GMT

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

Re: sort show wrong result

Paul1896 — Fri, 14 Oct 2016 06:25:29 GMT

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!

Re: sort show wrong result

inventsekar — Tue, 29 Sep 2020 11:25:22 GMT

check what happens with this one

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

Re: sort show wrong result

davebrooking — Fri, 14 Oct 2016 10:17:46 GMT

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

Re: sort show wrong result

cmerriman — Fri, 14 Oct 2016 12:03:56 GMT

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

Re: sort show wrong result

richgalloway — Mon, 17 Oct 2016 12:16:46 GMT

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