PageViews and Unique are ok, but how do I calculate visits?
Last post Feb 22, 2007 11:37 AM by Anonymous
Nov 07, 2006 06:22 AM|Anonymous|LINK
browsing through this forum I have found the answer to my questions ALMOST. But it's the almost part that I have been struggling with for a day and a half now. What I would like to measure is basically three things:
1. pageviews: I used query below to do that (please correct me if I'm wrong)
SELECT COUNT (*) AS PAGEVIEWS into PAGEVIEWS.txt FROM www3\*.log WHERE EXTRACT_EXTENSION(cs-uri-stem) NOT IN ('css';'js';'gif';'bmp';'jpg';'jpeg';'png')
2. unique visitors: query below (once again: I might be wrong....)
SELECT COUNT(DISTINCT HASHSEQ(STRCAT(c-ip, [cs(User-Agent)]))) AS UNIQUE into UNIQUE.txt FROM www3\*.log
3. and this is the hard one: VISITS a.k.a. user sessions
We use the IISWSC logfiles, so I first thought: why not use the ASPSESSIONID for that. Since our pages are mainly ASP, the ASPSESSIONID field should be filled on almost every line of the logfile. BUT.... that means missing out on all files not being ASP
and I have no idea what will happen once we migrate to ASPX.
Then I thought something like:
SELECT COUNT(DISTINCT HASHSEQ(STRCAT(c-ip, [cs(User-Agent)]))) from www3\*.log HAVING (MAX(date,time)-MIN(date,time)= etc. etc.
But as you will understand from the etc. etc., the HAVING part is where I give up. I have no idea about the syntax or whether this can be done in the first place.
I read somewhere that a VISIT or USER SESSION means that a visitor stays active for at least 20 minutes. But how do I query that OR how do the loganalyzer software makers do that?
Any help appreciated!!
IIS Input Format
Nov 07, 2006 06:44 AM|Anonymous|LINK
Nov 07, 2006 06:49 AM|Anonymous|LINK
I did think about looking at AWStats, but the first glimpse learned that it is not as flexible as Logparser is. That's why I love it, I just output a number, add it to an HTML table, throw in the company logo and export it to PDF. My manager loves me for
Thanks for the reply Daniel.
Nov 07, 2006 07:34 AM|Anonymous|LINK
Nov 07, 2006 07:51 AM|Anonymous|LINK
And you have to have dynamic pages, because IIS f.e. does not log the session cookie in case of an .HTM(L) file. If the same user visits an ASP page too then it is no problem, but if your website is mainly static pages.....
Feb 17, 2007 09:48 PM|Anonymous|LINK
nice querys tho.. very interesting for a newbie like me..
WebLogExpert (Pro trial priod) gives me output like this
The one called total visitors is users that has been away from the site more than 30 minutes (this is something that u set analyzer options panel)
Unfortuneatle i could not disable it..to see if it gave the same result as your "time sesssion" - query. I could set it at 10000 minutes at max..but the two numbers were not that far from eachother.
If you have more sweet query lines throw them this way ^_^
Feb 22, 2007 11:37 AM|Anonymous|LINK
The big problem with Log Parser and User level statistics is that Log Parser is designed to focus on a row of data and does not cross the row boundary. When faced with a problem that goes cross rows, you really need to write a little code. Just keep track
of the first and last time for a "user" and clear/dump them when the last time is too far away from the "current" time (I have hacked similar using a pair of arrays in Java - nothing I would let anyone look at, but it can be done).
The hardest part is actually defining "user". This is simple if you force authentication. If you don't, then you have to worry about proxies verses users.