Working out session counts from imported iis logs

[mattquantic]

For the reports I can get things like sessions out by grouping by a var in the cookie.

But some of my clients want different determinations of a session timeout length than others.

What I'm trying to do in a query:
• get the logs for a day (done)
• create a field with an id (sessid) that I set in the query
• establish the time diff in mins in the current row from that of the row above it
• if the time is > 15 min to increment the sessid by 1
• group the results by the sessid
• populate results in a db so the query only needs to run once
I have looked into things a little and am investigating the use of:

select
case when datepart(min, thedatetime) - rowbefore
then [increment+1] as sessit
else [increment] as sessit
end
,col2
,col3
from logtb
where etc etc

The other alternative is to first go though the table and then repopulate another table with the same data, adding a calculated sessid.

Can I borrow your brain?

Matt

 

[mattquantic]

Hopefully this makes things a little clearer:

I've added a result's based demo here to make things clearer in my own head as well as the post:
------------------------------------------------------------------------------------------------

time & page & cfid are real columns
cfid is generated by the server when a new session is created
sess (in second table) is a fake one
the query is ordered by cfid and then by time

raw log
-------
time page cfid
11:20.0 /page1.htm 1028
12:37.0 /page2.htm 1028
12:53.0 /page3.htm 1028
16:53.0 /page1.htm 1028
17:08.0 /page2.htm 1028
17:28.0 /page3.htm 1028
19:10.0 /page1.htm 1028
19:50.0 /page2.htm 1028
20:24.0 /page3.htm 1028
20:37.0 /page1.htm 1029
21:14.0 /page2.htm 1029
22:00.0 /page3.htm 1029
22:35.0 /page1.htm 1029
06:22.0 /page2.htm 1029


after new query (sess grouped by 20 min of inactivity)
---------------
time page cfid sess
11:20.0 /page1.htm 1028 1
12:37.0 /page2.htm 1028 1
12:53.0 /page3.htm 1028 1
16:53.0 /page1.htm 1028 2
17:08.0 /page2.htm 1028 2
17:28.0 /page3.htm 1028 2
19:10.0 /page1.htm 1028 3
19:50.0 /page2.htm 1028 4
20:24.0 /page3.htm 1028 5
20:37.0 /page1.htm 1029 6 (new sess because the cfid is diff)
21:14.0 /page2.htm 1029 7
22:00.0 /page3.htm 1029 8
22:35.0 /page1.htm 1029 9
06:22.0 /page2.htm 1029 10

----------------------------------------------------------------------------------------------------

I hope this makes everything a bit clearer?

Please let me know if you know what I mean or have any suggestions...

Matt

 

[mattquantic]

I actually screwed the sess vals slightly as I didn't see that the first one changed by 1h 17 min rather than 17 min.

So it should read:

time page cfid sess
11:20.0 /page1.htm 1028 1
12:37.0 /page2.htm 1028 2
12:53.0 /page3.htm 1028 2
16:53.0 /page1.htm 1028 3
17:08.0 /page2.htm 1028 3
17:28.0 /page3.htm 1028 3
19:10.0 /page1.htm 1028 4
19:50.0 /page2.htm 1028 5
20:24.0 /page3.htm 1028 6
20:37.0 /page1.htm 1029 7 (new sess because the cfid is diff)
21:14.0 /page2.htm 1029 8
22:00.0 /page3.htm 1029 9
22:35.0 /page1.htm 1029 10
06:22.0 /page2.htm 1029 11