topic Re: proper use/function of 'set intersect' in Splunk Search

proper use/function of 'set intersect'

rgonzale6 — Wed, 18 Aug 2010 02:03:50 GMT

I have an index where events contain a source IP and a URL destination field. I would like to construct a query that would show commonality in events. I would like to search multiple IPs and have my search return only URLs that have been contacted by all of those IPs. I had constructed my search like so, for only two IPs:

set intersect [search index=INDEX_NAME Internal_IP=IPPADDR1 | fields URL ] [search index=INDEX_NAME Internal_IP=IPADDR2 | fields URL]|fields URL

By my thinking, this would return only the URL fields where there was commonality found between the results of the two subsearches here. It's not working.

I can do a subsearch that does this easily enough when it's only two hosts...but in practice, I will need the results for far more than two hosts. Here's my subsearch-based solution for two hosts, which works well:

[search index=INDEX_NAME Internal_IP=IPADDR1 | fields URL] index=INDEX_NAME Internal_IP=IPADDR2 | fields URL | top URL

Thanks!

Re: proper use/function of 'set intersect'

Stephen_Sorkin — Wed, 18 Aug 2010 02:10:43 GMT

Using set isn't going to be the most efficient way to solve this problem.

I'd use stats to look at the source IP characteristics for each url like:

index=INDEX_NAME | stats values(Internal_IP) as Internal_IPs dc(Internal_IP) as Internal_IP_count by URL

You can then pipe the results of this to | search Internal_IP_count > <threshold> to see the URLs that were accessed by more than <threshold> IPs as well as the IPs that accessed them.

Re: proper use/function of 'set intersect'

ziegfried — Wed, 18 Aug 2010 02:16:14 GMT

Just an idea for a different approach:

index=INDEX_NAME (Internal_IP=IPADDR1 OR Internal_IP=IPADDR2) | stats dc(Internal_IP) as ip_count by URL | where ip_count>1

which would reduce the events to those with IPs you're interested in before computing number of distinct ip addresses per url, and then filtering the results to only those that has been accessed by all IPs. It can easily expanded to more IPs:

index=INDEX_NAME (Internal_IP=IPADDR1 OR Internal_IP=IPADDR2 OR Internal_IP=IPADDR3) | stats dc(Internal_IP) as ip_count by URL | where ip_count>2

Re: proper use/function of 'set intersect'

rgonzale6 — Wed, 18 Aug 2010 02:51:54 GMT

thanks! Much appreciated.

Re: proper use/function of 'set intersect'

rgonzale6 — Wed, 18 Aug 2010 02:52:17 GMT

thanks! Appreciate your response.