WHERE clause using aggregate value, after using aggregate function to get value



  • Official comment
    Matt Sullivan

    once you've done the aggregation pct call, the duration is indeed dropped. You might consider splitting this into two queries.

    First query could be run as a scheduled search to save off the 95th percentile value in a lookup table. Your code above would work fine, just replace last line with these 2:

    | 1 as key
    | save tmp/duration/percentile95 // replace save location with your own, use /shared if others using the data

    Then your code to check for logs that meet the > 95th percentile would looks like this (again replacing lookup location if you changed above to shared/xxx, etc.

    _source="logs source"
    | json "logStream" as logStream
    | json "message" as message
    | parse "@*:" as app
    | parse "duration: *ms" as duration
    | 1 as key
    | lookup percentile from tmp/duration/percentile95 on key=key
    | where duration > percentile

    If you want to make the lookup more generic, you might opt to include multiple percentages in the same line of code prior to the save command, e.g.:

    | pct(duration, 95) as p95, pct(duration, 90) as p90, pct(duration, 75) as p75 //etc

    I hope this helps.

    Comment actions Permalink
  • Avatar
    Khun Khurelbaatar

    Thanks Matt, that worked!

    Comment actions Permalink

Please sign in to leave a comment.