Join and pass value to sub search

[harkirat9712]

HI Team,

I would like to use join to search for "id" and pass it to sub search and need the consolidate result with time.

search 1: searching for value next to "id" provide me list

index=TEST sourcetype=source1  url="/api/v1/test" |  rex "'id':'(?<id>[\d.]+)" | table _time id

Above search gives me integer "id" I will pass in search2.

Search2: 

index=TEST sourcetype=source2 url="/api/*/values(id)/*" Response_Status="200" | table url _time

I need output from search2 referencing id from search 1 

[bowesmana]

Subsearches run before the outer search, so what you want to do is not possible.

https://docs.splunk.com/Documentation/Splunk/8.0.4/Search/Aboutsubsearches

However, you can do the outer search which collects both data sets and then picks the rows wanted

index=TEST (sourcetype=source1 OR sourcetype=source2) (url="/api/v1/test" OR (url="/api/*/values(id)/*" AND Response_Status="200"))
| rex "'id':'(?<id>[\d.]+)"
| eval matchUrl="\/api\/[^\/]*\/values\(".id."\)\/.*"
| where (url="/api/v1/test" OR match(url,matchUrl))
| table _time id url

or you could do a join where you  

index=TEST sourcetype=source1  url="/api/v1/test" 
| rex "'id':'(?<id>[\d.]+)" 
| table _time id
| join max=0 id [
  search index=TEST sourcetype=source2 url="/api/*/values(*)/*" Response_Status="200" 
  | rex field=url "\/api\/[^\/]*/values\((?<id>[\d.]+)\)\/"
  | rename _time as sub_time
  | table id sub_time url 
]

I have not tested either search as I don't have a suitable data set. The join will give you a different output where the second searches are joined together with the parent, whereas the first will give you interleaved rows, but of course you can aggregate with stats.

The join approach can be difficult to debug, as there are limitations on join subsearches, particularly if your data set is large.

 Hopefully this gives you a start.

[harkirat9712]

Small correction:

url in Search2: 

url="/api/*/value-of-id-from-search-1/*"

for example:

/api/run1/2982989/done*

index=TEST sourcetype=source2 url="/api/*/values-of-id/*" Response_Status="200" | table url _time

I am now stuck on . Could you please correct below?

in answer-1 

eval matchUrl="\/api\/[^\/]*\/values\(".id."\)\/.*"

in answer-2

rex field=url "\/api\/[^\/]*/values\((?<id>[\d.]+)\)\/"

[bowesmana]

 Hi @harkirat9712 

So if I understand the data format correctly, the combined search could be

index=TEST (sourcetype=source1 OR sourcetype=source2) (url="/api/v1/test" OR (url="/api/*/*/*" AND Response_Status="200"))
| rex "'id':'(?<id>[\d.]+)"
| eval matchUrl="\/api\/[^\/]*\/".id."\/.*"
| where (url="/api/v1/test" OR match(url,matchUrl))
| table _time id url

and the rex for the second would be

index=TEST sourcetype=source1  url="/api/v1/test" 
| rex "'id':'(?<id>[\d.]+)" 
| table _time id
| join max=0 id [
  search index=TEST sourcetype=source2 url="/api/*/*/*" Response_Status="200" 
  | rex field=url "\/api\/[^\/]*/(?<id>[\d.]+)\/"
  | rename _time as sub_time
  | table id sub_time url 
]

 I hope this is what you need. I have just removed the values() part of what I thought was in the data, so now it's just looking for the id between the //

[isoutamo · Accepted solution]

Does the command map https://docs.splunk.com/Documentation/Splunk/7.3.3/SearchReference/Map help you?