Splunk Search

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

iamtheclient20
Explorer

 

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.

Labels (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

iamtheclient20
Explorer

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Explorer

Hi gcusello,

I cannot use this approached because of the limit.

Thank you.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...