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

Follow

Khun Khurelbaatar

• April 24, 2019 19:15
• Edited

Hi, I am trying to get list of logs where the time duration value is in the top 95th percentile of all duration values. Here is what I got but it's not accepting this query:

_source="logs source"
| json "logStream" as logStream
| json "message" as message
| parse "@*:" as app
| parse "duration: *ms" as duration | pct(duration, 95) as percentile 
| where duration>percentile

The error I am getting is "Field duration not found, please check the spelling and try again.". 

I tried this, also not working complaining the no definition found for function:

...

| where duration>(pct(duration, 95))

 

0

• Official comment

  

  Matt Sullivan

  • April 26, 2019 20:55
  • Edited

  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.

  Permalink
• 

  Khun Khurelbaatar

  • May 02, 2019 21:47

  Thanks Matt, that worked!

  0

  Permalink

Please sign in to leave a comment.