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.
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())
Worked like a charm. Tks!
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())