Splunk Search

Is it possible to make conditional lookup ?

Super Champion

Hello,

I would like to make a view that allows me to see which product is being queried the most by my clients.

So my index contains a list of products with two main columns: Product and Description. I have a list of keywords in a lookup file and these keywords should in some cases be compared to products and in other cases to the description using wildcard lookup.

The lookup file is made out of two columns: Keyword , Match (the match column can be used to indicate whether this keyword should be compared with products or with descriptions.).

Is it possible to make a conditional lookup and in the case Match points towards a product the comparison would be done with a product and in the other case the lookup will apply on the description field ?

To make the question clear:
Index contains : Column 1 and 2
Lookup contains : Column A and B

Column B is the condition that define whether the lookup should apply to column 1 or 2 and column A will be used to do that lookup.

Anyone has an idea how that can be done ?

Thanks for you help.
Regards,
David

0 Karma
1 Solution

Esteemed Legend

Given this part of your clarification:

Ether FruitType or FruitDescription can match at a single time never both. Also, both are in the same lookup file.

Then this should work because even when the 1st lookup matches, the 2nd lookup (because it fails to match) will not null out the non-null result of the 1st lookup; I don't see any better way to do it:

index=TutiFuity | lookup FruitInfo.csv FruitType AS Fruitname | lookup FruitInfo.csv FruitDescription AS Fruitname

View solution in original post

Splunk Employee
Splunk Employee

Based on what you've written, there may be some newer Splunk features that could help you.

First, if your lookup table originated as a database table, you could keep it in the database and use DBConnect to query the rows in the table appropriate to the search.

Two options:
https://splunkbase.splunk.com/app/958/
https://splunkbase.splunk.com/app/2686/

Another option is to use Splunk's new features: KV Store. You can think of it as sort of Splunk's take on an internal database. It has several advantages over lookup tables including being able to handle more data and more quickly.

http://dev.splunk.com/view/webframework-features/SP-CAAAEZK

In both cases (and, in truth, in the case of just plain lookup tables), you can solve your problem by some combination of the following approaches:

  1. Bring in the lookup table and then filter for rows with the "Match" value that you need in that particular search. (So,
  2. Create two lookup tables, one with "Match" for Products, the other with "Match" for Description.

Esteemed Legend

Given this part of your clarification:

Ether FruitType or FruitDescription can match at a single time never both. Also, both are in the same lookup file.

Then this should work because even when the 1st lookup matches, the 2nd lookup (because it fails to match) will not null out the non-null result of the 1st lookup; I don't see any better way to do it:

index=TutiFuity | lookup FruitInfo.csv FruitType AS Fruitname | lookup FruitInfo.csv FruitDescription AS Fruitname

View solution in original post

Super Champion

yes it works ^^ i didn't say it didn't hehe I just wanna do both in a single lookup with some logic added...want to check if that's possible with Splunk 😄

0 Karma

Esteemed Legend

You question as described does not make sense to me but if you give it to me in pseudo-code (logical algorithm), I am sure I will be able to help you. Don't bother mapping it to Splunk context/language; just spell it out more clearly.

Super Champion

Okay here goes nothing , let me know if you need something simpler:

index contains _time, field 1, field 2
Lookup file contains field A, field B

if col A= X
lookup fieldB as field1
if col A=Y
lookup fieldB as field2

So based on A i choose whether i will match the lookup with field 1 or field 2 of the index.

0 Karma

Esteemed Legend

This is still nonsense. What is it that is inside an/each event that allows me to identify a specific row inside the lookup table? In your description, you are describing logic based on a row (with fields "A" and "B") but you have not specified the input field in the event nor the index field inside the table that allows me to isolate that particular row in order to apply your logic. You need to say "do this to isolate/index to a particular row" and then such and such logic.

What it actually sounds like to me is that you need to create a macro with some all-external logic and then have it do the lookup and then do some more logic based on that and then return the proper fields/values.

0 Karma

Super Champion

Thanks for your reply woodcock. There is no specific input field-- this is the main point of the question-- the input field varies based on another field.

In a simple lookup you would match a field from the index to a field in the lookup file. What i want to do is based on whether a field X is equal to 1 or 0 use the lookup on different fields. That would be similar to applying 2 consecutive lookups on two different fields of the index.

I want to do it in one shot to increase the performance of my search.

Hope that makes things clearer. If it's still nonsense i'll elaborate more ^^

0 Karma

Esteemed Legend

You are saying "use an output field to return the input field of a lookup" which is paradoxical nonsense. Give me the 2 lookups (which, BTW, is probably the only way to do it) for your 2-step solution and maybe that will help me understand.

Super Champion

Nice you got it 😄 You are saying "use an output field to return the input field of a lookup" which is paradoxical nonsense ---> I agree with you, this is why it's a question on whether or not it is possible... I want to use that field from the lookup file to decide which field would serve as an input. The field used for the decision doesn't have to be outputted. (Thanks for baring with me ^^)

Ex:
index=TuttyFuity | lookup FruitInfo.csv FruitType AS Fruitname | lookup FruitInfo.csv FruitDescription AS Fruitname

Thing is that either FruitType or FruitDescription can match at a single time never both. Also, both are in the same lookup file. So it's kinda weird to have to go through that file twice don't you think ?

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!