Squid: Guardando los logs en una base de datos SQLite

Publicado originalmente el 2011-06-19 23:18, en EcuaLUG.org

En una publicación anterior de este foro se preguntaba si alguien conocía de alguna herramienta más versátil que SARG para consultar/filtrar los logs de Squid. Entre los comentarios salto la idea de construir una alternativa a SARG y además se sugirió almacenar los logs en una base de datos para una mayor facilidad en la consulta de los mismos.

En el presente post no voy (todavía :P ) a crear una alternativa a SARG, pero si voy a mostrar como almacenar los logs de Squid en una base de datos SQLite. Para la funcionalidad de almacenar los logs en esta base de datos voy a usar el proyecto sqlite3squidlog

Si bien es cierto la base de datos sugerida fué MySQL, yo me decidí por usar SQLite por dos razones: La primera es que en el Servidor donde se encuentra mi Squid, he tratado en lo posible de instalar la menor cantidad de software. La segunda es que la carga de ese servidor no es muy alta, son solo 20 usuarios los que lo utilizan. De todas formas reconozco el hecho de que para entornos más exigentes es necesario analizar la utilización de una base de datos más robusta como MySQL.

• IMPORTANTE: Estoy partiendo del hecho de que tengo instalado un Squid (Version 2.7.STABLE9) sobre un Debian 6.0.1.

Procedimiento: 

Instalamos la interfaz de linea de comandos para SQLite 3, lo cual por dependencia nos instalará la librería compartida libsqlite3-0:

apt-get install sqlite3

Descargamos el paquete de instalación de sqlite3squidlog de la página de descargas del proyecto. Desde la línea de comandos:

wget http://sqlite3squidlog.googlecode.com/files/sqlite3squidlog_0.1.0-1_i386.deb

Procedemos a su instalación.

dpkg -i sqlite3squidlog_0.1.0-1_i386.deb

Creamos (opcional) un directorio llamado db dentro de /var/log/squid/ para colocar la base de datos que almacenará los logs.

mkdir /var/log/squid/db

Creamos la base de datos access_log.db.

sqlite3 /var/log/squid/db/access_log.db <<EOF
CREATE TABLE access_log(
timestamp REAL,
resp_time INTEGER,
src_ip TEXT,
cache_status TEXT,
http_status TEXT,
reply_size INTEGER,
req_method TEXT,
req_uri TEXT,
username TEXT,
peer_access TEXT,
dst_ip TEXT,
mime_type TEXT
);
EOF

Asignamos los permisos respectivos al directorio db y a su contenido, de tal manera que el usuario/grupo proxy sean los únicos autorizados para escribir/leer en ellos.

chown -R proxy:proxy /var/log/squid/db
chmod -R 740 /var/log/squid/db

Editamos el archivo squid.conf

nano /etc/squid/squid.conf

Y en la sección LOGFILE OPTIONS modificamos las siguientes líneas (lo recomendable es comentar las ya existentes):

logformat sqlite3_columns %ts.%03tu %tr %>a %Ss %Hs %<st %rm %ru %Sh %un %<A %mt
access_log daemon:/var/log/squid/db/access_log.db sqlite3_columns
logfile_daemon /usr/bin/sqlite3squidlog

Reiniciamos el servicio.

/etc/init.d/squid restart

• Tip: Como me tocó ir probando cual sería el permiso más óptimo para el directorio db y su contenido, hubo un momento en que luego de reiniciar el Squid aparentemente todo estaba bien, pero los clientes no navegaban, y el mensaje que recibian era que el proxy estaba rechazando las conexiones. Encontré que el error eran los permisos revisando el syslog.

  tailf /var/log/syslog
  

  Y observamos que hay un registro que indica que no se puede abrir la base de datos:

  Jun 19 22:21:28 svpx03 (logfile-daemon): Can't open database: unable to open database file
  Jun 19 22:21:29 svpx03 squid[7384]: I don't handle this error well!
  Jun 19 22:21:29 svpx03 squid[7364]: Squid Parent: child process 7384 exited due to signal 6
  Jun 19 22:21:32 svpx03 squid[7364]: Squid Parent: child process 7388 started
  

  Una vez corregido lo de los permisos, ahora si los clientes se conectaron

  Jun 19 22:22:44 svpx03 (logfile-daemon): starting...
  Jun 19 22:22:44 svpx03 (logfile-daemon): Preparing database...
  Jun 19 22:22:44 svpx03 (logfile-daemon): Prepared database.
  

Pruebas:
Iniciamos sesión en la base de datos

cd /var/log/squid/db
sqlite3 access_log.db 

Consultamos nuestros logs

sqlite> select * from access_log;

Y obtenemos

1308541210.575|906|192.168.3.204|TCP_MISS|200|10578|GET|http://www.ecualug.org/?|DIRECT|-|174.142.111.36|text/html
1308541212.927|313|192.168.3.204|TCP_MISS|200|710|GET|http://www.ecualug.org/modules/codefilter/codefilter.css?|DIRECT|-|174.142.111.36|text/css
1308541212.976|362|192.168.3.204|TCP_MISS|200|908|GET|http://www.ecualug.org/modules/ad/serve.php?|DIRECT|-|174.142.111.36|application/x-javascript
1308541213.508|894|192.168.3.204|TCP_MISS|200|29476|GET|http://www.ecualug.org/files/css/css_bd6376b87b257c4eaecdddc38c9821fc.css|DIRECT|-|174.142.111.36|text/css
1308541213.938|517|192.168.3.204|TCP_MISS|200|943|GET|http://www.ecualug.org/modules/smileys/packs/Example/tongue.png|DIRECT|-|174.142.111.36|image/png

Para salir del cliente de SQLite

sqlite> .quit

Eso es todo ;)

Estuve tratando de ver si se podía guardar al mismo tiempo en la DB y en el archivo access.log de toda la vida pero no me funcionó, lo que se me vino a la mente es que el logfile_daemon debería realizar esas dos funciones para que sea posible lo que quiero. En todo caso si alguien sabe como hacerlo, que lo postee.

Saludos :D  

Referencias:

• How To Implement Simple Logging With SQLite
• Squid configuration directive logformat
• Squid configuration directive access_log
• Squid configuration directive logfile_daemon
• SQLite tutorial

PD OT: Una sugerencia jazz-rock-metal-classical-gypsy, The Butcher's Ballroom by Diablo Swing Orchestra.

Para comentarios y/o preguntas, por favor visite EcuaLUG.org