Modified Detailed Site Log

The detailed site log provides details about the referrer, User agent, Host address and the Page viewed. However, I find that the resolving of the user agent does not provide details about which browser has been used, too many resolver to Netscape Navigator 6+. It would also be nice to know which operating system is being used.

The SQL given below may be used to improvethe resolving of the current DotNetNuke detailed site log.

create procedure dbo.GetSiteLog2
  @PortalId int,
  @PortalAlias nvarchar(50),
  @StartDate datetime,
  @EndDate datetime
AS
SELECT
  SiteLog.DateTime,
  'Name' =    
  CASE
    WHEN SiteLog.UserId IS NULL THEN NULL
    ELSE Users.FirstName + ' ' + Users.LastName
  END,
  'Referrer' =  
  CASE 
    WHEN SiteLog.Referrer LIKE '%' + @PortalAlias + '%' THEN NULL
    ELSE SiteLog.Referrer
  END,
  'UserAgent' =  
  CASE
    WHEN SiteLog.UserAgent LIKE '%Konqueror%' then 'Konqueror'
    WHEN SiteLog.UserAgent LIKE '%Slurp%' then 'Inktomi Robot'
    WHEN SiteLog.UserAgent LIKE '%WISEnutbot%' then 'Looksmart Robot'
    WHEN SiteLog.UserAgent LIKE '%Ask Jeeves%' then 'Ask Jeeves/Teoma'
    WHEN SiteLog.UserAgent LIKE '%Safari%' then 'Safari'
    WHEN SiteLog.UserAgent LIKE '%Firefox%' then 'Firefox'
    WHEN SiteLog.UserAgent LIKE '%MSIE 1%' then 'Internet Explorer 1'
    WHEN SiteLog.UserAgent LIKE '%MSIE 2%' then 'Internet Explorer 2'
    WHEN SiteLog.UserAgent LIKE '%MSIE 3%' then 'Internet Explorer 3'
    WHEN SiteLog.UserAgent LIKE '%MSIE 4%' then 'Internet Explorer 4'
    WHEN SiteLog.UserAgent LIKE '%MSIE 5%' then 'Internet Explorer 5'
    WHEN SiteLog.UserAgent LIKE '%MSIE 6%' then 'Internet Explorer 6'
    WHEN SiteLog.UserAgent LIKE '%MSIE%' then 'Internet Explorer'
    WHEN SiteLog.UserAgent LIKE '%Mozilla/1%' then 'Netscape Navigator 1'
    WHEN SiteLog.UserAgent LIKE '%Mozilla/2%' then 'Netscape Navigator 2'
    WHEN SiteLog.UserAgent LIKE '%Mozilla/3%' then 'Netscape Navigator 3'
    WHEN SiteLog.UserAgent LIKE '%Mozilla/4%' then 'Netscape Navigator 4'
    WHEN SiteLog.UserAgent LIKE '%Mozilla/5%' then 'Netscape Navigator 6+'
  ELSE SiteLog.UserAgent
  END,
  'OS' =  
  CASE
    WHEN SiteLog.UserAgent LIKE '%Windows 95%' then 'Windows 95'
    WHEN SiteLog.UserAgent LIKE '%Windows 98%' then 'Windows 98'
    WHEN SiteLog.UserAgent LIKE '%Windows NT 5.0%' then 'Windows NT'
    WHEN SiteLog.UserAgent LIKE '%Windows NT 5.1%' then 'Windows XP'
    WHEN SiteLog.UserAgent LIKE '%Windows NT 5.2%' then 'Windows .net'
    WHEN SiteLog.UserAgent LIKE '%Linux%' then 'Linux'
    WHEN SiteLog.UserAgent LIKE '%Mac OS X%' then 'Mac OS X'
  END,
  SiteLog.UserHostAddress,
  Tabs.TabName
  FROM
    SiteLog
    LEFT OUTER JOIN Users on SiteLog.UserId = Users.UserId 
    LEFT OUTER JOIN Tabs on SiteLog.TabId = Tabs.TabId 
  WHERE
    SiteLog.PortalId = @PortalId
    AND SiteLog.DateTime BETWEEN @StartDate AND @EndDate 
  ORDER BY
   SiteLog.DateTime desc

NAT August 2005