Comparing lookup file with an index

[pop345]

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

[PickleRick]

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.

[gcusello]

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:

index="activity" 
([ | inputlookup activity2 | rename lb AS src | fields src ] OR
[ | inputlookup activity2 | rename lb AS dest | fields dest ])
| ...

 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

[pop345]

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?

[gcusello]

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 

[pop345]

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.

[pop345]

Yes, its an IP address.

[marnall]

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 = *