topic Re: Does sort like "order by" in sql for list of fields in Splunk Search

Does sort like "order by" in sql for list of fields?

Jackiifilwhh — Wed, 23 Feb 2022 18:39:25 GMT

Hi, I'm new to Splunk. The question I want to ask is does sort like "order by" in sql for list of fields, which divide into groups first and then sort within group. For example :

no	time
1	2022-01-22 18:00:00.000
2	2022-01-20 18:00:00.000
2	2022-01-26 18:00:00.000
1	2022-01-21 18:00:00.000

When in sql, using command "order by no, time desc", the result is like this :

no	time
1	2022-01-22 18:00:00.000
1	2022-01-21 18:00:00.000
2	2022-01-26 18:00:00.000
2	2022-01-20 18:00:00.000

But in SPL, when I use command "sort str(no), -str(time)", the result is this :

no	time
2	2022-01-26 18:00:00.000
1	2022-01-22 18:00:00.000
1	2022-01-21 18:00:00.000
2	2022-01-20 18:00:00.000

Is sort different from order by in sql or just my command is wrong? Thank you very much for answering my question!

Re: Does sort like "order by" in sql for list of fields

gcusello — Wed, 23 Feb 2022 12:16:49 GMT

Hi @Jackiifilwhh,

you should see the sort command at https://docs.splunk.com/Documentation/SCS/current/SearchReference/SortCommandExamples

you can use the sort command adding a field and not a function, to have the desc, you have to use "-".

in your case

| sort no -time

Ciao.

Giuseppe

Re: Does sort like "order by" in sql for list of fields

Jackiifilwhh — Wed, 23 Feb 2022 12:39:20 GMT

Hi @gcusello

thank you very much!

sort is same as "order by" and I know why it seems doesn't work firstly

I use command "table" first and then use "sort", so it won't work!

when I reverse these two commands, it worked successfully!

Re: Does sort like "order by" in sql for list of fields

tshah-splunk — Wed, 23 Feb 2022 12:45:22 GMT

Hi @Jackiifilwhh,

Can you try using the field directly in the sort function instead of converting it to string? Although, you will need to convert time to string using time converter functions. Below is the sample query you can try out.

| eval Time=strftime(time, "%Y-%m-%d %H:%M:%S.%Q") | table no Time | sort no - Time

Re: Does sort like "order by" in sql for list of fields

Jackiifilwhh — Wed, 23 Feb 2022 12:49:47 GMT

Hi @tshah-splunk

thank you and it worked finally by using table command after sort!

the truth is sort is same as order by in sql!

Re: Does sort like "order by" in sql for list of fields

gcusello — Wed, 23 Feb 2022 13:01:20 GMT

Hi @Jackiifilwhh,

at first put attention to the field names: the are case sensitive ("Time" is different by "time") and if you're using the rename command you have to use the renamed fieldname.

In addition, to have a descendant order, you have to put the "-" attached to the field without spaces, in other words:

| sort - time isn't correct

| sort -time is correct

If you could share your search I could check it.

Ciao.

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

Re: Does sort like "order by" in sql for list of fields

Jackiifilwhh — Wed, 23 Feb 2022 13:21:00 GMT

Hi @gcusello

here is my search. Btw, my Splunk's version is 8.1.4

eval time=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q") | sort 0 no, -time | table no time

Re: Does sort like "order by" in sql for list of fields

gcusello — Wed, 23 Feb 2022 13:24:15 GMT

Hi @Jackiifilwhh.

when you want to sort for a date or a time, it's always better to sort in epochtime, in other words change the order of your commands.

In addition don't use comma in sort command.

| sort 0 no -_time | eval time=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q") | table no time

Ciao.

Giuseppe

Re: Does sort like "order by" in sql for list of fields

Jackiifilwhh — Wed, 23 Feb 2022 13:28:44 GMT

Hi @gcusello

Well, thank you! This is really a better way😁.