topic Re: How to create a lookup search for matching 2 fields? in Splunk Search

How to create a lookup search for matching 2 fields?

badoomi — Wed, 12 Jun 2019 07:49:07 GMT

I have 2 devices: fw and waf. I want to make a lookup, my lookup file is mal_ip that has 4 fields :

mal_ip category product,port 
1.1.1.1  mal_ip    firewall  443

I want to say where src=mal_ip show category, the Common field is product and port.
My query is :

index=fw OR index=waf  [ |inputlookup mal_ip | where src=mal_ip| fields category,mal_ip,product,port]|stats count by  src category

But it doesn't match any fields, can you tell me what can I do?

Re: How to create a lookup search for matching 2 fields?

richgalloway — Wed, 12 Jun 2019 13:47:54 GMT

Recall that subsearches run before the main search. Therefore, |inputlookup mal_ip | where src=mal_ip| fields category,mal_ip,product,port must return results. Since there is no 'src' field, the query will not return any results. Try the following variation:

index=fw OR index=waf | lookup mal_ip as src | fields category,mal_ip |stats count by src category

Re: How to create a lookup search for matching 2 fields?

jnudell_2 — Wed, 30 Sep 2020 00:51:28 GMT

Hi Badoomi,

You're using the lookup in the wrong way to achieve your results. @richgalloway is almost right in his answer:

index=fw OR index=waf
| lookup mal_ip mal_ip as src OUTPUT category product port
| stats count by src category

However, if you want to match more than src, and you need to check the product and the port as well it would be written as follows:

index=fw OR index=waf

| lookup mal_ip mal_ip as src product as product port as port OUTPUT category

| stats count by src category

This will match against src, product, and port. But product and port have to be extracted/defined before using the | lookup in that search.

Re: How to create a lookup search for matching 2 fields?

badoomi — Sat, 15 Jun 2019 05:31:16 GMT

it doesn't work and show me this error:
Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table.

Re: How to create a lookup search for matching 2 fields?

badoomi — Sat, 15 Jun 2019 05:35:10 GMT

it doesnt work, i want to compare src from my firewall and waf with mal_ip in my lookup file

Re: How to create a lookup search for matching 2 fields?

jnudell_2 — Sat, 15 Jun 2019 12:46:31 GMT

You'll need to make sure that both the lookup table (CSV file) AND the definition are created.

Please refer to this:
https://docs.splunk.com/Documentation/SplunkCloud/7.2.4/Knowledge/LookupexampleinSplunkWeb

Re: How to create a lookup search for matching 2 fields?

richgalloway — Mon, 17 Jun 2019 12:25:32 GMT

Make sure the fw and waf indexes are returning events with a field called 'src'. If not, add rename or eval statements to create such a field.

Re: How to create a lookup search for matching 2 fields?

woodcock — Mon, 17 Jun 2019 21:26:29 GMT

This assumes that your lookup file is referenced by a lookup definition called mal_ip (if not, replace the first mal_ip value with the correct lookup definition or lookup file😞

index=fw OR index=waf
| lookup mal_ip  mal_ip AS src product port OUTPUT category