WHERE clause using aggregate value, after using aggregate function to get value
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))
-
Official comment
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 dataThen 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 > percentileIf 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
Please sign in to leave a comment.
Comments
2 comments