Self Join and override Values

[akshaysaraf]

Need some help with a query

Sample Data:

{
	id: “123”,
	start_time: “2020-08-01 15:00:00”,
	end_time: “2020-08-01 16:00:00”,
	status: “FAIL”
}

{
	id: “124”,
	start_time: “2020-08-01 16:05:00”,
	end_time: “2020-08-01 16:30:00”,
	status: “SUCCESS”,
	original_id: “123”
}

Expected output (in table format) should only have 1 record:

id	start_time	end_time	status
123	2020-08-01 15:00:00	2020-08-01 16:30:00	SUCCESS

This event shows data from id=123, but overrides some field like end_time and Status from latter event.

Goal : When we have data where id=original_id then we override some fields from latter event to former  event.

[to4kawa · Accepted solution]

your_search | spath | eval id = coalesce(original_id,id) | stats earliest(start_time) as start_time latest(end_time) as end_time latest(status) as status by id

[akshaysaraf]

That works, Thank you so much.