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

Comments

2 comments

  • Official comment
    Avatar
    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.

  • Avatar
    Khun Khurelbaatar

    Thanks Matt, that worked!

Please sign in to leave a comment.