How to match IPs from two different sources, count the number of matches, and extract another value from the matching events?

[raduonica]

Hello,

I have two different types of data inputs, both having a field that represents an IP (let's call the list of all IPs from each data input IP1 - from first input and IP2 - from second input). What I need to do is to try and see if any IP1 appears in IP2, count the number of times if does appear (if any) and, IF POSSIBLE, when an intersection occurs, extract another value (say "event_name") from the event (from one of the input sources) corresponding to the IP that caused the intersection.

I am currently trying to do this by displaying all IPs from both sources in two table columns and doing a match between them.

Currently using this for doing the matching part:

source=SOURCE1 OR source=SOURCE2 |streamstats count by IP1, IP2 | stats values(IP1) AS ip1, values(IP2) AS ip2 | mvexpand ip2 | eval Status = if(match(ip1,ip2), "MATCH", "NO MATCH") | table ip1 ip2 Status | where Status="MATCH"

I can't seem to be able to count the number of matches that each gives (how many times ip2 is found in ip1 for example).
For this I've tried using a subsearch which seems to be working for a specific input ip, but I cant seem to be able to input each value from either the ip1 or ip2 list as a "parametr" for the subsearch:

appendcols [search source=SOURCE1 | search 100.100.100.100 | stats count as occurrences]

The last part, the extraction of a specific field from one of the events that caused a match, is an optional part but would be really useful.

[maciep]

I wonder if Join might be more useful here.
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

Side note: I believe the the values aggregate will only display unique results of that field. Where as list can be used to display all results of those field.

so if you have data like this

IP
1.1.1.1
2.2.2.2
1.1.1.1
3.3.3.3
3.3.3.3

stats values(IP) will give you 3 results, stats list(IP) will give you 5 results.

[raduonica]

I will look into a method of using "join". The hint you gave me did seem to help considering the fact that some IP's intersect more than once.