Netgate Discussion Forum
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Search
    • Register
    • Login

    pfSense + Squid + Mysql (RESOLVIDO)

    Scheduled Pinned Locked Moved Portuguese
    8 Posts 2 Posters 1.1k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • C
      ceccatto
      last edited by ceccatto

      This post is deleted!
      1 Reply Last reply Reply Quote 0
      • C
        ceccatto
        last edited by

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

        1 Reply Last reply Reply Quote 0
        • A
          amaica
          last edited by

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

          C 1 Reply Last reply Reply Quote 0
          • C
            ceccatto @amaica
            last edited by

            @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".

            1 Reply Last reply Reply Quote 0
            • A
              amaica
              last edited by

              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

              C 1 Reply Last reply Reply Quote 0
              • C
                ceccatto @amaica
                last edited by

                @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.

                1 Reply Last reply Reply Quote 0
                • C
                  ceccatto
                  last edited by

                  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();
}
?>
                  1 Reply Last reply Reply Quote 0
                  • A
                    amaica
                    last edited by

                    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

                    1 Reply Last reply Reply Quote 0
                    • First post
                      Last post
                    Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.