topic Re: How to use lookup in subsearch with join in Splunk Search

How to use lookup in subsearch with join

twh1 — Wed, 30 Sep 2020 01:14:37 GMT

I have requirement to print product details in a table. where i am getting some value from the log and some i have print based on matching product code from lookup table.

I tried below query and I am getting result in proper format.

| inputlookup PROC_DETAIL | table PROC_CODE PROC_NAME PROC_PARA PROC_TYPE | join PROC_CODE [ search index="test_data" sourcetype="test:data" | table TIMESTAMP SID PROC_CODE PROC_VALUE SYS_NAME ] | fields TIMESTAMP SID SYS_NAME PROC_TYPE PROC_PARA PROC_CODE PROC_NAME PROC_VALUE

But when I am expanding time range to see more data, my result count is remaining same, as I am matching the value from lookup table. I want to main my log data as main search and for each event, i want value of PROC_NAME PROC_PARA PROC_TYPE should come from lookup table based on matching PROC_CODE.

Re: How to use lookup in subsearch with join

gcusello — Wed, 30 Sep 2020 01:11:04 GMT

Hi twh1,
if you put a search in subsearch, you have the limit of 50,000 results, so expanding the time range you don't have additional results.
Anyway, the lookup command is like a join command so, rebuild your search inverting the terms.
In addition, you don't need to use the table command in intermediate part of the search.
In other words, try something like this:

index="test_data" sourcetype="test:data" 
| lookup PROC_DETAIL PROC_CODE OUTPUT PROC_CODE PROC_NAME PROC_PARA PROC_TYPE 
| table TIMESTAMP SID SYS_NAME PROC_TYPE PROC_PARA PROC_CODE PROC_NAME PROC_VALUE

beware that the key field PROC_CODE must be the same in search and lookup (and it's case sensitive), if it's different, add the option PROC_CODE AS other_PROC_CODE.

Bye.
Giuseppe

Re: How to use lookup in subsearch with join

vishaltaneja070 — Wed, 30 Sep 2020 01:14:45 GMT

@twh1

I would suggest you two ways here:
1. Use automatic lookup based where for sourcetype="test:data"
in input fields you can mention PROC_CODE and if you want fields from lookup them you can use field value override option.
By using that the fields will be automatically will be available in search
like

 index="test_data" sourcetype="test:data"   | table TIMESTAMP SID SYS_NAME PROC_TYPE PROC_PARA PROC_CODE PROC_NAME PROC_VALUE

Other way is using lookup as suggested by @gcusello in comments:
like

index="test_data" sourcetype="test:data"
| lookup PROC_CODE as PROC_CODE OUTPUT PROC_CODE PROC_NAME PROC_PARA PROC_TYPE
| table TIMESTAMP SID SYS_NAME PROC_TYPE PROC_PARA PROC_CODE PROC_NAME PROC_VALUE

Re: How to use lookup in subsearch with join

twh1 — Fri, 05 Jul 2019 18:49:40 GMT

Thanks @gcusello , it helped me alot.

Re: How to use lookup in subsearch with join

woodcock — Sat, 06 Jul 2019 00:03:07 GMT

Try this:

index="test_data" sourcetype="test:data"
| lookup PROC_DETAIL PROC_CODE OUTPUT PROC_NAME PROC_PARA PROC_TYPE