Analisando log Squid do Mikrotik no SARG

O problema do SARG não analisar os logs do Mikrotik é que os logs estão em formatos diferentes, o Mikrotik envia informações ao qual o SARG não entende, gerando uma confusão neste e resultando em não processamento das informações. Para esta integração serão necessários três softwares básicos: SARG, THE Dude e MK2SARG (software que desenvolvi).

[ Hits: 67.180 ]

Por: Deliam em 21/11/2008


Introdução



Depois de muito estudo, consegui fazer a integração do SARG com Mikrotik para análise dos logs de acesso dos clientes. O problema do SARG não analisar os logs do Mikrotik é que os logs estão em formatos diferentes, o Mikrotik envia informações ao qual o SARG não entende, gerando uma confusão neste, e resultando em não processamento das informações.

A solução que encontrei foi desenvolver um aplicativo que converte o arquivo de log no formato correto para análise.

Para esta integração serão necessários três softwares básicos: SARG, THE Dude, MK2SARG (software que desenvolvi).

SARG

Vou começar falando do SARG, uma ferramenta brazuca extremamente útil para análise de log de acesso de usuários. O arquivo principal é o sarg.conf, localizado na pasta sarg/etc. Este arquivo e necessário para o funcionamento do programa. Apesar de ser nacional, o arquivo de configuração está em inglês, deu um pouco de trabalho mas traduzi. Ele é bem intuitivo, as opções estão todas comentadas.

ARQUIVO: /sarg/etc/sarg.conf

#####################################################
# Seleciona a linguagem do Relatório
# TAG: language
#####################################################

# Linguagens Disponíveis: Bulgarian_windows1251, Catalan, Czech, Czech_UTF8, Dutch, English
# French, German, Greek, Hungarian, Indonesian, Italian, Japanese
# Latvian, Polish, Portuguese, Romanian, Russian_koi8, Russian_UFT-8
# Russian_windows1251, Serbian, Slovak, Spanish, Turkish
#
language Portuguese
#####################################################
# Caminho onde encontra o arquivo de LOG a ser processado
# TAG: access_log file
#####################################################
#
access_log c:/sarg/access.log
#####################################################
# Cor do gráfico do usuário
# TAG: graphs yes|no
#####################################################
# graph_days_bytes_bar_color blue|green|yellow|orange|brown|red
#
#graphs yes
graph_days_bytes_bar_color blue
#####################################################
# Especifica o título do relatório
# TAG: title
#####################################################
title "Relatório de Acesso à WEB - ADSNet"
#####################################################
# Especifica a fonte do relatório
# TAG: font_face
#####################################################
# Fontes disponíveis Tahoma,Verdana,Arial
#
font_face Verdana
#####################################################
# Especifica a cor do cabeçalho
# TAG: header_color
#####################################################
#
header_color red
#####################################################
# Especifica a cor de fundo do cabeçalho
# TAG: header_bgcolor
#####################################################
#
header_bgcolor blanchedalmond
#####################################################
# Especifica o tamanho da fonte do texto
# TAG: font_size
#####################################################
#
font_size 10px
#####################################################
# Especifica o tamanho da fonte do cabeçalho
# TAG: header_font_size
#####################################################
#
header_font_size 12px
#####################################################
# Especifica o tamanho da fonte do título
# TAG: title_font_size
#####################################################
#
title_font_size 20px
#####################################################
# Especifica a cor da fonte do título
# TAG: title_color
#####################################################
#
title_color red
#####################################################
# Especifica a cor de fundo do relatório
# TAG: background_color
#####################################################
#
background_color white
#####################################################
# Especifica a cor do Texto HTML
# TAG: text_color
#####################################################
#
text_color black
#####################################################
# Especifica a cor de fundo do Texto HTML
# TAG: text_bgcolor
#####################################################
#
text_bgcolor lavender
#####################################################
# Especifica a logomarca do relatório
# TAG: logo_image
#####################################################
#
logo_image c:/sarg/etc/images/Logo_ADSNet.gif
#####################################################
# Especifica o texto da logomarca do relatório
# TAG: logo_text
#####################################################
#
#logo_text "Internet Banda Larga"
#####################################################
# Especifica a cor do Texto da logomarca do relatório
# TAG: logo_text_color
#####################################################
#
logo_text_color green
#####################################################
# Especifica o Tamanho da logomarca do relatório
# TAG: image_size
#####################################################
# Largura / Altura
#
image_size 80 45
#####################################################
# Especifica a Imagem de fundo
# TAG: background_image
#####################################################
#
background_image c:/sarg/etc/images/fundo.bmp
#####################################################
# Especifica o diretório temporário
# TAG: temporary_dir
#####################################################
#
temporary_dir c:/sarg/tmp
#####################################################
# Especifica o diretório contendo os Arquivos Binários
# TAG: bin_dir
#####################################################
#
bin_dir c:/sarg/bin
#####################################################
# Especifica o diretório de Saída do relatório
# TAG: output_dir
#####################################################
#
output_dir c:/sarg/reports
#####################################################
# Converte Endereço IP em DNS Name
# TAG: resolve_ip yes/no
#####################################################
#
resolve_ip no
#####################################################
# Usar endereço IP em vez de userid nos relatórios
# TAG: user_ip yes/no
#####################################################
#
#user_ip yes
#####################################################
# Ordem de classificação para relatório de TopUser
# TAG: topuser_sort_field field normal/reverse
#####################################################
#
topuser_sort_field BYTES reverse
#####################################################
# Ordem de classificação para relatório de usuário
# TAG: user_sort_field field normal/reverse
#####################################################
#
user_sort_field BYTES reverse
#####################################################
# Usuários dentro do Arquivo serão excluídos do relatório
# TAG: exclude_users file
#####################################################

#
#exclude_users none
#####################################################
# Hosts, domínios ou sub-redes que serão excluídos relatórios
# TAG: exclude_hosts file
#####################################################

# Eg.: 192.168.10.10 - Exclui somente um IP
# 192.168.10.0 - Exclui a classe inteira
# s1.acme.foo - Exclui somente um HOST
# acme.foo - Exclui o domínio inteiro
#
#exclude_hosts none
#####################################################
# Formato das datas no relatório
# TAG: date_format
#####################################################

# Formato: e (European=dd/mm/yy), u (American=mm/dd/yy), w (Weekly=yy.ww)
#
date_format e
#####################################################
# Esta opção permite que você desabilite usuário acesso usuário se exceder um limite download
# TAG: per_user_limit file MB
#####################################################
#
#per_user_limit none
#####################################################
# Esta opção permite configurar o número de relatórios a ser exibido
# TAG: lastlog n
#####################################################
# O mais antigo será automaticamente removido
# 0 (zero) = Sem limite
lastlog 0
#####################################################
# Remove os arquivos temporários
# TAG: remove_temp_files yes
#####################################################
#
remove_temp_files yes
#####################################################
# Gera o índice principal
# TAG: index yes|no|only
#####################################################
# only - gera somente o índice principal "index.html"
#
index yes
#####################################################
# Maneira em que a árvore index.html é gerada
# TAG: index_tree date|file
#####################################################
#
index_tree file
#####################################################
# Sobrescreve o relatório antigo
# TAG: overwrite_report yes|no
#####################################################
#
overwrite_report yes
#####################################################
# Registro sem usuário
# TAG: records_without_userid ignore|ip|everybody
#####################################################
# O que fazer com os registros sem usuário (sem autenticação) no arquivo access.log?
#
# ignore - Este registo será ignorado.
# ip - Use o endereço IP em vez. (default)
# everybody - Usar todos.
#
records_without_userid ip
#####################################################
# Use vírgula ao invés de ponto nos relatórios.
# TAG: TAG: use_comma no|yes
#####################################################
use_comma no
#####################################################
# Estabelece o numero de sites mais acessados.
# TAG: topsites_num n
#####################################################
topsites_num 100
#####################################################
# Classificar o relatório TopSites por Conexão ou por Bytes
# TAG: topsites_sort_order CONNECT|BYTES A|D
#####################################################
# Onde A=Ascendente, D=Descendente
#
topsites_sort_order CONNECT D
#####################################################
# Classificar o Index.html na ordem crescente por Conexão ou por Bytes
# TAG: index_sort_order A/D
#####################################################
# Onde A=Ascendente, D=Descendente
#
index_sort_order D
#####################################################
# Ignorar registros com estes códigos.
# TAG: exclude_codes file
#####################################################
#exclude_codes c:/sarg/etc/exclude_codes
#####################################################
# Tipos de relatórios para gerar.
# TAG: report_type type
#####################################################
# topusers - relatório de usuário que mais usa a internet
# topsites - relatório de sites mais visitados
# sites_users - relatório de usuários / Sites
# users_sites - relatório de sites acessados por usuários
# date_time - relatório de bytes usados por dia e por hora
# denied - relatório de sites com URL negada
# auth_failures - relatório de falha de autenticação
# site_user_time_date - relatório de sites, datas, hora e bytes
# downloads - relatório de downloads por usuário
#
report_type topusers topsites sites_users users_sites date_time denied auth_failures site_user_time_date downloads
#####################################################
# Mostrar URL Inteira.
# TAG: long_url yes|no
#####################################################
# yes: Mostra a URL inteira
# no: Mostra somente o site visitado
# OBS: Não e recomendado, devido ao relatório ficar muito grande
#
long_url no
#####################################################
# Substitui o campo USERID (IP) pelo nome do usuário.
# TAG: usertab filename
#####################################################
# Ex: 192.168.10.1 Karol Wojtyla
#
#usertab c:/sarg/etc/ip_name.txt
#####################################################
# Usar BYTES ou TEMPO CORRIDO no relatório DATA/TIME?
# TAG: date_time_by bytes|elap
#####################################################
#
date_time_by bytes
#####################################################
# Registros que contém caracteres inválidos em userid será ignorado pelo Sarg.
# TAG: user_invalid_char "&/"
#####################################################
#
user_invalid_char "&/"
#####################################################
# Os relatórios serão gerados apenas para os usuários listados.
# TAG: include_users "user1:user2:...:usern"
#####################################################
#
#include_users none
#####################################################
# Os registros do arquivo access.log que contêm um dos textos listados serão ignorados.
# TAG: exclude_string "string1:string2:...:stringn"
#####################################################
#
#exclude_string none
#####################################################
# Mostra mensagem de sucesso ao final do processo.
# TAG: show_successful_message yes|no
#####################################################
#
show_successful_message yes
#####################################################
# Mostra estatísticas de leitura.
# TAG: show_read_statistics yes|no
#####################################################
#
show_read_statistics yes
#####################################################
# Quais campos devem ser Topuser no relatório.
# TAG: topuser_fields
#####################################################
#
#topuser_fields NUM DATE_TIME USERID CONNECT BYTES %BYTES IN-CACHE-OUT USED_TIME MILISEC %TIME TOTAL AVERAGE
#####################################################
# Quais campos devem estar no relatório do usuário.
# TAG: user_report_fields
#####################################################
#
user_report_fields CONNECT BYTES %BYTES IN-CACHE-OUT USED_TIME MILISEC %TIME TOTAL AVERAGE
#####################################################
# Mostra o campo BYTES no relatório Site & Users
# TAG: bytes_in_sites_users_report yes|no
#####################################################
#
bytes_in_sites_users_report yes
#####################################################
# Configura o número máximo de usuários TOP User a ser exibido
# TAG: topuser_num n
#####################################################
# 0 (zero) = sem limite
topuser_num 0
#####################################################
# Gera relatório for site_user_tima_date in LISTA ou TABELA
# TAG: site_user_time_date_type list|table
#####################################################
#
site_user_time_date_type table
#####################################################
# Salva o resultado do relatório em um banco de dados popular
# TAG: datafile file
#####################################################
#
#datafile c:/sarg/datafile
#####################################################
# Caractere ASCII usado como um separador de campos no datafile
# TAG: datafile_delimiter ";"
#####################################################
#
#datafile_delimiter ";"
#####################################################
# Quais campos de dados devem estar em datafile
# TAG: datafile_fields all
#####################################################
#
# user;date;time;url;connect;bytes;in_cache;out_cache;elapsed
# datafile_fields user;date;time;url;connect;bytes;in_cache;out_cache;elapsed
#####################################################
# Salva o URL ou o nome como ip no datafile
# TAG: datafile_url ip|name
#####################################################
#
#datafile ip
#####################################################
# Dia da semana a ser gerado o relatório
# TAG: weekdays
#####################################################
# Example: weekdays 1-3,5 - ( Domingo->0, Sabado->6 )
#
weekdays 0-6
#####################################################
# Período do dia a ser gerado o relatório
# TAG: hours
#####################################################
# Example: 7-12,14,16,18-20
#
hours 0-23
#####################################################
# Mostra as informações do SARG no rodapé do relatório
# TAG: show_sarg_info yes|no
#####################################################
#
show_sarg_info no
#####################################################
# Mostra a logomarca do SARG no topo do relatório
# TAG: show_sarg_logo yes|no
#####################################################
#
show_sarg_logo no
#####################################################
# Salva o log transformados em um formato SARG após analisar o arquivo de log
# TAG: parsed_output_log directory
#####################################################
#
parsed_output_log c:/sarg
#####################################################
# Comprime o arquivo de Log após processar o arquivo
# TAG: parsed_output_log_compress /bin/gzip|/usr/bin/bzip2|nocompress
#####################################################
#
parsed_output_log_compress c:/sarg/bin/bzip2
#####################################################
# Mostra como os valores serão exibidos no relatório
# TAG: displayed_values bytes|abbreviation
#####################################################
#
# bytes - 209.526
# abbreviation - 210K
#
displayed_values abbreviation
#####################################################
# Limites do relatório
# TAG: authfail_report_limit n
# TAG: denied_report_limit n
# TAG: siteusers_report_limit n
# TAG: squidguard_report_limit n
# TAG: user_report_limit n
# TAG: dansguardian_report_limit n
# TAG: download_report_limit n
#####################################################
# 0 (Zero) = sem limites
#
#authfail_report_limit 0
#denied_report_limit 0
#siteusers_report_limit 0
#squidguard_report_limit 0
#user_report_limit 0
#dansguardian_report_limit 0
#download_report_limit 0

#####################################################
# Funciona como se fosse um profile
# TAG: external_css_file path
#####################################################
# Sarg Usa essas classes de estilo
# .body Classe corpo do relatório class
# .info Classe de informação do relatório, align=center
# .title Classe Titulo, align=center
# .header Classe cabeçalho, align:left
# .header2 Classe cabeçalho, align:right
# .header3 Classe cabeçalho, align:right
# .text Classe texto, align:left
# .data Classe tabela do texto, align:right
# .data2 Classe tabela do texto, align:right, border colors
# .link Classe link
#
# Exemplo de Configuração /sarg/etc/css.tpl
# OBS: Ao habilitar esta opção, toda formatação passa a ser do profile
external_css_file c:/sarg/etc/css.tpl

#####################################################
# sufixo de arquivo a ser considerado como "download" no relatorio
# TAG: download_suffix "suffix,suffix,...,suffix"
# Use 'none' para desabilitar.
#
download_suffix "rar,zip,arj,bzip,gz,ace,doc,iso,adt,bin,cab,com,dot,drv$,lha,lzh,mdb,mso,ppt,rtf,src,shs,sys,exe,dll,mp3,avi,mpg,mpeg"

    Próxima página

Páginas do artigo
   1. Introdução
   2. The Dude!
   3. Convertendo o logs com MK2Sargv2
Outros artigos deste autor
Nenhum artigo encontrado.
Leitura recomendada

Squid com autenticação

Servidor proxy (Squid)

Gerenciando relatórios do SARG

Autenticando usuários do Squid em um banco de dados MySQL

Instalação do Squid 3.2.0.14 no Slackware com execução em ambiente chroot

  
Comentários
[1] Comentário enviado por dtux em 21/11/2008 - 15:08h

Onde posso baixa esse MK2Sargv2, muito bom seu artigo, você sabe de algum programa que de para interagir o Mikoritk com Bandwidth

[2] Comentário enviado por karls em 05/12/2008 - 15:20h

Muito bom teu artigo, esclareceu muito. Estou começando agora a mexer com mikrotik e é bom ver um artigo bom desses para quem está começando. valeu!

[3] Comentário enviado por clubelinux em 31/12/2008 - 18:47h



Olá Pessoal,


Gostei muito do artigo só não encontrei uma forma de baixar o programa que faz que converte,
Caso alguém tenha essa informação por favor me enviar a solução [email protected]

André Marinho

[4] Comentário enviado por deliam em 11/01/2009 - 17:43h

Galera, desculpe a demora, muito trabalho, viagens, etc... enfim, ta ai... Faca bom proveito, e se possivel de seu comentario...
Grande abraco...

http://www.4shared.com/dir/11787626/85d1e37e/sharing.html

Deliam Fábio

Engenheiro de Computacao
Consultor Tecnico em Provedores
Especialista em Mikrotik
Consultor Anatel / Faço e Assino Projeto SCM
msn: [email protected]
(24) 8115-6006

[5] Comentário enviado por carlosodias em 13/02/2009 - 23:14h

Olá pessoal,

Parabens Deliam pelo excelente artigo.

Conforme o tutorial conseguir configurar a Dude para receber os logs do Mikrotik em tempo real, porém o programa mk2sargv2.exe de sua autoria que converte os logs, processa as linhas do syslog gerado pelo dude mas não gera o arquivo access.log para o sarg processar. Também não apresenta nenhuma mensagem de erro. Gostaria de sua ajuda.

Este programa que desenvolveu ele fazer a conversão automatica ? Assim poderia deixar todo processo mesmo no windows todo automatico :D

Carlos Dias
msn: [email protected]

[6] Comentário enviado por deliam em 25/02/2009 - 23:21h

Talvez vc deva estar usando a versao 3 do MIKROTIK ROUTER OS. Os logs da versao 3, totalmente diferente, por isso n tem compatibilidade com meu programa. Para a versao 3, tenho que escrever 1 novo programa, ok? Pretendo fazer 1 programa novo, assim que tiver pronto, vou postar por aqui. Ate mais.

Deliam Fábio

[7] Comentário enviado por Fabio F em 07/05/2009 - 10:25h

Bom dia !!
Deliam, primeiramente gostaria de parabenizá-lo pelo excelente tutorial.
E agradecer pois consegui utilizar seu programa para gerar os relatórios de acesso.
Estou testando o uso do mikrotik em minha rede, e um dos "problemas" era a falta de relatórios. Pois temos a necessidade de controlar o acesso dos colaboradores.

Obrigado mais uma vez.

Abraço.

[8] Comentário enviado por Fabio F em 07/05/2009 - 10:39h

Só uma dúvida,
Tem como eu gerar o arquivo de log na hora que eu quizer ??
Esse processo feito pelo seu programa tem que ser manual ?

Abraço.

[9] Comentário enviado por dataspeed em 29/05/2009 - 13:30h

bao tarde!

olha gostei muito do sistema!

parabens pela iniciativa e acho que vc passou noites sem dormir!

valeu apena!
porem acho que estou fasendo alguma coisa errada esta dando erro e não consigo gerar relatorio da um erro na hora d converter!
me de uma ajuda ae!

muito obrigado por este programa e pela sa dedicação!

sou estudante de engenharia e sei o que é isso!

atenciosamente


marcos oliveira


Contribuir com comentário