Latest value in ESQL Elasticsearch piped query language on kibana

Elastic Stack Kibana
, ,
2

Making few strong assumption here before proposing a query solution:

  • alerts can only go, in order, from active to closed
  • alerts cannot be re-opened once closed

If that holds true, I propose the following:

  • aggregate by AlertActiveID first and count how many alerts are within
  • filter all those entries with more than 1 alert with that id
  • gives that all those fields aggregated by values at this point will have only 1 single value, use mv_first to go back to unpack them
  • now re-aggregate by time and sort
FROM winlogbeat-* 
| WHERE event.code == "3003" 
| STATS count = count(), Alert_Times = VALUES(@timestamp), Severities = VALUES(Severity), Names = VALUES(AlertName), Statuses = VALUES(AlertStatus), Device_Names = VALUES(Device_Name) by AlertActiveID
| WHERE count == 1
| EVAL Alert_Time = MV_FIRST(Alert_Times), Severity = MV_FIRST(Severities), Name = MV_FIRST(AlertName), Status = MV_FIRST(Statuses), Device_Name = MV_FIRST(Device_Names), ActiveID = AlertActiveID
| STATS BY Alert_Time, Severity, Name, ActiveID, Status, Device_Name
| SORT Alert_Time DESC
1 Like