topic Re: Join and pass value to sub search in Splunk Search

Join and pass value to sub search

harkirat9712 — Wed, 24 Jun 2020 23:12:37 GMT

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

Re: Join and pass value to sub search

bowesmana — Thu, 25 Jun 2020 00:10:43 GMT

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.

Re: Join and pass value to sub search

harkirat9712 — Thu, 25 Jun 2020 07:39:58 GMT

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.]+)\)\/"

Re: Join and pass value to sub search

isoutamo — Thu, 25 Jun 2020 16:34:44 GMT

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

Re: Join and pass value to sub search

bowesmana — Thu, 25 Jun 2020 21:57:18 GMT

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 //