Hi there,
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!!
Thanks
Cees