[Answering my own question. Any ideas for improvement are welcome] index=...
[| inputlookup ZeroEvents.csv | where Category="custom" | fields event| format]
| stats count as eventscount by event
| append [
| inputlookup ZeroEvents.csv
| eval currentdate=strftime(now(),"%Y-%m-%d")
| lookup NoEventDates.csv NEDate as date OUTPUT NEDate as Holiday
| eval Holiday=if(isnull(Holiday), "N", "Y"), DOW=strftime(now(), "%w"), currentHour=strftime(now(), "%H")
| where Category="custom" AND NOT match(DaysOfWeek, DOW) AND (Holiday="N" OR HolidaysOff="N") AND currentHour >= HourFrom AND currentHour <= HourTo
| fields event
| eval eventscount=0
]
| stats sum(eventscount) as total by events
| where total < 1 The main change is that almost all filtering is done on lookup table itself. Advantages: DRY principle is obeyed (almost) Main lookup logic is isolated and can be debugged on its own In order to test it's necessary to replace 4 variables with desired hard-coded values. Due to late filtering the query will perform extra work, but its performance is still acceptable.
... View more