Select most recent event where other fields match criteria

[danjone3]

Given a list of ticket entries, I'd like to generate a list of unique ticket IDs that are not resolved, the most recent time they were updated, and the most recent time they were updated by something other than the "System" user.
Essentially what I'd like to do is combine the following two searches:

ticket_status!=resolved | eval latest(ticket_edit_date) by ticket_id

ticket_status!=resolved ticket_update!="System" | eval latest(ticket_edit_date) by ticket_id

Perhaps something along the lines of this invalid search:

ticket_status!=resolved ticket_update!="System" | eval latest(ticket_edit_date) as LastUpdate latest(ticket_edit_date) where ticket_update!="System"  as LastHumanUpdate by ticket_id

[DalJeanis · Accepted solution]

Okay, so you want ticket_ids that are not resolved. That is this...

index=foo | dedup ticket_id | where ticket_status!="resolved" | table ticket_id

Put that code in brackets at the beginning of your search, and it amounts to giving the search the list of what tickets you care about in this format:

 ( ( ticket_id="firstid" ) OR ( ticket_id="secondID ) OR ... )

Now, for those tickets, we want the most recent update time of any kind, and the most recent update that is not System. We don't know what your date format is, so we'll assume it's not epoch or sortable ("%Y-%m-%d %H:%M:%S"). It's not generally efficient to try to limit the individual events one at a time, it's easier to just collect them and roll them together.

index=foo [index=foo | dedup ticket_id | where ticket_status!="resolved" | table ticket_id ]
| eval AllUpdate = strftime(ticket_edit_date,"whatever the date format is")
| eval HumanUpdate = if(ticket_update!="System",AllUpdate,null())
| stats max(AllUpdate) as LastUpdate max(HumanUpdate) as LastHumanUpdate by ticket_id  

| rename  COMMENT as "Now we reformat the results to be displayable."
| eval LastUpdate=strftime(LastUpdate,"%Y-%m-%d %H:%M:%S")
| eval LastHumanUpdate=strftime(LastHumanUpdate,"%Y-%m-%d %H:%M:%S")