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.