How to use search results in a table in a new search also using row count?

[dt9150813]

I'm still learning Splunk and would like to learn how to combine some searches.

Goal: Use the VPN search results to perform firewall searches according to how many VPN records found.

Example:
1. Search the vpn index to get a table of assigned_ip and the login/logout time:

index=vpn computer_name=Desktop_1 | table assigned_ip login_time logout_time

assigned_ip	login_time	logout_time
10.255.111.112	1728409500	1728459000
10.255.119.199	1728392083	1728401383

2. Use the result above to do a firewall search (I'd like to use results from step 1 instead of the hardcoded values. I also want to append separate rows found in step 1 to find firewall records during different ip assignments):

index=firewall source_ip=10.255.111.112 earliest=1728409500latest=1728459000
| append [ search index=firewall source_ip=10.2555.119.199 earliest=1728392083 latest=1728401383 ] 
| stats count by destination_ip

 
The closest I got so far is using separate subsearch returns, which takes longer to run and doesn't seem to return more than 1 value:

index=firewall
source_ip=[ search index=vpn computer_name=Desktop_1 | return $assigned_ip ]

latest=[ search index=vpn computer_name=Desktop_1 | return $logout_time ] 

earliest=[ search index=vpn computer_name=Desktop_1 | return $login_time] 

| stats count by destination_ip

 
Is there a way to do this? I also tried to use tojson(), but it returns 1 table row into its own json object that I can't use together for the firewall search.

Thank you so much in advance 🙂

[ITWhisperer · Accepted solution]

Try something like this

index=firewall [ search index=vpn computer_name=Desktop_1 | table assigned_ip login_time logout_time | rename login_time as earliest | rename logout_time as latest ] 
| stats count by destination_ip

[dt9150813]

That works! Thank you so much!