How to generate a search that will correlate multiple IP addresses that hit the same URL?

[DavidScavotto]

I am utilizing Cisco Ironport Squid logs. I found a suspicious event that is possible malware related and multiple computers/IP addresses hit. I would like to see if multiple computers hit the same URL to possibly get malware.

What I would like to do is correlate multiple IP addresses (sourcetype=cisco:squid (IP or IP or IP or IP or IP) that have hit the same URL address to assess whether they grabbed a file or visited the same malicious site. I know | top limit = 20 url will get me the most hit URLs from these IP's, but I am only interested in the URLs that are the same across all IP's.

Thanks!

[cmerriman]

sourcetype=cisco:squid (IP or IP or IP or IP or IP)|eventstats dc(IP) as totalIPs|stats values(IP) as IPaddresses dc(IP) as dcIP max(totalIPs) as totalIPs by URL|where dcIP=totalIPs

something like this should give you what you're looking for, if I'm not mistaken. the eventstats will distinctly count how many IPs you have total, then the stats command will list out the IP addresses and distinctly count them by each URL. If the number that hit the URL does not match the total, that URL would be filtered out, so you should only see URLs that were hit by every IP.

[gokadroid · Accepted solution]

if URL and ip is already extracted fields on your events try transaction on url:

your base query to return ips and urls events
| transaction url 
| table url, ip

[DavidScavotto]

This got me close to the answer I am looking for, but it still separates it out in individual lines with URL's that have not been hit by all 4 IP's. Going through this list though I will see some URL's that do have all 4 IP's and that is exactly what I am looking for. Any idea on how I can get it to show only URL hits that match all IP's put into my search?

[gokadroid]

your base query to return ips and urls events
 | transaction url 
 |fields url , ip
 |eval ipCount=mvcount(ip)
 | table url, ip,ipCount
 | where ipCount >=4

And since you know the all 4 IPs at the start, start with searching on those 4 IPs in you base query

[DavidScavotto]

This did it. My final search for those that are interested:
index=* sourcetype="cisco:squid" (IP OR IP OR IP OR IP)
| transaction url
| fields url, src
| eval IPCount=mvcount(src)
| table url, src, IPCount
| where IPCount = 4

My splunk results had the IP under src which is why my final search slightly deviates from what gokadroid provided. This worked great though and showed me the URL I was interested in serving up malware to all of my IP's. Thank you!

[skoelpin]

This will work but you should want to avoid the transaction command whenever possible due to slow performance