Is it possible to write a query to find same URLs visited by multiple clients in a proxy log?

[sureshtskumar]

Hi,

I am working with my proxy logs and trying to find a way to get same URLs visited by multiple clients. To add clarity, my current splunk query gives me an output similar to this:-

src_ip       URL
1.2.3.4      abc.com, jp.com, ms.com
2.3.4.5      abc.com, yahoo.com. jp.com
3.3.5.5      abc.com, hoot.com. japn.com
6.7.8.5      abc.com, yahoo.com. jp.com, ms.com

I am trying to get something like the below as clearly all clients visited abc.com

src_ip       URL
1.2.3.4      abc.com
2.3.4.5      abc.com
3.3.5.5      abc.com
6.7.8.5      abc.com

Anyone helping with an SPL would be greatly appreciated. I have tried a lot of documentations and forums but it doesn't look like there is a straightforward answer to what I am trying to accomplish.

[sureshtskumar · Accepted solution]

Played around a bit and so far this is the closest I could get. Feel free to improve if there is a better aternative:

index=<proxy_index> src_ip IN ("x.x.x.x", "y.y.y.y"....) earliest=-24h
| stats values (src_ip) dc(src_ip) as dcnt by cs_host
| where dcnt=<total number of src IPs>

This shows URLs that were visited by all the source IPs in question over a 24 hour period.

[ITWhisperer]

| eval URL=split(URL,",")
| mvexpand URL
| sort 0 URL src_ip

[sureshtskumar]

Thanks@ITWhisperer 

Your query would split the URLs and sort by src_ip. What I need is to find the URLs common across all src_ip and discard the other URLs. Hope it makes sense. I have given the details of the desired output to give an idea

[ITWhisperer]

| eval URL=split(URL,",")
| mvexpand URL
| eventstats count by URL
| where count > 1
| sort 0 URL src_ip
| table src_ip URL