Splunk Search

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

Jackiifilwhh
Path Finder

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! 

Labels (3)
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

 

View solution in original post

tshah-splunk
Splunk Employee
Splunk Employee

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

 

 

---
If you find the answer helpful, an upvote/karma is appreciated

Jackiifilwhh
Path Finder

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!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

 

Jackiifilwhh
Path Finder

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!

0 Karma

gcusello
SplunkTrust
SplunkTrust

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 😉

0 Karma

Jackiifilwhh
Path Finder

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

 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Jackiifilwhh
Path Finder

Hi @gcusello 

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

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...