Splunk Search

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

Traer001
Path Finder

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?

Labels (6)
0 Karma

tscroggins
Influencer

@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 )

0 Karma
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...