How to select the newest rows in an ordered result set?

[fgjensen (Flemming G. Jensen)]

Hello

I have an annoying problem with Elasticsearch SQL I hope someone can help me with. Elasticsearch is version 7.17.0 and it will soon be upgraded to 8.0.0.

We collect package data from Linux hosts with the auditbeat system module. The module is configured to send the current packages data each 12 hours. I am showing the package names, versions and description for a subset of the packages. The number of packages shown per host is different, but this no problem with an in clause.

The problem appears when a host is updated with new packages then the simple approach with

and "@timestamp" > NOW() - INTERVAL 12 HOUR

would work, because of two rows per package.

My next solution was to query for the timestamp of each package, grouping and ordering. The newest package is then always the first and the oldest the next for a given package name.

The I though I was home free with an outer select after

where "@timestamp" = MAX("@timestamp)

That does not work. I have also tried to CAST to LONG but the error message is the same

{
  "error" : {
    "root_cause" : [
      {
        "type" : "ql_illegal_argument_exception",
        "reason" : "Line 39:20: Comparisons against fields are not (currently) supported; offender [max(timeconv)] in [==]"
      }
    ],
    "type" : "ql_illegal_argument_exception",
    "reason" : "Line 39:20: Comparisons against fields are not (currently) supported; offender [max(timeconv)] in [==]"
  },
  "status" : 500
}

Any suggestions would be appreiciated.

Best regards
Flemming