Kind of inner join

[adamski007]

Hello,

Hopefully, you will understand what I mean...It was not clear how I could formulate a search to find some documentation.
I got an index, with a lot of fields [ f1, f2, f3, ... ]. Let's say that field f1 is the url from the proxy, and f2 is the source_ip of the request. What I would like is from a set of specific "source_ip", all the url that has been accessed by these "source_ip", and the url needs to be accessed by every single IP...

Any idea how can I emplement the query in Splunk ?

Thanks.

[DalJeanis · Accepted solution]

Here is the number of IPs total...

index=foo | stats dc(source_ip) as IpCount

Here is the number of IPs for any given url

index=foo | stats dc(source_ip) as IpCount by URL

So this will take the count for each URL, append in the count for ALL URLs, and reduce the output to only the URLs that match that count.

index=foo | stats dc(source_ip) as IpCount by URL
| append [search index=foo | stats dc(source_ip) as IpCount | eval URL = "All URLs"]
| eventstats max(IpCount) as MaxIpCount
| where IpCount = MaxIpCount

---

Updated: added word search to the append subsearch.

[adamski007]

I think I got it but without the line :
append [index=foo | stats dc(source_ip) as IpCount | eval URL = "All URLs"]
because it just give me an error with that line...
If I just give the following query :
index=foo (src_ip=10.0.01 OR src_ip=10.0.0.2 OR src_ip=10.0.0.3) | stats dc(source_ip) as IpCount by URL
| where IpCount = 3

This will give me ALL the destination URL which has been accessed by these 3 source IP.

[DalJeanis]

@adamski007 - Sorry, I missed the word search on the subsearch code. It should work as desired now without hardcode.

[adamski007]

Thanks for all the help, really appreciated !

[niketn]

@adamski007, you still need to be more precise possible with the input data sample and specific table you need in output.

If you require list of all url access by all searched source_ips you can try the following:

<YourBaseSearch>
| eventstats dc(source_ip) as distinctSourceIPs  
| stats dc(source_ip) as distinctSourceIPsByUrl last(distinctSourceIPs) as distinctSourceIPs by url
| search distinctSourceIPsByUrl=distinctSourceIPs 

Please try out and confirm.

[DalJeanis]

@niketnilay - spelling on line 4.

[niketn]

@DalJeanis, you are always the savior. I have corrected 🙂

[adamski007]

ok seems that I was not clear enough in my question...

Xisura almost made it... but in that query you showed me, it is actually all the url accessed by IP, and what I would want is the "intersect", the commun URL accessed by all these specific IP.

Let's say that I got 2 IPs :
- 10.1.1.1
- 10.2.2.2

and they respectively visit :

A. 10.1.1.1
- bloomberg.com
- foxnews.com
B. 10.2.2.2
- bloomberg.com
- cnn.com

And so actually, I would want a query that return only URL [ bloomberg.com ] , because this URL has been visited by these 2 IPs.

hoping it is clearer with the example. Thanks.

[xisura]

try :
index="your index name" |stats values(url) by source_ip

[andre_tucker]

adamski007 your question is a bit confusing, but I will try to understand so that I can help. Are you trying to see only url's that were accessed by EVERY source_ip? (ex. If there are 3 source_ip's do you only want to see the url (splunk.com for example) if source 1, 2, and 3 all accessed splunk.com?

[adamski007]

yes, indeed, that's exactly what I want... any idea ?