pfSense + Squid + Mysql (RESOLVIDO)

ceccatto

This post is deleted!

ceccatto

Problema resolvido... Favor fechar o tópico.

amaica

Olá como resolveu? também tenho essa dúvida

ceccatto

@amaica said in pfSense + Squid + Mysql (RESOLVIDO):

Olá como resolveu? também tenho essa dúvida

Na verdade como não tive muito tempo para resolver o problema de uma forma mais simples e eficaz, eu fiz um recurso técnico onde eu tenho a seguinte estrutura:

01 - pfSense (ip fixo dedicado 0.0.0.0)
02 - Servidor IIS (ip fixo dedicado 1.1.1.1)
03 - Servidor MySQL (ip fixo dedicado 1.1.1.1)

Hoje eu captura toda informação gerada pelo Squid utilizando uma conexão SFTP executada por um script PHP, com o qual eu efetuo o download do arquivo de log, importo ele para um banco de dados MySQL e efetuo a compactação do arquivo baixado, para mante-lo guardado mesmo este já estando no banco de dados. Ao término da importação e compactação eu ainda excluo o LOG antigo do pfSense para mante-lo sempre limpo.

Fiz algumas alterações no LOG do Squid para um ajuste nas informações capturadas, apenas as necessárias, como data/hora/usuário/mac/ip/url

Atualmente fiz um formulário de cadastro no Captive Portal onde todos os cadatros são registrados no MySQL junto com o log do Squid.

Ainda não tive tempo para reduzir este processo. Até o momento tudo funciona 100%, mas com tempo e um pouco de paciência eu consigo fazer este processo "Do jeito certo".

amaica

Vou passar minha solução:
primeiro:

pkg install mysql57-client-5.7.29
em outro host criei uma base de dados chamada squid e uma tabela chamada logs:
CREATE TABLE logs (
ID int(11) NOT NULL AUTO_INCREMENT,
DIA_SEMANA varchar(200) DEFAULT NULL,
MES varchar(200) DEFAULT NULL,
DIA varchar(200) DEFAULT NULL,
HORA_DATA varchar(100) DEFAULT NULL,
ANO varchar(100) DEFAULT NULL,
TAMANHO_BYTES varchar(100) DEFAULT NULL,
IP varchar(100) DEFAULT NULL,
METODO varchar(200) DEFAULT NULL,
METODO1 varchar(100) DEFAULT NULL,
METODO2 varchar(100) DEFAULT NULL,
URL varchar(200) DEFAULT NULL,
METODO3 varchar(100) DEFAULT NULL,
METODO4 varchar(200) DEFAULT NULL,
MIME varchar(200) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1245166 DEFAULT CHARSET=latin1;
no pfsense rodo o seguinte script agendado:
#!/bin/bash -x
perl -pe 's/^\d+.\d+/localtime($&)/e;' /var/squid/logs/access.log | awk '{print$1"|"$2"|"$3"|"$4"|"$5"|"$6"|"$7"|"$8"|"$9"|"$10"|"$11"|"$12"|"$13"|"$14}' > /var/squid/logs/accessOK
mysql -h192.168.XX.XX -uXXX -pXXXX squid --local-infile=1 -e "load data local infile '/var/squid/logs/accessOK' into table logs FIELDS TERMINATED BY '|';"

Não é uma solução "gloriosa" mas funciona.
Valeu

ceccatto

@amaica said in pfSense + Squid + Mysql (RESOLVIDO):

Vou passar minha solução:
primeiro:

pkg install mysql57-client-5.7.29

em outro host criei uma base de dados chamada squid e uma tabela chamada logs:
CREATE TABLE logs (
ID int(11) NOT NULL AUTO_INCREMENT,
DIA_SEMANA varchar(200) DEFAULT NULL,
MES varchar(200) DEFAULT NULL,
DIA varchar(200) DEFAULT NULL,
HORA_DATA varchar(100) DEFAULT NULL,
ANO varchar(100) DEFAULT NULL,
TAMANHO_BYTES varchar(100) DEFAULT NULL,
IP varchar(100) DEFAULT NULL,
METODO varchar(200) DEFAULT NULL,
METODO1 varchar(100) DEFAULT NULL,
METODO2 varchar(100) DEFAULT NULL,
URL varchar(200) DEFAULT NULL,
METODO3 varchar(100) DEFAULT NULL,
METODO4 varchar(200) DEFAULT NULL,
MIME varchar(200) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1245166 DEFAULT CHARSET=latin1;

no pfsense rodo o seguinte script agendado:
#!/bin/bash -x
perl -pe 's/^\d+.\d+/localtime($&)/e;' /var/squid/logs/access.log | awk '{print$1"|"$2"|"$3"|"$4"|"$5"|"$6"|"$7"|"$8"|"$9"|"$10"|"$11"|"$12"|"$13"|"$14}' > /var/squid/logs/accessOK
mysql -h192.168.XX.XX -uXXX -pXXXX squid --local-infile=1 -e "load data local infile '/var/squid/logs/accessOK' into table logs FIELDS TERMINATED BY '|';"

Não é uma solução "gloriosa" mas funciona.
Valeu

Então... A minha é basicamente a mesma coisa... Única diferença é que eu reajusto o log do squid apenas com as informações necessárias e depois efetuo o download do arquivo de log para um servidor windows onde eu efetuo a importação dele no mysql e ainda guardo o original.

ceccatto

O arquivo de rotação do SQUID está configurado para ser executando todos os dias às 00:00 e às 01:00 eu executo este script abaixo em um servidor IIS.

Essa é a parte do script que eu uso para fazer a conexão SSH no pfSense e efetuar o download do arquivo access.log.0 gerado às 00:00 e efetuo a importação dos dados para o MySQL.

Depois deste script eu efetuo a compactação do arquivo original e envio um e-mail em caso de erros no processo.

<?php
$host = "PFSENSE_IP";
$port = PFSENSE_SSH_PORT;
$username = "USUARIO_SSH_PFSENSE";
$password = "SENHA_ROOT_PFSENSE";
$connection = NULL;
$remote_file = "/var/squid/logs/access.log.0";
$local_file = "pfSense/access_".date("d-m-Y").".log";

try {
	$connection = ssh2_connect($host, $port);
	if(!$connection){
		throw new \Exception("Não foi possível localizar $host:$port.<br/>");
	}
	$auth  = ssh2_auth_password($connection, $username, $password);
	if(!$auth){
		throw new \Exception("Não foi possível autenticar-se com o usuário $username.<br/>");
	}
	$sftp = ssh2_sftp($connection);
	if(!$sftp){
		throw new \Exception("Não foi possível iniciar o SFTP.<br/>");
	}
	if (file_exists("ssh2.sftp://".$sftp .$remote_file)) {
		echo "Arquivo access.log.0 encontrado com sucesso no SFTP";
		if(ssh2_scp_recv($connection, $remote_file, $local_file)) {
			echo "O download do arquivo foi efetuado com sucesso.<br/>";
			$conexao = conexao::getInstance();
			$sql = "LOAD DATA LOCAL INFILE '$local_file' INTO TABLE logs FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n'";
			$stm = $conexao->prepare($sql);
			$retorno = $stm->execute();
			if ($retorno):
			echo "Informações importadas com sucesso.<br/>";
			else:
			echo "Não foi possível importar as informações.<br/>";
			endif;
		} else {
			echo "Não foi possível efetuar o download do arquivo.<br/>";
		}
		if($echo_ssh2_scp_recv === "true") {
			if(ssh2_sftp_unlink($sftp, $remote_file)) {
				echo "Arquivo excluído com sucesso do SFTP.<br/>";
			} else {
				echo "Não foi possível excluir o arquivo do SFTP.<br/>";
			}
		}
	} else {
		echo "Arquivo access.log.0 não encontrado no SFTP";
	}
	$connection = NULL;
} catch (Exception $e) {
   echo "Erro devido a: ".$e->getMessage();
}
?>

amaica

Sua solução está muito boa, além de inserir na base tu gerencia este arquivo log. No meu caso só mando o conteúdo "tratado" para o banco. Parabéns