Hello! I am having trouble with a query where I want the results to depend on the time results of another query. This first large query shows the total duration (reserving and occupying) that a user...
See more...
Hello! I am having trouble with a query where I want the results to depend on the time results of another query. This first large query shows the total duration (reserving and occupying) that a user is/was in the current room and last room. index=INDEX host=HOSTNAME sourcetype=FIRST_SOURCETYPE | rex field=_raw "User:\s(?<user_id>\d+)\s\(LeaveRoom\):\s(?<room_id>\d+)" | rex field=_raw "User:\s(?<user_id>\d+)\sChooseRoom\s(?<room_id>\d+)" | eval action=if(like(_raw, "%LeaveRoom%"), "LeaveRoom", (if(like(_raw, "%ChooseRoom%"), "ChooseRoom", null))) | where isnotnull(action) | eval late=if(action="LeaveRoom", _time, null) | streamstats latest(late) as _time earliest(_time) as early_time values(action) as actions by room_id user_id | where mvcount(actions)=2 | stats latest(room_id) as last_room_id latest(room) as last_room latest(_time) as left_room_time latest(early_time) as chosen_room_time by user_id | eval last_occupation_duration=tostring((left_room_time - chosen_room_time), "duration") | eval last_chosen_time=strftime(chosen_room_time, "%Y-%m-%d %H:%M:%S") | eval last_left_time=strftime(left_room_time, "%Y-%m-%d %H:%M:%S") | fields - left_room_time, chosen_room_time | join user_id [search index=INDEX host=HOSTNAME sourcetype=FIRST_SOURCETYPE | rex field=_raw "User:\s(?<user_id>\d+)\s\(LeaveRoom\):\s(?<current_room_id>\d+)" | rex field=_raw "User:\s(?<user_id>\d+)\sSelected\s(?<current_room_id>\d+)" | eval action=if(like(_raw, "%ChooseRoom%"), "ChooseRoom", null) | where isnotnull(action) | sort user_id _time | eventstats latest(current_room_id) as latest_room by user_id | streamstats count as count_value by user_id current_room_id reset_on_change=true | where current_room_id=latest_room AND count_value=1 | stats latest(_time) as chosen latest(current_room) as current_room by user_id current_room_id | eval current_occupation_duration=tostring((now() - chosen), "duration") | eval current_chosen_time=strftime(chosen, "%Y-%m-%d %H:%M:%S") | fields - chosen] | table user_id, current_room, current_room_id, current_chosen_time, current_occupation_duration, last_room, last_room_id, last_chosen_time, last_left_time, last_occupation_duration Now I want to use the times in this following query as the latest times that the above query checks for. I want the variable_time to be the latest value. Once I do this I'll be able to check if there was already a user in the room at the time in this second query. Then I think I can use a where clause to compare if the next location in this second query had a user in it based on results from the first query. index=INDEX host=HOSTNAME sourcetype=SECOND_SOURCETYPE | rex field=_raw "UserId:\s(?<user_id>\d+)\scurrent\slocation:\s(?<current_loc>\w+)\snext\slocation:\s(?<next_loc>\w+)" | eval variable_time=_time | table user_id, current_loc, next_loc, variable_time How would I be able to use that variable_time as the latest time in the first query?