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: 70.432 ]

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

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

Recebendo relatório do SARG via e-mail (Gmail)

SQUID e as autenticações em NTLM e RADIUS

Squid + IPtables com dois links de internet

Problemas com o Squid

  
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 clubelinux@hotmail.com.

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: deliamfabio@hotmail.com
(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: carlosodias@hotmail.com

[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




Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts