IIS Logs and SQL

How can I import logs into SQL Server and analyze them.

[yarbdog]

I am looking to import my IIS logs into SQL 2000 and then run my log analyzer against SQL, not a text file or proprietary database. My logs are getting huge and I want to write custom queries against my logs as well as use a tool like Webtrends.

I need a tool to import form IIS text logs into SQL and a tool to analyze logs which are stored in SQL not some proprietary database created by the tool.

I know that Site Server can and some very high end Webtrends tools do this, but I would prefer not to spend $3000+!

Any ideas?

[JuniorHarris]

I suppose depending on the LOG format, you may be able to use the Data Transformation Services (DTS) to import that data. I'm not sure if you'll have to create the table first or not...

...as for analyzers, I don't know of any that access SQL directly. But it really sounds like a good multi-dimensional data cube might fit the bill!~

[yarbdog]

JuniorHarris,

Great idea, but we have tried DTS, unfortunately, the IIS log file is space delimited, which means any space in the field values wrecks the import. Furthermore, anytime you restart IIS, it inserts a status report into the log. Thus, using DTS is a real pain.

PS: Thanks for your great discussion on cloaking. We implemented it here and it works great.

[Xoc]

Welcome to WebmasterWorld.

There is a configuration in IIS that will store the logs in SQL Server. In the IIS configuration dialogs, under Web Site, where you enable logging, it allows you to specify the save format. One of the choices is to ODBC, which you can configure to use SQL Server.

For your existing log files, a VB or VBScript programmer can import those into SQL Server for you in no time at all. Examine the Split function to divide up the lines into their components, then use some ADO code to add it to SQL Server.

<added>
I just tried it, storing to an Access database. Works like a charm. You have to create a table with the correct fields. They give you a .sql script for SQL Server, but you have to do it by hand for Access. Was able to get it working in under 10 minutes. This feature only works in IIS Server on Win2000, which comes with Win2000 server. You will have to export the data back to a text file to use it with most analysis tools, though.
</added>

[yarbdog]

Greg,

I have not checked this thread in a bit. Sorry. Thanks for your thoughts. Tell me what you think of this.

Our problem with using split is that IIS makes the text file space delimited. However, many of the field values have spaces in them. Therefore we can't just do a simple split of the data since we can't reliably use it as a delimiter. We collect a lot of data and some of it is irregular.

As for using ODBC, we thought of that, but some say it is a resource hog. We were hoping to do the import off line on a non-production server.

With some work, we can work through these issues. The real issue for us is finding an interpretation tool like webtrends that works with SQL. We want to write custom queries, but we don't want to write every query.

[Xoc]

IIS doesn't put spaces into the actual fields. Instead it puts + signs where a space would appear. So split should work. However, last weekend while searching for something else, I found that IIS has a library that deals with log files called the MSWC IIS Log Object Library. It will split the file for you.

Send me stickymail for another alternative for loading the database.

[jaanis]

Have any of you seen a problme using IIS compared to Apache as far as rankings are concerned? We host a lot of pages and get great rankings with Apache yet when we set a server up with IIS we seem to get less rankings. Does the spider have any more difficulty with IIS than Apache?