Using a resulting time from one query as the latest time in another query

[Traer001]

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?

[tscroggins]

@Traer001 

Is the second search expected to return more than one event? If yes, you'll need reduce the results to one _time value, e.g.:

| tstats latest(_time) as latest_time where index=INDEX host=HOSTNAME sourcetype=SECOND_SOURCETYPE

After that, you can plug this into your base search:

index=INDEX host=HOSTNAME sourcetype=FIRST_SOURCETYPE
[| tstats latest(_time) as latest_time where index=INDEX host=HOSTNAME sourcetype=SECOND_SOURCETYPE ]
| rex field=_raw "User:\s(?<user_id>\d+)\s\(LeaveRoom\):\s(?<room_id>\d+)"
...

The inner search produces one event with one field, latest_time. The result of the subsearch is inserted into the parent search as a set of predicates, e.g.:

index=INDEX host=HOSTNAME sourcetype=FIRST_SOURCETYPE ( latest_time=1617567041 )