Splunk Search

How to use lookup in subsearch with join

twh1
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 PROC_NAME PROC_PARA PROC_TYPE should come from lookup table based on matching PROC_CODE.

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

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

vishaltaneja070
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="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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

twh1
Communicator

Thanks @gcusello , it helped me alot.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...