Assumptions
You say you will make a lookup table from a csv table, but then in the example query you posted you have index=timetable
That what you want out of this is to list the data in work day and append the timetable data
That the time table data represents the scheduled time (really just affects that way I named things in the final solution)
I'm going to use two source types, but you could easily swap one or both of them with csv data.
Timetable
I'm going to start with timetable, I indexed the following data as sourcetype timetable
user;monday_work_hours;tuesday_work_hours;wednesday_work_hours;thursday_work_hours;friday_work_hours;saturday_work_hours;sunday_work_hours
userA; 8.00-12.00;13.00-19.00;12.00-16.00;12.00-16.00;12.00-16.00;12.00-16.00;12.00-16.00;
userZ; 7.00-12.00;11.00-19.00;12.00-19.00;;;;7.00-19.00
I then want to get the latest schedule per user. If you're doing a csv, it may only have the current data, but this way I can update one users schedule and get the correct result. If I really wanted to do this, I would use stream stats to line up the two sourcetypes so that I could process this on historical data.
sourcetype=timetable | stats latest(*_work_hours) as * by user
This gives me a table with user and then the days of the week.
Work Day
I then queried to get the work day data ( sourcetype=answers_work_day ). I then joined this to the query I previously ran ( sourcetype=timetable | stats latest(*_work_hours) as * by user ) joining on user
sourcetype=answers_work_day
| join user [search sourcetype=timetable | stats latest(*_work_hours) as * by user]
Getting Something Useful
The next step is to use the weekday field from the answers_work_day data to pick the correct work schedule. The way I did this (apologize caffeine hasn't kicked in yet) was to use a really ugly case statement the compares the value of weekday to the known weekdays. You could use lower to make this a bit more reliable.
[...] | eval scheduledhours=case(
weekday="monday", monday,
weekday="tuesday", tuesday,
weekday="wednesday", wednesday,
weekday="thursday", thursday,
weekday="friday", friday,
weekday="saturday", saturday,
weekday="sunday", sunday
1=1,"fixme")
Finally, I added a table so you'd see just the columns that you want (see below)
Final Solution
sourcetype=answers_work_day
| join user [search sourcetype=timetable | stats latest(*_work_hours) as * by user]
| eval scheduledhours=case(
weekday="monday", monday,
weekday="tuesday", tuesday,
weekday="wednesday", wednesday,
weekday="thursday", thursday,
weekday="friday", friday,
weekday="saturday", saturday,
weekday="sunday", sunday)
| table user weekday worktime scheduledhours
While this solution works, I try to avoid subqueries. If you're honestly using a lookup for timetable, the subquery method would work. If you use join user [ inputlookup "timetable" [...] ] , then hopefully this meets your needs.
The problem I have is what if you have historical data and schedules have changed over time. Typically you can avoid using sub-queries by using stats to combined rows. You may be able to use streamstats then to handle the case of changing schedules, but as I said I haven't had caffeine yet this morning so an obvious solution is escaping me and I've already sank more time then I should have into this answer.
... View more