How to create a lookup search for matching 2 fields?

[badoomi]

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?

[woodcock]

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

[jnudell_2]

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.

[badoomi]

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.

[jnudell_2]

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

[richgalloway]

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

[badoomi]

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

[richgalloway]

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.