How to iterate values inside lookup command and compare the value to event field value

[iamtheclient20]

index=test | table severity location vehicle

severity	location	vehicle
high	Pluto	Bike

testLookup.csv

severity	location	vehicle
high octane	Pluto is one of the planet	Bike has 2 wheels

 
As you can see on my table i have events. Is there a way to compare my table events to my testLookup.csv field values without using lookup command or join command ?
Example. if my table events severity value have matched or has word same as "high" inside the severity in lookup field severity value then it is true otherwise false.

Thank you.

[gcusello]

Hi @iamtheclient20 ,

in general, you can use a subsearch with the inputlookup command, but in your specific cas isn't applicable because in your lookup you have a more detailed field ("high octane"), instead in the text you have only "octane".

the opposite it's possible but bit this use case.

The only workaround, could be use an elaborated field starting from the severity field in the lookup, something like this:

index=test [ | inputlookup testLookup.csv | rex field=severity "^(?<severity>\w+) | fields severity"
| table severity location vehicle

but I don't know if this could meet your requirements.

Ciao.

Giuseppe

[iamtheclient20]

Hi @gcusello, thank you for giving ideas. But is not meet my requirements cause I also need to compare the event field values of location and vehicle as well.

If the event fields severity location vehicle values present inside the lookup field values severity location vehicle it will tag as all 3 to true

Thank you

[gcusello]

Hi @iamtheclient20 ,

if you can define a rule also for the other fields in lookup (e.g. the relevant word is the first in the field value), you could apply the regex approach alto to the other fields, e.g.:

index=test 
[ | inputlookup testLookup.csv 
     | rex field=severity "^(?<severity>\w+)" 
     | rex field=location "^(?<location>\w+)" 
     | rex field=vehicle "^(?<vehicle>\w+)" 
     | fields severity location vehicle
]
| table severity location vehicle

Otherwise, it isn't possible.

let me know if I can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

[iamtheclient20]

Hello @gcusello 

Based on your query, it will return the value from lookup values. Once return the values the index will use it to search the events. What  I am looking for is to check only if the events fields severity location vehicle value is present inside lookup field values.
Anyway, I appreciate your idea.

Thank you.

[gcusello]

Hi @iamtheclient20 ,

you could try to revert the search using the same approach:

| inputlookup testLookup.csv 
| rex field=severity "^(?<my_severity>\w+)" 
| rex field=location "^(?<my_location>\w+)" 
| rex field=vehicle "^(?<my_vehicle>\w+)" 
| search [ search 
     index=test 
     | eval my_severity=severity, my_location=location, my_vehicle=vehicle
     | fields my_severity my_location my_vehicle
     ]
| table severity location vehicle

Remember that there's the limit of 50,000 results in the subsearch.

let me know if I can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[iamtheclient20]

Hi gcusello,

I cannot use this approached because of the limit.

Thank you.

[gcusello]

Hi @iamtheclient20 ,

let me understand:

you want to find the matches between the index and the lookup, in other words the values present both in the lookup and in the events, so, why doesn't my first solution  match your requisite?

It extracts the events from the main search that match the values in the lookup and also the reverse (values in the lookup that match the events in the search).

there could be an issue if you want the NOT match condition, but the match condition is reversable.

Ciao.

Giuseppe