topic Re: Comparing lookup file with an index in Splunk Search

Comparing lookup file with an index

pop345 — Fri, 22 Mar 2024 19:16:37 GMT

I am trying to compare an IP address field called ex_ip thats stored in a lookup file with an index called activity which contains dest, src and a few other fields. I am trying to match the ex_ip from the lookup file with the dest IP from the activity index.

My following query is not resulting in any matches. Any help would be appreciated.

index="activity" 
|lookup activity2 ex_ip as lb OUTPUT ex_ip as match
|eval match=if(LIKE('dest', 'ex_ip'), 1, 0)
|search match=1

Re: Comparing lookup file with an index

marnall — Fri, 22 Mar 2024 19:50:41 GMT

The lookup command should be formulated like this:

| lookup activity2 <col_in_lookup> as <field_in_search> OUTPUT <col_in_lookup> as <field_to_output>

Thus your lookup command should work if the field containing the ip address value in the events is called "lb". But if you want to check if a dest ip is in the lookup, you might try:

| lookup activity2 ex_ip as dest OUTPUT ex_ip as ip_found | search ip_found = *

Re: Comparing lookup file with an index

gcusello — Sat, 23 Mar 2024 11:23:11 GMT

Hi @pop345 ,

if you need to compare an ip addres from a lookup with one or more fields in the index events, you have two choices:

search one by one all the fields (in this example only src and dest, but you can use more fields:

search as full text:

index="activity" [ | inputlookup activity2 | rename lb AS query | fields query ] | ...

with this second solution you search the lookup IPs also outside of the fields.

Ciao.

Giuseppe

Re: Comparing lookup file with an index

pop345 — Mon, 25 Mar 2024 15:40:03 GMT

I've tried this before but wasn't successful in finding any matches, hence I resorted to an eval. Anyway you can expand on the examples you provided? Is there an eval statement or search that I should be using?

Re: Comparing lookup file with an index

gcusello — Mon, 25 Mar 2024 17:32:09 GMT

Hi @pop345,

what's te content od the lb field?

Isupposed that it's an IP address.

Anyway, in the first example, you should rename the lb field name to all the fields in the main search (src, dest).

In the second example, you perform a full text search on _raw.

ciao.

Giuseppe

Re: Comparing lookup file with an index

pop345 — Mon, 25 Mar 2024 17:40:00 GMT

Yes, its an IP address.

Re: Comparing lookup file with an index

pop345 — Mon, 25 Mar 2024 17:44:50 GMT

Do I need do add anything else other than the inputlookup? I am still unsuccessful with getting a match when I know there are a ton.

Re: Comparing lookup file with an index

PickleRick — Mon, 25 Mar 2024 20:36:49 GMT

I'm not sure why you do all this magic after the lookup command.

|lookup activity2 ex_ip as lb OUTPUT ex_ip as match

This will find a row in your lookup table activity2 for which the ex_ip value is equal to the lb value from the event. If such row is found the value from the ex_ip column (in this case it's the same column you searched by - it's a common lookup-verifying technique) is copied to the field called "match" in your result set. If there was no match the 'match' field is left empty.

So if you want to find only those events that matched your lookup you simply filter to find events which have a value in this field

| search match=*

It's that simple.

If you want to match by other field you have to specify other field(s) in your lookup.