ô¿ô - Blog by Frank Rusch

Rather than using LogParser to analyze IIS logs, you can import your log files in an instance of SQL Server Express.

First, create a table that has the same columns as your log file. Depending on what data you were capturing in your log files, you will need a different structure.

For example, on my Windows XP IIS 5.1 machine, I had the following fields:

CREATE TABLE [dbo].[iis_logtable_1] (
        [time] [datetime] NULL ,
        [c-ip] [varchar] (50) NULL ,
        [cs-method] [varchar] (50) NULL ,
        [cs-uri-stem] [varchar] (255) NULL ,
        [sc-status] [int] NULL
        )

For an IIS7 server instance, I had these fields captured:

CREATE TABLE [dbo].[iis_logtable_2] (        
[date] [datetime] NULL,        
[time] [datetime] NULL ,        
[s-ip] [varchar] (50) NULL ,        
[cs-method] [varchar] (50) NULL ,        
[cs-uri-stem] [varchar] (255) NULL ,        
[cs-uri-query] [varchar] (2048) NULL ,        
[s-port] [varchar] (50) NULL ,        
[cs-username] [varchar] (50) NULL ,        
[c-ip] [varchar] (50) NULL ,        
[cs(User-Agent)] [varchar] (2048) NULL ,        
[sc-status] [int] NULL ,        
[sc-substatus] [int] NULL ,        
[sc-win32-status] [varchar] (255) NULL ,        
[time-taken] [int] NULL        
)

You may need to modify depending on which fields are being logged on your web server.

You could do a BULK INSERT now to pull all the data into SQL Server. However, your log file probably has comments in it, which IIS typically adds each time the server restarts… these lines start with hash (#) and cause BULK INSERT to choke. If you have enough of these in your log file, the insert will fail.

You can remove comments from your log file by using the PrepWebLog utility. Command will be something like this:

preplog.exe c:\temp\iislogs\mylogfile.log > c:\temp\iislogs\mylogfile_new.log

Finally, the newly un-commented file can be read by SQL Server.

BULK INSERT [dbo].[iis_logtable_2]
FROM 'C:\temp\iislogs\mylogfile_new.log'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '\n')

For more info, see How To Use SQL Server to Analyze Web Logs.

Here’s my process for creating Ubuntu VMs on and ESXi 4.0 host.

I downloaded a pre-built VMware 9.10 image from thoughtpolice. I chose the amd64 version and downloaded it via bittorrent.

This image won’t run out of the box on ESXi 4.0 – it must first be converted. For this, I needed to download the VMware vCenter Converter Standalone.

Convert the .vmx file and deploy it to the ESXi host using VMware vCenter Converter Standalone:

• Choose “Convert Machine” button.
• Select source type: “VMware workstation or other VMware virtual machine”.
• Browse to the .vmx file.
• For the destination, choose “VMware Infrastructure virtual machine” and enter appropriate login credentials.
• Type the VM name.

That copies the VMX to the ESXi instance. On my setup, it took about 15 minutes over my LAN.

Once that was all settled, I performed the following standard updates in the VMware console:

# start VM in vSphere – go to console
passwd
sudo aptitude update
sudo aptitude upgrade
sudo nano /etc/hostname # change hostname as needed
sudo /sbin/shutdown -h -P now
# set up router so VM’s MAC address is linked to a single IP
# start VM again in vSphere – go to console
sudo aptitude install openssh-server

Also, update:

sudo nano /etc/hosts

sudo dpkg-reconfigure tzdata

Furthermore, I created a user as follows:

# create user
sudo useradd -d /home/myusername -m myusername
sudo passwd myusername
# show user's shell
getent passwd myusername
# change user's shell
sudo chsh -s /bin/bash myusername

To ease file transfers, I installed samba and set up a samba share.