ES SQL - Order by Count

[Jose_H]

Hi all,

Im trying to order and display by the highest count for @computed.message field, but encountering the below error:

POST /_sql?format=txt
 {
   "query":"SELECT \"@timestamp\", \"@computed.message\", \"@computed.blocked\", \"sourceIpAddress\" FROM \"csco-fmc-ms\" where \"@timestamp\" > now() - interval 60 minutes AND \"@computed.message\" IS NOT NULL ORDER BY COUNT(*)"
 } 

{
"error" : {
"root_cause" : [
{
"type" : "sql_illegal_argument_exception",
"reason" : "Cannot find backing column for ordering aggregation [org.elasticsearch.xpack.sql.querydsl.container.AggregateSort@da22d608]"
}
],
"type" : "sql_illegal_argument_exception",
"reason" : "Cannot find backing column for ordering aggregation [org.elasticsearch.xpack.sql.querydsl.container.AggregateSort@da22d608]"
},
"status" : 500
}

Any help will be greatly appreciated.

[bogdan.pintea]

The error is a bit cryptic and could probably be improved, but what it tries to say is that there's nothing to count on as requested in the ORDER BY COUNT(*): you need a grouping (i.e. an aggregation) to be counted, if you want to order by its cardinality. So it's an SQL semantical error.

A potential example: SELECT "@computed.message" FROM ... [WHERE ...] GROUP BY "@computed.message" ORDER BY COUNT(*).

[bogdan.pintea]

@Jose_H

POST /_sql?format=txt
 {
   "query":"SELECT \"@timestamp\", \"@computed.message\", \"@computed.blocked\", \"sourceIpAddress\" FROM \"csco-fmc-ms\" where \"@timestamp\" > now() - interval 60 minutes AND \"@computed.message\" IS NOT NULL ORDER BY COUNT(*)"
 } 

Just a tip: if you're using Kibana Console, you can use the triple quotes for the query value, to avoid quotes escaping within the SQL query:

POST /_sql?format=txt
 {
   "query": """SELECT "@timestamp", ...  """
 }