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)
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
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
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?
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
Thanks again @richgalloway , it works! If I could upvote twice I would 🙂