topic How to modify my search to show users who have visited both category="Entertainment" and category="Business"? in Splunk Search

How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

ivar9692 — Wed, 05 Oct 2016 10:24:08 GMT

I'm using following search but it's not working:

index=proxy_logs  category="Entertainment"  category="Business" | stats ..

This search is not giving results but in logs I have users who visited sites with both categories.

Like a user visited site1 with category="Entertainment" and while further surfing, he visited another site2 category="Business".
I need to find such users.

If using this search:

index=bluecoat  category="Translation" OR category="Pornography"

it is giving results. But in those results, I have users who accessed either one of them not both of them.

Please tell me if you need more information.

Re: How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

lukejadamec — Thu, 06 Oct 2016 00:43:54 GMT

I don't have the answer, but the problem with your first search is that it is looking for single events that contain both categories at the same time, which is not possible with single value fields.
Fear not, I'm sure someone will show you how to use your search and sort them out by user so that only users that did both in different events are listed.

Re: How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

somesoni2 — Thu, 06 Oct 2016 06:29:19 GMT

Try like this. Replace PutYourUserFieldHere with the field that you want to use for user

index=bluecoat  category="Translation" OR category="Pornography"  | stats values(category) as category by PutYourUserFieldHere | where mvcount(category)=2

Re: How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

govindsinghrawa — Thu, 06 Oct 2016 07:02:10 GMT

try this if your user name field is say "userName":

index=bluecoat category="Translation" OR category="Pornography" | stats dc(category) as distinctCategory by userName| where distinctCategory>=2

Re: How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

inventsekar — Thu, 06 Oct 2016 07:36:44 GMT

Please check this one -

 index=proxy_logs  category="Entertainment"  [ search index=proxy_logs category="Business" | table UserNames ] | stats ..

Re: How to modify my search to show users who have visited both category="Entertainment" and category="Business"?

aaraneta_splunk — Mon, 24 Oct 2016 03:25:48 GMT

Hi @ivar9692 - Looks like you have a few possible solutions to your question. If one of them provided a working solution, please don't forget to click "Accept" below the best answer to resolve this post. If you still need help, please leave a comment. Thanks!