Splunk Search

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

sureshtskumar
Explorer

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.

Labels (2)
0 Karma
1 Solution

sureshtskumar
Explorer

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.

View solution in original post

0 Karma

sureshtskumar
Explorer

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.

0 Karma

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

sureshtskumar
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval URL=split(URL,",")
| mvexpand URL
| eventstats count by URL
| where count > 1
| sort 0 URL src_ip
| table src_ip URL
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...