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!
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
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
thank you and it worked finally by using table command after sort!
the truth is sort is same as order by in sql!
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
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!
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 😉
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
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
Hi @gcusello
Well, thank you! This is really a better way😁.