Show if IP is also in another sourcetype

[Fischerman]

Hyas all

I'm sure this is an easy thing for a Splunk crack, but not for me as I'm a noob (4 days Splunk experience 😉

What I try to do is following :

I got a
sourcetype="CUSTOMER_EXTERNAL_IP" and in there the field "customer_external_ip"
and
sourcetype="BOTNET" and in there the field "customer_host"

Now I wanna see all "customer_host" if the IP is also in the "customer_external_IP"

Any hint in which direction I have to search for the solution ?

Thx in advance !-)

[Fischerman]

UPDATE :
Had some problems with subsearch (Splunk is telling me network is down and other wired stuff), so I was playing arround with Martins solution (coalesce) and this works smoothly and without any problem. I think this one is using a lot less system power and I would propose ; If you got the same challenge to solve, use first the "coalesce" solution.

BTW : Right now I got 66,467 customer_external_ip and about 1200 botnet events,,,botnet events growing from day to day.

[martin_mueller]

Depending on your requirements you could join the two searches using the IP as join field, or you could use one as a subsearch of the other producing IPs as filter criteria, or many other ways.

For example, if you just want a list of IPs that appear in both you could do this:

sourcetype="CUSTOMER_EXTERNAL_IP" OR sourcetype="BOTNET" | eval ip = coalesce(customer_external_ip, customer_host) | stats dc(sourcetype) as dc by ip | where dc > 1 | table ip

[Fischerman]

Thx Martin for your great answer. Also this works fine and it points me to the right direction for my next solution to solve with Splunk 😉

[BobM]

Subsearch will give you this. It lets you take the result of a search and use it as a filter. This should work for you.

sourcetype="BOTNET" [search sourcetype="CUSTOMER_EXTERNAL_IP" | fields customer_external_ip | rename customer_external_ip as customer_host]

[BobM]

Yes that would be easy to do. Have the csv file saved in your app's lookup folder and configure it to use it. There is a good splunk blog on lookups here.

http://blogs.splunk.com/2009/07/27/enriching-data-with-lookups-part-1/

[martin_mueller]

You could store known IPs in a CSV file together with a "type" column with values such as "botnet", "company X", "whatever". The lookup adds that field to your splunk results and you can filter by those.

As for tutors, you can consult your local splunk partner for some help 🙂

[Fischerman]

Thx a lot for your fast answer. This is / was exactly what I was searching for.

I know it is not nice to ask another question as in the title of this thread, but it is related to the question ;

would the same also be possible if I do not "import" the External IPs and use the CSV file as a lookup table ?
If yes, any hint to a good tutor for lookup tables ?-)