Introduction
Today I want to open one more interesting part of making internet business - statistics collection. I hope you already knows popular service Google Analytics that helps many companies to track internet activity on web site (main issue that those analytics is not runtime, you have to wait 1 day for getting ready to use statistics). But do you want to know how to make own statistics tracking engine on IIS? I hope you do.
In this article I will describe how to collect statistics from IIS and how to do most common thing - resolve clients IP into geo-locations.
Theory
Let's make a quick look on web site hosting. What we can see? Let's count:
- IIS hosting server
- Web site (for example ASP.NET)
- Database (for example MS SQL Server)
Those 3 components are the most common for every web site based on Microsoft technologies.
Statistics tracking can be done only on first two components: IIS and ASP.NET... (but this is not exactly true, if take a look into depth then we can identify that in reality we have only one component IIS and it's filters model - ISAPI filters.)
Tracking of statistics on IIS layer is well described by Microsoft in own knowledge database.
Practice
First of all we don't want to re-invent bicycles. That is why we are taking a close look on systems available on WEB for free or commercial use (to avoid any advertising I miss here names of those systems). All those systems also known as “IIS Logs analyzer” utilities. So the first part of our effort will be to configure properly IIS for collecting our statistics.
To Do so we will use Microsoft published article: http://support.microsoft.com/kb/245243 -How to configure ODBC logging in IIS. Article contains all requered information about proper configuration of IIS for storing logs into Database (in my case this was latest SQL Server 2008). I hope Microsoft did a great job and everyone can repeat steps from article for proper IIS configuration.
Next step is the most interesting: How to get geo-location information about IP? Fast look into google and we found a lot of commercial services that for additional payment will open for us database of IPs geo-location... But THIS IS NOT OUR WAY! I search a little more and found really amazing service: http://software77.net/
and there open database IP-to-country: http://software77.net/cgi-bin/ip-country/geo-ip.pl
That looks very promissing. Software77.NET open database for free downloads. Delivery is done by ZIP archive with database CSV files in it. Wow! this is very very prommising.
3 hours later I publish small C# console application that automatically download database, unpack it and upload into our local database.
result database table is:
|
CREATE TABLE [dbo].[geoips]
(
[ip_from] [bigint] NULL,
[ip_to] [bigint] NULL,
[rigistry] [nvarchar](20) NULL,
[assigned] [bigint] NULL,
[country_code] [nchar](2) NULL,
[country_abbr] [nchar](5) NULL,
[country_name] [nvarchar](100) NULL
) ON [PRIMARY] |
and for proper user friendly representation of IPs I create special sql view:
|
create view vwGeoips
as
select ip_from,
cast( (ip_from & 0xFF000000) / 16777216 as NVARCHAR(3)) + '.' +
cast( (ip_from & 0xFF0000) / 65536 as NVARCHAR(3)) + '.' +
cast( (ip_from & 0xFF00) / 256 as NVARCHAR(3)) + '.' +
cast( ip_from & 0xFF as NVARCHAR(3)) as from_ip,
ip_to,
cast( (ip_to & 0xFF000000) / 16777216 as NVARCHAR(3)) + '.' +
cast( (ip_to & 0xFF0000) / 65536 as NVARCHAR(3)) + '.' +
cast( (ip_to & 0xFF00) / 256 as NVARCHAR(3)) + '.' +
cast( ip_to & 0xFF as NVARCHAR(3)) as to_ip,
rigistry,
country_code,
country_abbr,
country_name
from geoips |
And last moment is how to resolve client IP’s into geo locations:
|
create view [dbo].[vwInetLog]
as
select
t.ClientHost,
t.HttpRequests,
t.LogTime,
t.bytesrecvd,
t.bytessent,
g.country_abbr,
g.country_name
from (
select
ClientHost,
dbo.IpToNumeric( ClientHost ) as clientIp,
COUNT(*) as HttpRequests,
MAX(LogTime) as LogTime,
SUM(bytesrecvd) as bytesrecvd,
SUM(bytessent) as bytessent
from inetlog i
where ClientHost not like '10.10.0.%'
group by ClientHost
) t
left join geoips g on t.clientIp between g.ip_from and g.ip_to |
NOTE: in view we remove all IP's that belongs to the local domain scope. In our case this "10.10.0.%" pattern, in yours case it can be different, for example: "192.168.1.%".
and small T-SQL function in use:
|
CREATE FUNCTION [dbo].[IpToNumeric]( @strIP varchar(15) )
RETURNS bigint
AS
BEGIN
DECLARE @intIPNum bigint
-- check to ensure there are 3 dots
IF( LEN(@strIP) - LEN( REPLACE( @strIP, '.', '' ) ) ) / LEN('.') = 3
begin
SET @intIPNum = (
16777216 * CAST(PARSENAME(@strIP,4) as bigint) +
65536 * PARSENAME(@strIP,3) +
256 * PARSENAME(@strIP,2) +
PARSENAME(@strIP,1)
)
end
RETURN @intIPNum
END |
As result we have a view that resolve for us IP to it's geo-location. shows last visit time from corresponding IP, show quantity of HTTP requests from one IP, shows accumulated IN/OUT traffic for corresponding IP...
I think this is very good first step for making own statistics analysis systems, IP resolving web services and etc.
Enjoy
Note: in source code change CONNECTION string according to your environment.
Source code of upload utility: geoipupdater_src.zip (29.03 kb)
Currently rated 5.0 by 3 people
- Currently 5/5 Stars.
- 1
- 2
- 3
- 4
- 5