October 26, 2007 at 11:07 pm
Fairly new to non-trivial SQL statements and I have server logs that I have imported and I want to be able to take a snapshot of the system and identify the makeup of the referrers based on the extentions found in the logs. I try using a case function but I am having no luck.
The furthest I have been able to get...
SELECT 'Language'=
CASE [http_referrer]
WHEN '%.aspx%' THEN 'ASPX'
WHEN '%.asp%' THEN 'ASP'
WHEN '%.html%' THEN 'HTML'
WHEN '%.htm%' THEN 'HTM'
WHEN '%.php%' THEN 'PHP'
WHEN '%.cgi%' THEN 'CGI'
WHEN '%.jsp%' THEN 'JSP'
WHEN '%.pl%' THEN 'PERL'
WHEN '%.swf%' THEN 'SWF'
WHEN '%.cfm%' THEN 'CFM'
WHEN '-' THEN 'Blank'
ELSE 'Other'
END
FROM logs
I cannot seem to find a grouping option since 'Language' is not a field in the table
In the end I want to be able to see
| LANGUAGE | COUNT |
|ASP | 25 |
|ASPX | 52 |
and so on.
October 27, 2007 at 3:09 am
Here is the statement which will give the no. of hits by page type....
SELECT[LANGUAGE], COUNT( * ) AS [COUNT]
FROM(
SELECT( CASE
WHEN CHARINDEX( '.', REVERSE( [http_referrer] ) ) > 0
THEN RIGHT([http_referrer], CHARINDEX('.', REVERSE( [http_referrer] ) ) )
ELSE '-'
END ) AS [LANGUAGE]
FROMlogs
) L
GROUP BY [LANGUAGE]
ORDER BY [LANGUAGE]
--Ramesh
October 27, 2007 at 7:50 pm
I am wondering if that will do what I am trying to do
'Language' is not acutally a field in the table and the URLs are oddly formated as they are often times the end point of sales transations with session and receipt information in them.
October 29, 2007 at 12:24 am
jdwebwolf (10/27/2007)
I am wondering if that will do what I am trying to do'Language' is not acutally a field in the table and the URLs are oddly formated as they are often times the end point of sales transations with session and receipt information in them.
'Language' is an alias name for the output column from the derived table....
As you mentioned, the URLs are scattered in the column...., this means the only ways you can retrieve is either by using LIKE operator or by enabling full text indexing capabilities...
SELECT[LANGUAGE], COUNT( * ) AS [COUNT]
FROM(
SELECT( CASE
WHEN [http_referrer] LIKE '%.aspx%' THEN 'ASPX'
WHEN [http_referrer] LIKE '%.asp%' THEN 'ASP'
WHEN [http_referrer] LIKE '%.html%' THEN 'HTML'
WHEN [http_referrer] LIKE '%.htm%' THEN 'HTM'
WHEN [http_referrer] LIKE '%.php%' THEN 'PHP'
WHEN [http_referrer] LIKE '%.cgi%' THEN 'CGI'
WHEN [http_referrer] LIKE '%.jsp%' THEN 'JSP'
WHEN [http_referrer] LIKE '%.pl%' THEN 'PERL'
WHEN [http_referrer] LIKE '%.swf%' THEN 'SWF'
WHEN [http_referrer] LIKE '%.cfm%' THEN 'CFM'
WHEN [http_referrer] LIKE '-' THEN 'Blank'
ELSE 'Other'
END ) AS [LANGUAGE]
FROMlogs
) L
GROUP BY [LANGUAGE]
ORDER BY [LANGUAGE]
--Ramesh
October 31, 2007 at 11:02 am
I've found CTE's to be one of the better methods to use to report on aggregates. My code is the same as Ramesh, but utilizes a CTE, my favorite toy in SQL Server 2005.
WITH myCTE
AS
(
SELECT 'Language'=
CASE
WHEN RTRIM(RIGHT(http_referer, 5)) = '.aspx' THEN 'ASPX'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.asp' THEN 'ASP'
WHEN RTRIM(RIGHT(http_referer, 5)) = '.html' THEN 'HTML'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.htm' THEN 'HTM'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.php' THEN 'PHP'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.cgi' THEN 'CGI'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.jsp' THEN 'JSP'
WHEN RTRIM(RIGHT(http_referer, 3)) = '.pl' THEN 'PERL'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.swf' THEN 'SWF'
WHEN RTRIM(RIGHT(http_referer, 4)) = '.cfm' THEN 'CFM'
WHEN RTRIM(LTRIM(http_referer)) = '-' THEN 'Blank'
ELSE 'Other'
END
FROM logs
)
SELECT Language, COUNT(Language) AS Count
FROM myCTE
GROUP BY Language
October 31, 2007 at 1:49 pm
Thanks Ramesh,
That worked. Now the challenge that I have is that reporting services will not allow me to use Language and Count as valid sources for the rows since they are not a part of the data set.
Any ideas?
November 1, 2007 at 1:32 am
May be I could have learned a little bit on Reporting Services:cool:
May be dumping data in a temporary table and returning from it could help?:hehe:
--Ramesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply