topic Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits? in Dashboards & Visualizations

What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

reswob4 — Mon, 28 Sep 2020 18:14:48 GMT

Here's what I'm trying to do: I have multiple lists of bad IPs, each from a different source, each set up as a lookup. Two are automatically updated and one is manually updated. The lists are stored in files called badip1.csv, badip2.csv and badip3.csv. I have a dashboard that shows if any of the IPs were found, but I'm not sure if this is the most efficient method.

Here is the search that creates the dashboard:

sourcetype=IPS [inputlookup additionalbadips | fields src] OR [inputlookup malwareips | fields src] | table source_host,source_address

Is there a more efficient search?

As a bonus, it would be nice for the results to show which lookup found which IP.

Thoughts, suggestions?

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

martin_mueller — Tue, 25 Nov 2014 22:48:37 GMT

That's a pretty efficient way of doing that, as long as you're not breaking the search with millions of bad IPs.

As for listing where it came from, you could apply each lookup after the search and add a field to the data. That field has to exist in your lookup, so for example you would have this in one file:

src      lookup
1.2.3.4  additionalbadips

And in the other file:

src      lookup
2.3.4.5  malwareips

After adding the lookups to the search regularly you should see a field "lookup" filled appropriately.

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

musskopf — Tue, 25 Nov 2014 23:01:24 GMT

Another option is to split your search and use append. Something like that:

sourcetype=IPS [inputlookup additionalbadips | fields src] | eval lookup="BadIP" |
append [
 search sourcetype=IPS [inputlookup malwareips | fields src] | eval lookup="Malware"
 ]
| table source_host,source_address, type

Cheers

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

martin_mueller — Tue, 25 Nov 2014 23:12:12 GMT

That should be much slower than one large search though.

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

musskopf — Tue, 25 Nov 2014 23:26:32 GMT

Yes, it might be, but there's no need to run a lookup after the search which might balance things 🙂

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

martin_mueller — Tue, 25 Nov 2014 23:33:37 GMT

The lookup at the end will be a very quick in-memory operation. Much quicker than going through all the buckets multiple times on disk.

Also doesn't throw up duplicates and isn't constrained by subsearch size limits...

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

reswob4 — Mon, 28 Sep 2020 18:15:42 GMT

This is the one I got to work (note that I changed eval lookup to eval Badlist and the table column from type to Badlist):

I could not get the right syntax for martin_mueller's suggestion to function.

I tried:

and

but the first did not return any results and the second gave me an error (Error in 'eval' command: Typechecking failed. 'OR' only takes boolean arguments.)

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

martin_mueller — Wed, 26 Nov 2014 20:34:34 GMT

By setting the Badlist field within the subsearch you're effectively filtering for events with that field.

Try this:

  sourcetype=IPS [inputlookup additionalbadips | fields src] OR [inputlookup malwareips | fields src]
| lookup additionalbadips src OUTPUT lookup_name | lookup malwareips src OUTPUT lookup_name
| table source_host,source_address,lookup_name

That's assuming the lookups contain a column called lookup_name that identifies the name of the lookup you want to see in your results.

Re: What is the best way to compare search results to multiple lookup tables and identify which ones return hits?

reswob4 — Tue, 27 Jan 2015 20:41:52 GMT

So it turns out I needed to combine the answers of @martin_mueller and @musskopf for this to work. Using OR caused the search to run VERY slow. And piping the result of each search into a lookup got the info I wanted.

example below:

lookup#1

Domain, thislist
test.com,baddomainlist1
test2.com,baddomainlist2

lookup#2

Domain, thislist
anothertest.com,baddomainlist3
anothertest1.com,baddomainlist4

Update your transforms.conf and props.conf as appropriate

Then perform your search. For example if user1 at 10.10.10.10 has made an DNS query to anothertest.com and user2 at 192.168.0.0 has made a DNS query to test.com the following search:

sourcetype=WinDNS [inputlookup additionalbaddomains | fields Domain ] | lookup additionalbaddomains Domain output thislist | append [ search sourcetype=WinDNS [inputlookup malwaredomains | fields Domain] | lookup malwaredomains Domain output thislist ] |table Domain,source_address,thislist

Produces the following table:

Domain                                         source_address                                    thislist
anothertest.com                                10.10.10.10                                       baddomainlist3
test.com                                       192.168.0.0                                      baddomainlist1

Here is the point of this:

Say you have multiple sources from which you collect malicious domains or IPs. When you create your lookup tables, for each malicious domain/ip that you enter, put the source from where you go it. This way, when you get a hit against a bad domain or bad IP, you can refer back to the source to get more information about why that domain/ip was bad and figure out what other actions you should take.

We noticed that we were getting hits against an IP or domain labeled as BAD but no idea WHY because the reason it was put as an alert was lost. By being able to refer to the source, we could now find out if the IP or domain was bad because it's part of a botnet or spearphish or whatever and take specific actions.