Question: automating logging data into SQL table?

[Jerren]

Question:

Is there a way to easily dump the zoom statistics into a SQL table for analytical purposes? I really love the pre-canned reports zoom provides but the content team is requesting the ability to analyze search data verses browse or external links to better understand how the tool is being used.

The preference would be for a real time update of the SQL database but if its possible to use zoom's scheduler to kick off a DTS task or something similar I think I could convince them to live with it.

Thanks in advance.

[David]

The logs of searches performed are in CSV format. Which means it is an easy job to import them into Excel or an SQL database.

You would need to write some SQL code, but it would be something like this.

CREATE TABLE Stats
 
BULK INSERT Stats
FROM 'c:\SearchLog.csv' 
WITH 
( 
 FIELDTERMINATOR = ',', 
 ROWTERMINATOR = '\n' 
)

To do this insert real time would require a modification to our search script code.

We also have some alpha code written in PHP to process the CSV file in real time to produce a HTML report on the server. It will be part of V6 of Zoom, but it doesn't use SQL. E-Mail us if you want to test it.

[Jerren]

The logs of searches performed are in CSV format. Which means it is an easy job to import them into Excel or an SQL database.

You would need to write some SQL code, but it would be something like this.

CREATE TABLE Stats
 
BULK INSERT Stats
FROM 'c:\SearchLog.csv' 
WITH 
( 
 FIELDTERMINATOR = ',', 
 ROWTERMINATOR = '\n' 
)

To do this insert real time would require a modification to our search script code.

We also have some alpha code written in PHP to process the CSV file in real time to produce a HTML report on the server. It will be part of V6 of Zoom, but it doesn't use SQL. E-Mail us if you want to test it.

Nice, will give it a try for the log file import.

I'm thinking a possible work around might be to write a generic asp page to display all content and track the usage stats that way, then use the zoom URL re-write function to pass the actual html pages to that asp page as a querystring argument... or am I just chasing my tail at this point?

[David]

I would keep it simple and use the web server logs to track hits on pages, if that is the aim.

But you could wrap our search script in your own script if you wanted. Then track hits from within your script.