Splunk Search

How to use lookup in subsearch with join

Communicator

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 PROCNAME PROCPARA PROC_TYPE should come from lookup table based on matching PROC_CODE.

0 Karma
1 Solution

Legend

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 PROCCODE must be the same in search and lookup (and it's case sensitive), if it's different, add the option `PROCCODE AS otherPROCCODE`.

Bye.
Giuseppe

View solution in original post

0 Karma

Esteemed Legend

Try this:

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

Motivator

@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
  1. Other way is using lookup as suggested by @gcusello in comments:
    like

    index="testdata" sourcetype="test:data"
    | lookup PROC
    CODE as PROCCODE OUTPUT PROCCODE PROCNAME PROCPARA PROCTYPE
    | table TIMESTAMP SID SYS
    NAME PROCTYPE PROCPARA PROCCODE PROCNAME PROC_VALUE

0 Karma

Legend

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 PROCCODE must be the same in search and lookup (and it's case sensitive), if it's different, add the option `PROCCODE AS otherPROCCODE`.

Bye.
Giuseppe

View solution in original post

0 Karma

Communicator

Thanks @gcusello , it helped me alot.

0 Karma