How do you use multiple thresholds from a timechart for a single alert for an arbitrary number of devices?

[michaelsplunk1]

_time	device1_avg	device2_avg	device3_avg	device4_avg
2022-04-07 00:00	34	3	11	22
2022-04-07 01:00	21	76	41	87
2022-04-07 02:00	2	18	32	32
2022-04-07 03:00	12	3	36	54
2022-04-07 04:00	7	8	21	43
2022-04-07 05:00	11	3	17	21
2022-04-07 06:00	19	12	19	16
2022-04-07 07:00	15	10	12	19
2022-04-07 08:00	4	2	19	6

I have a table of averages for an arbitrary number of arbitrary devices as shown above. How do I use these averages as thresholds for alerts about these devices? I'm trying to have a search that runs every 15 minutes to check which devices have exceeded these averages.

For example, if a search is run at 06:45, and returns that device1 has a count of 10, device2 has a count of 15, device3 has a count of 21, and device 4 has a count of 2, send an alert that says device2 and device3 have exceeded their averages listed for the 06:00 hour (i.e., 12 and 19, respectively).

[richgalloway · Accepted solution]

Here's an untested idea.  Round the runtime of the search to the beginning of the hour.  Look up the result in the _time field of the averages table, returning all device thresholds for that hour.  Compare the calculated average to the threshold and trigger an alert if the result count is not zero.

```Round the runtime of the search to the beginning of the hour. ```
| eval lookupTime=relative_time(_time, "@h")
```Look up the result in the _time field of the averages table, returning all device thresholds for that hour.  ```
| lookup averages.csv _time OUTPUT device1_avg AS device1_thresh, device2_avg AS device2_thresh, device3_avg AS device3_thresh, device4_avg AS device4_thresh
```Compare the calculated average to the threshold```
| where (device1_avg > device1_thresh OR device2_avg > device2_thresh OR device3_avg > device3_thresh OR device4_avg > device4_thresh)