Latest value in ESQL Elasticsearch piped query language on kibana

Hi Guys

We are running below ES|QL query on Kibana to make a table of list of alerts triggered from a external Monitoring tool.

FROM winlogbeat-* | WHERE event.code == "3003" | stats Alert_Time = VALUES(@timestamp), Severity = VALUES(Severity), Name = VALUES(AlertName), ActiveID = VALUES(AlertActiveID), Status = VALUES(AlertStatus),Device_Name = VALUES(Device_Name) by @timestamp

When the new Alert generates, we get an event with “Status” field with value “ACTIVE”
When the Alert resolves we get an event with “Status” field with value as “CLOSED”

But now We want to create a table which shows only the alerts which are still ACTIVE and not resolved. Here only the key value which we can use to correlate the Alert Status is “AlertActiveID” field which is a unique number assigned a new alert when its generates and also same number will be assigned to new event when the same alert resolves.

As per my understanding we need to use something like LATEST(AlertActiveID) in the query but its not working.

Can some one help us with this so that we can get to see the table of alerts which has list on only ACTIVE alerts (not resolved).

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

Yes, this worked for me. Thanks a lot the help.