How find the first event of type A after the last event of type B?

[gtamaki]

I'm trying to extract a report for devices in my network. Home assistant sends a log record with a value of 1 when a device is present and 0 when it's not, but sometimes it loses the record of the devices. On the sample data below, I need to find the first (value=0) occurrence after the last (value=1) for each "Friendly Name" per day. Can someone help me with this, please ?

TimeStamp	Friendly Name	value
9/3/22 12:48	User B	0
9/3/22 11:58	User B	0
9/3/22 10:32	User B	1
9/3/22 10:27	User B	0
9/3/22 7:44	User B	1
9/3/22 7:22	User B	1
9/3/22 0:15	User B	0

In this case I need the second record.

[gtamaki]

Worked like a charm. Tks!

[ITWhisperer · Accepted solution]

There might be an easier way to do this, but try something like this:

| eval day=relative_time(_time,"@d")
| fieldformat day=strftime(day,"%F")
| sort _time
| streamstats current=f global=f latest(value) as previous by day Friendly_Name
| eval previous=if(previous=1,previous,null())
| sort - _time
| streamstats sum(previous) as previous by day Friendly_Name
| eval flag=if(value=0 AND previous=1,"true",null())