Hi
I previously asked this question and marked it as answered following eelisio2's response.
http://splunk-base.splunk.com/answers/37075/calculating-page-read-time
Unfortunately I've been playing with this and have worked out that this returns the entire transaction rather than the time for each step in the transaction. I think the mvexpand statement is failing and so the delta statement can only work on the whole transaction. I found this link which appears to solve my problem but I'm struggling to understand the logic sufficiently to be able to merge it into my existing query.
http://splunk-base.splunk.com/answers/4344/delta-between-timestamp-within-a-transaction-search
I'd be really grateful if someone could have a look at the link and explain to me how I can make that work with the following query
sourcetype="iis"
tidy
| eval steptime= _time | transaction cs_username | eval raw=split(_raw, "\n") | mvexpand raw | rename raw as _raw | sort cs_username, -steptime | streamstats count as seq by cs_username| delta steptime as StepDuration | eval StepDuration=abs(StepDuration) | eval StepDuration=if(seq=1,0,StepDuration) | search StepDuration > 40 AND StepDuration < 1800 | eventstats mean(StepDuration) as ViewTime by cs_uri_stem | convert timeformat="%M:%S" ctime(ViewTime) as ViewTime | table cs_uri_stem, ViewTime | sort -ViewTime
I eventually ended up with this:
sourcetype=iis | eval etime=_time | fields cs_username cs_uri_stem etime | transaction cs_username | eval tr_id=mvindex(_serial,0) | mvexpand etime | streamstats current=f global=f window=1 last(etime) as letime by tr_id | eval timediff=coalesce(etime-letime,0) | search timediff > 40 AND timediff < 1800
| rename cs_uri_stem as Page | stats mean(timediff) as ViewTime by Page | convert timeformat="%M:%S" ctime(ViewTime) as ViewTime | sort -ViewTime
The logic is as follows:
I've borrowed heavily from the links in my original question and I'm a splunk newbie so there may well be bugs
I eventually ended up with this:
sourcetype=iis | eval etime=_time | fields cs_username cs_uri_stem etime | transaction cs_username | eval tr_id=mvindex(_serial,0) | mvexpand etime | streamstats current=f global=f window=1 last(etime) as letime by tr_id | eval timediff=coalesce(etime-letime,0) | search timediff > 40 AND timediff < 1800
| rename cs_uri_stem as Page | stats mean(timediff) as ViewTime by Page | convert timeformat="%M:%S" ctime(ViewTime) as ViewTime | sort -ViewTime
The logic is as follows:
I've borrowed heavily from the links in my original question and I'm a splunk newbie so there may well be bugs