Welcome to WebmasterWorld Guest from 188.8.131.52
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+!
...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!~
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.
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.
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.
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.
Send me stickymail for another alternative for loading the database.