Splunk Search

Calculating page read time

Bulluk
Path Finder

I have a requirement from the business to register the time a user stayed on a news story, the idea being that this will be a more accurate measure of a story's interest that page views. I've been trying to work out how to accomplish this and have come up with the following:

  • group events by the users' login
  • break events into pairs, ie if the user visits 3 links that would be 2 pairs - 1and2, 2and3
  • calculate the time between each event pair
  • discard events less than 2 minutes (the user didn't read the story) and greater than 30 minutes (the user read the story and then left the window open while they do other stuff) - call this time duration
  • calculate the average duration for the first page of each pair

I'm struggling with the grouping of events. The closest I've come is to use the transaction command but I end up throwing loads of events away so I'm seeking some guidance or someone to tell me that I'd doing this in a stupidly complicated way and I should do it like this..... 🙂

Thanks in advance

Tags (2)
1 Solution

eelisio2
Path Finder

This should get you part of the way there. Not sure if it is more efficient than what you are currently doing.

sourcetype=yoursourcetype | eval steptime= _time | transaction UserLogin | mvexpand steptime | sort UserLogin, -steptime | streamstats count as seq by UserLogin | delta steptime as StepDuration | eval StepDuration=abs(StepDuration) | eval StepDuration=if(seq=1,0,StepDuration) | convert ctime(steptime) as StepTime | table _time UserLogin Page steptime StepTime StepDuration

Note that the transaction command automatically creates the duration and eventcount fields for an entire transaction. Sort on -steptime is so that the results of the delta command end up with the appropriate page/event.

This does not account for the third page in your posted question. The last page in any set would have a StepDuration of zero.

If you want to eliminate certain StepDurations from the results you can add a search command.

sourcetype=yoursourcetype | eval steptime= _time | transaction UserLogin | mvexpand steptime | sort UserLogin, -steptime | streamstats count as seq by UserLogin | delta steptime as StepDuration | eval StepDuration=abs(StepDuration) | eval StepDuration=if(seq=1,0,StepDuration) | search StepDuration > 120 AND StepDuration < 1800 | convert ctime(steptime) as StepTime | table _time UserLogin Page steptime StepTime StepDuration

View solution in original post

eelisio2
Path Finder

This should get you part of the way there. Not sure if it is more efficient than what you are currently doing.

sourcetype=yoursourcetype | eval steptime= _time | transaction UserLogin | mvexpand steptime | sort UserLogin, -steptime | streamstats count as seq by UserLogin | delta steptime as StepDuration | eval StepDuration=abs(StepDuration) | eval StepDuration=if(seq=1,0,StepDuration) | convert ctime(steptime) as StepTime | table _time UserLogin Page steptime StepTime StepDuration

Note that the transaction command automatically creates the duration and eventcount fields for an entire transaction. Sort on -steptime is so that the results of the delta command end up with the appropriate page/event.

This does not account for the third page in your posted question. The last page in any set would have a StepDuration of zero.

If you want to eliminate certain StepDurations from the results you can add a search command.

sourcetype=yoursourcetype | eval steptime= _time | transaction UserLogin | mvexpand steptime | sort UserLogin, -steptime | streamstats count as seq by UserLogin | delta steptime as StepDuration | eval StepDuration=abs(StepDuration) | eval StepDuration=if(seq=1,0,StepDuration) | search StepDuration > 120 AND StepDuration < 1800 | convert ctime(steptime) as StepTime | table _time UserLogin Page steptime StepTime StepDuration
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...