Processing squid's access.log into a mysql DB

eantoranz · 11-03-2005, 09:12 AM

I want to process squid's access.log into a mysql DB so I can use the DB to make proxy's analysis. I think I'll make a cron job that every hour updates the DB so the requests made during the last hour are inserted into the DB.

My feeling is that doing it with a bash script could be a little tedious.

I have read some time ago that PHP can be used as a shell script machine... but in my kubuntu box, I see no php executable... at least, not in the $PATH.

How can I enable it so I can use it?

And can Anybody provide an example of a php-shell script?

fouldsy · 11-03-2005, 10:08 AM

You can run php from the command line, you need to look at installing + configuring php-cgi which you may not already have installed. You simply link you php script in cron to run at whatever times you want. There are some Squid logfile analysers available that you might want to look at first rather than doing your own, check out the squid website.

Anyways, this is what I have running to parse DansGuardian logs into a MySQL database. It's set as Squid log file formatting, but you might need to change some of the row names accordingly:

Code:

 // Set the location of the DansGuardian access.log file
 $fcontents = file ($logfile);
 
 // Clear out all the existing data in the table
 $result = @mysql_query("DELETE FROM dglogs");
 // Make sure that query was formed correctly, otherwise let user know there's a problem
 if (!$result) {
 	echo("<br>Error performing query: " . mysql_error() . ". Please contact the server administrator with details of your actions.");
 	exit();  
 }
 
 // Execute the mySQL query to output the data into the database
 mysql_query($result);
 echo "<br>Done!";
 
 echo "<br><br>Starting to parse DansGuardian access.log file and adding users.";
 echo "<br>Depending on the size of your log file, this could take several minutes...\n\n";
 
 // Run through file and get contents of data ready to output to array
 for($i=0; $i<sizeof($fcontents); $i++) {
 	$line = addslashes(trim($fcontents[$i]));
 	$line = preg_replace("/\s+/", " ", $line);
 
 	// Create new array called '$parsed' based on contents of the DansGuardian access log
         //$parsed = list(,$rfc931,,$time,,$authuser,$request,,,$bytes,$status)=explode(" ", $line);
 	$parsed = list($date,$time,$user,$ip,$request,$result,$status)=explode(" ", $line);
 
         // Create SQL query to insert data from '$parsed' array into mySQL data
 	$result = @mysql_query("INSERT INTO dglogs VALUES ('','$date','$time','$user','$ip','$request','$result','$status')");
 
 	// Make sure that query was formed correctly, otherwise let user know there's a problem
 	if (!$result) {
 		echo("Error performing query: " . mysql_error() . ". Please contact the server administrator with details of your actions.");
 	exit();  }
 
      	// Execute the mySQL query to output the data into the database
      	mysql_query($result);
 
 }

That's set to run each evening at midnight, and then the logs are rotated so tidy things up.

eantoranz · 11-03-2005, 10:29 AM

I installed php-cli and it's working perfectly!.

We have a squid log analysis tool installed: sarg.

And it's been very usefull. Now we need a little more information that sarg doesn't provide.. and that would be it.

Afsana · 05-12-2013, 03:38 PM

i am making a plugin for squid server, how this plugin communicate with squid server.