There are many web analysis tools available, for example, the excellent Webalizer. However, there are times when you just want to be able to search through your logs for specific conditions. At times like these, the power of a database comes in handy, but how do you get your log data in there in the first place?

You *could* use mod_log_mysql or one of it’s variants, but they involve recompiling apache from source. Having been through all that once, and got your site up and running it is probably not something you want to go through again in a hurry. This solution will take your historical data, already written to the filesystem in apache’s “combined” log format, and insert it into a mysql table for you.

Ths table should be in the following format*.

DROP TABLE IF EXISTS `tat`;
CREATE TABLE `tat` (
  `client` varchar(15) collate latin1_general_ci NOT NULL default '',
  `logname` varchar(255) collate latin1_general_ci NOT NULL default '',
  `user` varchar(255) collate latin1_general_ci NOT NULL default '',
  `date` varchar(35) collate latin1_general_ci NOT NULL default '',
  `method` varchar(10) collate latin1_general_ci NOT NULL default '',
  `uri` varchar(255) collate latin1_general_ci NOT NULL default '',
  `protocol` varchar(20) collate latin1_general_ci NOT NULL default '',
  `r_status` varchar(5) collate latin1_general_ci NOT NULL default '',
  `r_bytes` varchar(10) collate latin1_general_ci NOT NULL default '',
  `referrer` varchar(255) collate latin1_general_ci NOT NULL default '',
  `agent` varchar(255) collate latin1_general_ci NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

And now, the script…

#!/usr/bin/php -q

After configuring the first 6 lines, you can run this script. I’d probably recomend doing it daily, immediately after a logrotate, although this will not give you 100% up-to-the-minute stats. In most cases, though, this is good enough.

*NB. The table layout in this example needs tweaking. It is the first run of the draft, and I was not too choosy about column specifications. I’d recommend you take a look at the data and determine your own layout.

It seems that Google Earth have updated the photography of the Torino area. They even supply a .kmz file for Google Earth with the major event sites listed. I like it.

http://services.google.com/earth/kmz/OlympicVenues.kmz

(from the original post at http://googleblog.blogspot.com/2006/02/virtually-torino.html )

The Generator Form v2.90 is a nifty little tool… Great if you need some CSS to use as a starting point for a layout, IMHO

I have just found out about the very cool 30 Boxes calendar app. Apparently, they aim to do for the calendar what Gmail has done for email, and I have to say that my limited look at it so far has impressed me. I recommend you at least have a look.

Here. Have a link to the superbly silly IP Spotting – How interesting is your IP address?

It’s fun for a few minutes, and then starts to get a bit stale – however, there may be updates at a later date.

Technorati Profile

I have just had to write an application to complete some missing data in an Excel spreadheet from our internal database. One thing that irritated me was the dearth of information on the web regarding how to close a document properly, and ensure that the instance of Excel that the application starts is correctly colsed down.

To open the document, I used some fairly standard code.

private Excel.Application _excel;

private void openOriginalExcel(string _filename)
{
	try
	{
		_excel = new Excel.Application();
	}
	catch(Exception e3)
	{
		MessageBox.Show(e3.Message);
		return;
	}
	wb = _excel.Workbooks.Open(_filename, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
	_excel.Visible = true;
	ws = (Excel.Worksheet)_excel.ActiveSheet;
}

You can now access the readily documented functions to access and update the contents of the worksheet. In order to correctly close the document, you should use the following…

private void killOriginalExcel()
{
	wb.Close(null,null,null);
	_excel.Workbooks.Close();
	_excel.Quit();
	System.Runtime.InteropServices.Marshal.ReleaseComObject (_excel);
	System.Runtime.InteropServices.Marshal.ReleaseComObject (ws);
	System.Runtime.InteropServices.Marshal.ReleaseComObject (wb);
	ws=null;
	wb=null;
	_excel=null;
	GC.Collect();
}

There are now no running instances of Excel left behind by the code.