Splunk Search
Highlighted

How to search a lookup table and return the matching term?

Explorer

All-

I am new to Splunk and trying to figure out how to return a matched term from a CSV table with inputlookup. I just researched and found that inputlookup returns a Boolean response, making it impossible to return the matched term.

With that being said, is the any way to search a lookup table and return a matching term? I would imagine eval would do the trick, but I have not been successful in making it work.

I have tried the below:

index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms] | eval matchedterm=if( [|inputlookup aterms.csv | return 10000 $aterms], $aterms)

Thanks for the help!

Highlighted

Re: How to search a lookup table and return the matching term?

Legend

See if this is what you're looking for...

index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | rename aterms AS search | return search]
0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Motivator

Lets say your Lookup table is "inputLookup.csv" and it is as follows:

Field1,Field2
AA,11
AB,22
AC,33
BA,21
BB,22
BC,23

You can match terms from input lookup on any of the above fields Field1 or Field2 as follows (I am matching on Field1 and displaying Field2):

|inputlookup inputLookup.csv | search Field1=A* | fields Field2 

If you now want to use all the Field2 values which returned based on your match Field1=A* as subsearch then try:

yourBaseSearch [ |inputlookup inputLookup.csv | search Field1=A* | fields Field2 ]

Updating as per the comment conversation:

index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms]
 | rename _raw as rawText
 | eval foo=[|inputlookup aterms.csv |stats values(aterms) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
 | eval foo=split(foo,",") 
 | mvexpand foo 
  | eval foo=lower(foo)     
  | eval rawText=lower(rawText)
 | where like(rawText,"%"+foo+"%")
 | table rawText, foo

View solution in original post

Highlighted

Re: How to search a lookup table and return the matching term?

Explorer

my lookup table is a list of hundreds of strings that I am searching against logs. The search works perfect as: index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms] and returns all of the events that have one of the matching strings in my lookup table.

The issue is that I need to display the input table string that was identified in the corresponding event.

0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Motivator

Are you trying to match the strings which get returned from inputlookup to the event strings of outersearch randomly or the outer search will have the text matched randomly but the matched string will happen to be a field value in your outer search. As the latter case might be a good case for lookup command then.

0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Explorer

Yeah, so we are looking for keywords in the outer search, I just need the inner search to identify the keyword that hit. The input lookup only returns the boolean true, so I would need the inner search to identify the keyword in the event that was returned from the input lookup.

0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Motivator
0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Explorer

That post is simply highlighting the matched term, I am looking to add the matched term into a new field.

0 Karma
Highlighted

Re: How to search a lookup table and return the matching term?

Motivator

I put in some research and coming back with solution which does following:

  • Outer search matches your lookup strings in events
  • Rename _raw as rewText so not to lose it downstream
  • Take out all the strings in your lookup in a field called foo
  • Split foo as multivalue field
  • Expand the field foo and match it piecemeal in your rawText.
  • When matched table it out with rawText and foo.

Let me know if it worked so I can update in answer. Hope that is what you were looking for

index=proxysg sourcetype=proxysg_base [|inputlookup aterms.csv | return 10000 $aterms]
| rename _raw as rawText
| eval foo=[|inputlookup aterms.csv |stats values(aterms) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval foo=split(foo,",") 
| mvexpand foo 
 | eval foo=lower(foo)     
 | eval rawText=lower(rawText)
| where like(rawText,"%"+foo+"%")
| table rawText, foo
Highlighted

Re: How to search a lookup table and return the matching term?

Explorer

It worked perfectly!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thanks a million!!