Splunk Enterprise

eval if field value is same in two different events

user93
Communicator

Hello

How can I return results, for all events, where a field value is the same in two of those events. 

For example, on a website, I want only events were one user viewed two different pages and the events of the page loads are different events. I don't want to specify the users, but rather return all the users that viewed both pages. 

 

<base> page=abcd OR page=1234 AND  (eval if userids are equal)  

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

That can be done with stats rather than eval.

<base> (page=abcd OR page=1234) 
`comment("Group the unique page names by user")`
| stats values(page) as pages by user
`comment("Filter out the users that only viewed one page")`
| where mvcount(pages) = 2
`comment("Return all the users that viewed both pages")`
| table user

 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

That can be done with stats rather than eval.

<base> (page=abcd OR page=1234) 
`comment("Group the unique page names by user")`
| stats values(page) as pages by user
`comment("Filter out the users that only viewed one page")`
| where mvcount(pages) = 2
`comment("Return all the users that viewed both pages")`
| table user

 

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

user93
Communicator

Thanks @richgalloway ! I'm not sure how to upvote in the new UI, but I accepted the solution.  

It works, but I realize I need to further refine. I need for both page visits to have occurred on the same day to be counted as 1.  If someone visits page abcd and two months later page 1234, they are less likely to be related as if they visited both within the same day or the same hour. 

 

Any ideas? 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You can up-vote a reply by clicking on the thumbs-up icon below it.

Here's an untested update to my query that accounts for the time between page views.

<base> (page=abcd OR page=1234) 
`comment("Group the unique page names by user")`
| stats values(page) as pages, range(_time) as timeSpan by user
`comment("Filter out the users that only viewed one page")`
| where (mvcount(pages) = 2 and (timeSpan <= 3600)
`comment("Return all the users that viewed both pages")`
| table user
---
If this reply helps you, an upvote would be appreciated.

user93
Communicator

Thanks again @richgalloway , it works! If I could upvote twice I would 🙂 

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!