How do I exclude events based on lookup file contents?

[bt149]

I have a lookup file that has 5 columns.  Those are src_ip, dest_ip, dest_port, signature and active.

src_ip has 18 values while the dest_ip has 50 values.  Signature is based on the dest_ip field, meaning 30 of the dest_ip we'll see a signature named "ssh login."  The other 20 sigs will be "ftp login."  sigs that are "ssh login" will always be dest_port=22 and sig "ftp login" will always be dest_port=21.

The src_ip can hit any of the destinations / dest_ports / signatures.

I've tried this in my search but it falls short of adding in the src_ip against all the dest_ip.

| inputlookup exclusion_list.csv | fields src_ip dest_ip dest_port signature | format | table search

The issue I'm seeing is once the search gets to a row in the lookup file that doesn't contain a src_ip it doesn't add on to the results.  So in essence I end up with 18 line that have:

( (dest_ip=xxxx AND dest_port=22 AND signature=xxx AND src_ip=yyyy) OR (dest_ip=xxxx AND dest_port=22 AND signature=xxx) )

I can't figure out how to make the command sedn the src_ip's to all the dest_ip / dest_port / signature combos.

This is hard to write out what I want but hopefully there is some help out there.  Thanks in advance.

[VatsalJagani]

If you want to map all src_ip with all the combo's of dest_ip, dest_port, and signature, try them as two sub-search separately and search it.

<your search>
[| inputlookup exclusion_list.csv | fields src_ip | format | table search]
[| inputlookup exclusion_list.csv | fields dest_ip dest_port signature | format | table search]

If you want to exclude this then you may need to try something like:

<your search>
NOT (
[| inputlookup exclusion_list.csv | fields src_ip | format | table search]
[| inputlookup exclusion_list.csv | fields dest_ip dest_port signature | format | table search])

[bt149]

Unfortunately this does not work.  The issue I'm running into is the rows where a src_ip does not exist, because there is only ~18 src_ip and there are ~50 dest_ip / dest_port / signature, anything I'm doing stops at the ~19 line.  I'm looking to have any src_ip with any dest_ip/port/signature combo.

[VatsalJagani]

Can you please give show what your lookup has and what your _raw data has? So I can help you further.

Just make changes in the IP addresses or mask if require.

[PickleRick]

I don't quite follow what you want as a result. A subsearch will return a formatted complex condition based on the list of fields present in each result row separately.

So if you have a lookup that has fields f1, f2 and f3 and only some of them have values yoh should get something along the like of  ((f1=x) OR (f2=x AND f3=x) OR (f2=x) OR ...).

If you need to fil empty fields with some value, you can do eval with coalesce() or simply fillnill.