Custom 404 and logging

[Paul Woods]

I'm developing an application that uses a custom 404 page to deliver all of
my site's content. However, doing things this way renders IIS's regular log
files pretty much useless.

Are there any established "best practices" for creating your own logging
system? I know that others use this technique and I'm hoping someone has
some ideas they can pass along.

For example, do you log every single page request or do you just log totals
per day, week, or month? How do you deal with the increasing volumes of
data? Do you collect referrer data, etc.?

Thanks,

Paul

 

[Aaron Bertrand - MVP]

Are there any established "best practices" for creating your own logging

system?

"logging system" is far too broadly defined here. What information are you
trying to capture?

For example, do you log every single page request or do you just log totals
per day, week, or month?

You're going to get as many answers as respondants. Some people find raw
stats more valuable (because you can pinpoint a specific request and all the
information about that single event, and you can also apply aggregates to
get sums by date range(s)), others conserve disk space (and processing, to
some degree) by just storing aggregates.

How do you deal with the increasing volumes of
data?

Sound database design.

Do you collect referrer data, etc.?

Again, this really depends. I don't think a survey is going to tell you
what information you're interested in... maybe you can tell us what your
requirements are.

 

[Paul Woods]

Thanks Aaron. I guess I was looking for help in determining what
information I wanted/needed vs. the requirements for storing/processing it.

It's fairly easy to store in a database much of the same information that is
stored in IIS log files (which is what I would like as a minimum). However,
I also know that log files can get huge so I was wondering what strategies
others have used to either reduce the amount of information collected or to
archive the raw data offline, etc. So in this case I would welcome a
variety of opinions as opposed to getting one "right" answer.

When I asked about how to deal with increasing volumes of data, you answered
"sound database design". Do you have any examples? Or even examples of
what NOT to do? For example, is storing raw log data in a single table a
bad idea? My thought was to aggregate/offload the data from that table
perhaps once a month, but of course the frequency would depend on traffic.

 

[Adrienne]

I'm developing an application that uses a custom 404 page to deliver
all of my site's content. However, doing things this way renders IIS's
regular log files pretty much useless.

Are there any established "best practices" for creating your own
logging system? I know that others use this technique and I'm hoping
someone has some ideas they can pass along.

For example, do you log every single page request or do you just log
totals per day, week, or month? How do you deal with the increasing
volumes of data? Do you collect referrer data, etc.?

Thanks,

Paul

I'm using my own system because my former host did not provide access to
the raw logs.

Here's what I collect: date, time, ip, url, referer, host (since there is
more than one domain name), querystring, and user agent

I find referer to be very interesting, as it tells me what keywords a
visitor used coming from a search engine. I also like user agent, because
it call tell me what spiders are coming around, and if need to block them
if they are not well behaved.

Right now, I only keep one month of data stored in the table, and create a
new table at the beginning of each month. If the traffic gets larger, I
might think about reducing to one week, and archiving the old tables in
another database.

If you have access to the raw logs, make sure that you are collecting
referer information. If you need a good analyzer, I think that
http://www.surfstats.com is quite good, although not free.

 

[Aaron Bertrand [MVP]]

When I asked about how to deal with increasing volumes of data, you
answered

"sound database design". Do you have any examples?

I don't really have any generic examples of "sound database design"... it's
just a matter of proper normalization and designing your tables correctly
for the information you want to store, and not designing them for the
information you don't care about. Depending on the needs of the
application, it might be that you want the design optimized for INSERT
performance, and the people querying the data can suffer a little worse
performance for it. Or, it might be that the people querying the data are
more demanding (or it happens more) so that is where you should spend your
time focusing on efficiency of design. I really don't know. But I can
suggest at least a couple of ways to prevent such an app from chewing disk
space all day and night (at slight query performance costs).

For log applications in particular, there is often a whole lot of useless /
redundant information. One thing is that a lot of people store the entire
user agent string in every single row. What we did in one application is
have a set of *common* browsers, taught our application how to lump them
into categories, and stored their representation instead. So we had a table
with a TINYINT column and a VARCHAR(32) column, which stored the "browser
map" (about 20 rows) and then only stored the tinyint value with the large
set of data. This reduced the large fact tables from a VARCHAR(255) to a
TINYINT, so a savings of 254 bytes per row. Basically:

CREATE TABLE BrowserMap
(
BrowserMapID TINYINT
PRIMARY KEY CLUSTERED
BrowserName VARCHAR(32)
)

CREATE TABLE RawLog
(
RawLogID INT IDENTITY(1, 1)
PRIMARY KEY NONCLUSTERED,
dtEvent DATETIME NOT NULL,
BrowserMapID TINYINT FOREIGN KEY
REFERENCES BrowserMap(BrowserMapID),
...
)

Another thing we did is store the IP address in four TINYINT columns, rather
than a big VARCHAR(15). There is a discussion about various ways to reduce
storage space for an IP in http://www.aspfaq.com/2450, and some general
database efficiency suggestions at http://www.aspfaq.com/2424#db