sort show wrong result

[Paul1896]

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!

[richgalloway · Accepted solution]

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

[Paul1896]

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!

[davebrooking]

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

[Paul1896]

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.

[inventsekar]

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

[Paul1896]

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?

[richgalloway]

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

[cmerriman]

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?

[cmerriman]

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

[Paul1896]

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.

[cmerriman]

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

[inventsekar]

Please try the query on my answer

[inventsekar]

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 

[Paul1896]

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 

[inventsekar]

check what happens with this one

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

[gcusello]

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