I'm working with a system where each event has its own creation timestamp (always the same) and modification timestamp. Currently, I want to get the most recent resolved event to establish the lates...
See more...
I'm working with a system where each event has its own creation timestamp (always the same) and modification timestamp. Currently, I want to get the most recent resolved event to establish the latest modification time and then get the earliest resolved event in order to establish the actual resolve time which I've achieved with the following search query: index=<index name> | where match(status, "resolved") | dedup 1 incident_id sortby -_time
| table incident_id, status, creation_time, resolve_time, modification_time
| appendcols [
search index=<index name> | where match(status, "resolved") | dedup 1 incident_id sortby +_time
| eval resolve_time = modification_time
| reverse
| table resolve_time ]
| eval modification_time = if(resolve_time == modification_time, "Unchanged", modification_time)
| table incident_id, status, creation_time, resolve_time, modification_time It can be hard to visualise this so I've illustrated it below: From the above screenshot, you can see that the incident with ID 1735 was initially resolved on 2020/11/06 at 11:56:27 but was modified twice, once at 11:57:54 and again at 15:02:42. When the search only includes one post-resolution modification, this is exactly what's reported which can be seen in the following screenshot However, the strange thing is that, when there's a second post-resolution modification, the timestamps get skewed into the future - resolve_time is supposed to remain as 2020/11/06 11:56:27.351 but gets changed to 2020/11/06 14:52:13.822. This can be seen in the following screenshot: Through testing, I have: Verified that the two searches work exactly as intended when copy and pasted into standalone searches. Found that all timestamps (_time, creation_time, and modification_time) within the appendcols subsearch are skewed. Even more bizarrely, the timestamps that are outputted aren't mentioned anywhere else. It's almost as if they're a result of some kind of search-time calculation. Why is this happening?