Advanced IIS log Parsing

The top ten files/pages requested

select top 10  
     [uri-stem]+uri as [page] 
     , count([uri-stem]+uri) n
from [dbo].[18jan-logdata  
group by [uri-stem]+uri  
order by n desc  

The top ten IP addresses (or users) who requested the most URLs

select top 10 ip  
     , count(ip) n
from [dbo].[18jan-logdata]  
group by ip  
order by n desc  

The top three most active hours (most requests per hour)

select DATEPART("HH",TIME) AS [HOUR]  
    , COUNT(DATEPART("HH",TIME)) AS REQUESTS 
from [DBO].[18JAN-LOGDATA]  
group by DATEPART("HH",TIME)  
order by [HOUR]  

Importing the resultant buckets into Excel makes for a striking chart.

The number of requests per query method

select method  
    , count(method) n
from [dbo].[18jan-logdata]  
where method<>'GET' or [uri-query]<>'-'  
group by method  
union  
select 'GET(no params)'  
    , count(method) n
from [dbo].[18jan-logdata]  
where method='GET' and [uri-query] = '-'  
group by method 

select distinct ip  
from [dbo].[18jan-logdata]  
where method like '-'  --are these requestors hiding their browser headers using something like the "modify headers" addon for firefox  

User agents and/or spiders?

select useragent  
    , count(*) n
from [dbo].[18jan-logdata]  
where useragent like '%googlebot%'  
group by useragent  
select *  
from [dbo].[18jan-logdata]  
where useragent like '%google%' and uri like '%robots.txt%'  
select  *  
from [dbo].[18jan-logdata]  
where useragent like '%ipd%' and uri like '%robots.txt%'

select top 10 useragent  
    , count(*) n   
from [dbo].[18jan-logdata]  
group by useragent  
order by n desc  

Referrers

Do we owe anyone any kudos or cash?

select referrer  
FROM [dbo].[18jan-logdata]  
where  referrer not like '%resign%' and referrer not like '-'  

Example

Where of course We can see the typical search engines represented, exhibiting the usual tendency towards Google and secondly, Bing’s predecessor.