Splunk Search

How to use rex field as subsearch input value

hoyomi
Explorer

My main search will extract a rex field. I want to use this rex field value as a search input in my subsearch so that I can join 2 results together.

My search is like below:

index=uat sourcetype=log_layer_1 "login handle: ABCDEFG" | rex field=source "api\.(?[^\.]+)" | dedup SESSION_ID | table SESSION_ID platform_info| join SESSION_ID [search index=uat soucetype=log_layer_2 **source=*SESSION_ID*** "userID=" | rex field=source "log\.(?[^\.]+)" | rex field=_raw "userID=(?[^ ]+)" |dedup SESSION_ID, USER_ID | table SESSION_ID USER_ID] | table SESSION_ID USER_ID platform_info

Sourcetype log_layer_1 does not have user ID. Sourcetype log_layer_2 does not have field platform_info.

My question is how can I use the SESSION_ID from main search, as a condition value input in the subsearch (bold part)

Tags (3)
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

The direct answer to the question is, use map instead of join, and pass the values you are searching for to the mapped search.

index=uat sourcetype=log_layer_1 "login handle: ABCDEFG" 
| rex field=source "api.(?<SESSION_ID>[^\.]+)" 
| rex field=_raw "login handle:\s+(?<handle>[^\s]+)"
| dedup SESSION_ID 
| table SESSION_ID platform_info handle
| map  search="search index=uat sourcetype=log_layer_2 source=*$SESSION_ID$* \"userID=\" 
       | rex field=source \"log.(?<SESSION_ID>[^\.]+)\" 
       | rex field=_raw \"userID=(?<USER_ID>[^\s]+)\" 
       | dedup SESSION_ID, USER_ID 
       | table SESSION_ID USER_ID
       | eval platform_info=\"$platform_info$\"
       | eval login_handle=\"handle\"
       | table SESSION_ID USER_ID platform_info login_handle"

If you are doing it for more than one user, other than as a one-shot, it's generally going to be more efficient to do something like this, with no join involved at all...

index=uat   (sourcetype=log_layer_2 "userID=" ) OR
    (sourcetype=log_layer_1 AND "login handle: ABCDEFG")
| rex field=source "^(?:api|log).(?<SESSION_ID>[^\.]+)" 
| rex field=_raw "userID=(?<USER_ID>[^\s]+)" 
| rex field=_raw "login handle:\s+(?<handle>[^\s]+)"
| stats values(USER_ID) as USER_ID, values(platform_info) as platform_info, values(handle) as login_handle by SESSION_ID

My assumptions are as follows. Given that

session = "a1b2c3"  
userid  = "user1" 
platform = "plat1"
handle="ABCDEFG"

Your source records for log_layer_1 look like this

sourcetype=log_layer_1   source=api.a1b2c3.something  platform_info=Plat1 _raw="somewhere in the _raw is login handle: ABCDEFG plus stuff"

Your source records for log_layer_2 look like this

sourcetype=log_layer_2   source=log.a1b2c3.something  _raw="somewhere in the _raw is userID=user1 plus stuff"

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

The direct answer to the question is, use map instead of join, and pass the values you are searching for to the mapped search.

index=uat sourcetype=log_layer_1 "login handle: ABCDEFG" 
| rex field=source "api.(?<SESSION_ID>[^\.]+)" 
| rex field=_raw "login handle:\s+(?<handle>[^\s]+)"
| dedup SESSION_ID 
| table SESSION_ID platform_info handle
| map  search="search index=uat sourcetype=log_layer_2 source=*$SESSION_ID$* \"userID=\" 
       | rex field=source \"log.(?<SESSION_ID>[^\.]+)\" 
       | rex field=_raw \"userID=(?<USER_ID>[^\s]+)\" 
       | dedup SESSION_ID, USER_ID 
       | table SESSION_ID USER_ID
       | eval platform_info=\"$platform_info$\"
       | eval login_handle=\"handle\"
       | table SESSION_ID USER_ID platform_info login_handle"

If you are doing it for more than one user, other than as a one-shot, it's generally going to be more efficient to do something like this, with no join involved at all...

index=uat   (sourcetype=log_layer_2 "userID=" ) OR
    (sourcetype=log_layer_1 AND "login handle: ABCDEFG")
| rex field=source "^(?:api|log).(?<SESSION_ID>[^\.]+)" 
| rex field=_raw "userID=(?<USER_ID>[^\s]+)" 
| rex field=_raw "login handle:\s+(?<handle>[^\s]+)"
| stats values(USER_ID) as USER_ID, values(platform_info) as platform_info, values(handle) as login_handle by SESSION_ID

My assumptions are as follows. Given that

session = "a1b2c3"  
userid  = "user1" 
platform = "plat1"
handle="ABCDEFG"

Your source records for log_layer_1 look like this

sourcetype=log_layer_1   source=api.a1b2c3.something  platform_info=Plat1 _raw="somewhere in the _raw is login handle: ABCDEFG plus stuff"

Your source records for log_layer_2 look like this

sourcetype=log_layer_2   source=log.a1b2c3.something  _raw="somewhere in the _raw is userID=user1 plus stuff"

hoyomi
Explorer

Thank you, DalJeanis. This is very promising.

You assumption is correct. Unfortunately, I cannot use your "more efficient" search because we format our log in a way that on log_layer_2, log files are separated by each individual user sessions. Thus, it relies on the SESSION_ID from log_layer_1 to narrow down the search. Otherwise, search for data in the past 30 days can be extremely slow.

Regarding your first search string, somehow, it doesn't work as expected. Even if I trim the search to below, the log entries with "userID=" does not return in the results. In fact, the returned results are way less than what it should be by running the mapped search with a real SESSION_ID plugged in directly.

index=uat sourcetype=log_layer_1 "login handle: ABCDEFG" 
 | rex field=source "api.(?[^\.]+)" 
 | rex field=_raw "login handle:\s+(?[^\s]+)"
 | dedup SESSION_ID 
 | map  search="search index=uat sourcetype=log_layer_2 source=*$SESSION_ID$*"

Any suggestion on what may be wrong?

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Try these two ways :

| map  search="search index=uat sourcetype=log_layer_2 source='*$SESSION_ID$*'"

| map  search="search index=uat sourcetype=log_layer_2 source=\"*$SESSION_ID$*\""

If those don't work, then back up and get one particular value for SESSION_ID and try these, one by one, until you get a result...

 | makeresults | eval SESSION_ID="TheSessionID" | table SESSION_ID
 | map  search="search index=uat sourcetype=log_layer_2 source=TheSessionID"

 | makeresults | eval SESSION_ID="TheSessionID" | table SESSION_ID
 | map  search="search index=uat sourcetype=log_layer_2 source='TheSessionID'"

 | makeresults | eval SESSION_ID="TheSessionID" | table SESSION_ID
 | map  search="search index=uat sourcetype=log_layer_2 source=\"TheSessionID\""

 | makeresults | eval SESSION_ID="TheSessionID" | table SESSION_ID
 | map  search="search index=uat sourcetype=log_layer_2 source=\"$SESSION_ID$\""

 | makeresults | eval SESSION_ID="TheSessionID" | table SESSION_ID
 | map  search="search index=uat sourcetype=log_layer_2 source=\"*$SESSION_ID$*\""

I don't believe the asterisks should be needed for this, since you have the actual SESSION_ID value. We are just seeking the right search to go in the map command, and once it works, you can go back to the full search.

0 Karma

hoyomi
Explorer

Double quote with escape is the answer. Btw, I actually need the asterisks because log for one SESSION_ID can be split into multiple files, depending on the size.

Problem solved. Thank you so much.

DalJeanis
SplunkTrust
SplunkTrust

Heh, so of course, it's the last one I posted...

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...