Blog do Dibiei

Maicon Carneiro, Oracle ACE Pro ♠

Listener Scripts

Script lsnr_miner.sh – Minerando Log do Listener do Oracle

ByMaicon Carneiro

Sep 13, 2024 #lminer, #lsnr_clients, #lsnr_miner

Introdução

No início de 2022 eu publiquei sobre o script lsnr_clients.sh que havia criado para automatizar a contagem de conexões por IP a partir do log do Listener. Esse script tinha (e ainda tem) a missão de simplificar a tarefa de identificar quais IPs conectam no banco de dados.

Alguns meses depois eu precisei adaptar o script para atender outros casos de uso pontuais mais voltados a investigação de incidentes ou desvios de comportamentos. Com isso, entre uma necessidade e outra, desenvolvi uma versão mais flexível para este fim, de modo que não fosse necessário fazer modificações no código para cada tipo de análise diferente.

Assim nasceu o lsnr_miner.sh, esse script conta com um conjunto de parâmetros opcionais que permitem mudar o foco da análise entre IP, Hostname, OS User e Service Name. Esses atributos podem ser usados como um agrupamento na contagem das conexões (como se fosse um GROUP BY), ou como um filtro nos registros do log (como se fosse um WHERE).

Dentre as capacidades desse script, uma das principais (e que me deu mais trabalho de conseguir implementar) é o filtro com precisão pelo horário exato que foi gerado o registro no log (timestamp). Por exemplo: Se eu precisar investigar a origem de um pico de conexões em um curto intervalo de tempo, eu consigo realizar uma contagem especificando um período como “03-SEP-2024 11:31:00” a “03-SEP-2024 11:32:30”.

Outra grande diferença em relação ao script lsnr_clients.sh, é que enquanto o seu antecessor precisa ser executado no servidor de banco de dados enquanto o Listener está online (porque ele foi projetado para facilitar o uso por profissionais não-DBA), o lsnr_miner.sh por sua vez trabalha de forma offline.

Isso permite que a análise seja realizada a partir de qualquer máquina com Linux ou MacOS, onde eu indico o caminho do arquivo do log que pretendo analisar, ou o diretório que contem os arquivos de log.

A opção de informar um diretório ao invés de um arquivo específico permite analisar um conjunto de arquivos de logs diferentes de forma mais fácil. Por exemplo: Se for necessário analisar um período de 15 dias e o log está espalhado em 15 arquivos, porque o servidor faz a rotação dos logs diariamente.

Outro caso de uso para a abordagem de informar um diretório é o de analisar o log de todos os nodes do RAC de forma coesa. Basta copiar todos os logs para um diretório único, como um ACFS no próprio cluster, ou copiar esses logs para uma outra máquina (como a minha estação de trabalho ou máquina ponte).

Opções do script

O script pode ser usado com uma grande variedade de combinações de parâmetros, conforme documentado na tabela abaixo:

ParâmetroO que faz
-logIndica o nome do arquivo do log ou o diretório contendo os arquivos de log. Pode ser usado caminhos relativos.

Esse é o único parâmetro obrigatório.
-filterPermite filtrar os atributos que aparecem na linha do log do listener.

Atributos suportados:
IP, HOST, PROGRAM, USER, SERVICE_NAME

Exemplos:
-filter IP=192.168.1.80
-filter “user=zabbix,ip=10.1.1.80,service_name=SVCPROD”
-filter_filePermite fazer um filtro com múltiplos valores para um mesmo atributo, simulando uma operação “IN” do SQL.

Deve ser informado um arquivo que contém os valores a serem filtrados, cada linha desse arquivo auxiliar é considerando como um valor a ser filtrado.

Exemplo:
-filter_file /tmp/lista_ip.txt
-filter_attrIndica qual o tipo de atributo que deve ser filtrado com os valores contidos no arquivo informado em -filter_file.

Exemplo:
-filter_file /tmp/lista_ip.txt -filter_attr IP
-beginIndica o início do intervalo de data e horário que precisa se filtrado. Deve ser informado no formato “DD-MON-YYYY HH:MI:SS”.

Exemplo:
-begin ’19-SEP-2024 11:00:00′
-endIndica o fim do intervalo de data e horário que precisa se filtrado. Deve ser informado no formato “DD-MON-YYYY HH:MI:SS”.

Exemplo:
-end ’12-SEP-2024 23:00:00′
-group_byIndica qual atributo deve ser usado para agrupar a contagem de conexões, pode ser qualquer uma das opções suportadas:
TIMESTAMP, IP, HOST, PROGRAM, USER, SERVICE_NAME.

Default: IP.

Exemplo 1: Conexões por service name:
-group_by SERVICE_NAME

Exemplo 2: Conexões por dia:
-group_by TIMESTAMP
-group_formatIndica o formato da data usado para fazer agrupamento quando a opção usada em -group_by for TIMESTAMP.

Default: “DD-MON-YYYY” (conexões por por dia)

Exemplos:
-group_format “DD-MON-YYYY HH” (conexões por hora)
-group_format “DD-MON-YYYY HH:MI”(conexões por minuto)
-group_format “DD-MON-YYYY HH:MI:SS”(conexões por segundo)
-csvSalva o resultado da contagem em um arquivo no formato CSV ao invés de apresentar o resultado na tela em formato de tabela.
-csv_delimiterEssa opção permite mudar o delimitador usado para gerar o CSV quando a opção -csv é usada.

O padrão é “,” (vírgula).

Exemplo mudando o delimitador para ponto e vírgula:
-csv -csv_delimiter “;”
-save_filterPermite gerar um novo arquivo de log incluindo apenas os registros que atenderam aos filtros da execução atual, essa opção é útil quando você pretende realizar mais análises usando os mesmos filtros em comum.

Essa técnica reduz o consumo de CPU e o tempo de execução para realizar contagens quando o arquivo de log original é muito grande. A ideia é que o arquivo gerado nessa execução seja usado no parâmetro -log das próximas execuções.

Exemplo:
-save_filter cluster01_2024-09.log
-filter_onlyGera o mesmo arquivo que o -save_filter, mas o script irá apenas gerar o novo arquivo de log com os filtros aplicados, sem realizar a contagem de conexões.

Primeiros passos

1) Obtenha o script clicando aqui ou executando o comando abaixo em uma máquina com acesso a internet:

curl -O https://raw.githubusercontent.com/maiconcarneiro/blog-dibiei/main/lsnr_miner.sh

2) Conceda permissão de execução no script:

chmod +x lsnr_miner.sh

3) Com o script no servidor ou em sua máquina local, pode sempre consultar os modos de uso conforme abaixo:

./lsnr_miner.sh -h

      Usage: /home/oracle/lsnr_miner.sh -log <listener_log_path>
                   [-filter <ATTR=VALUE> | ATTR1=VALUE1,ATTR2=VALUE2,ATTRn=VALUEn]
                   [-filter_file <filer_file_name> -file_attr <IP|HOST|PROGRAM|USER|SERVICE_NAME>]
                   [-begin 'DD-MON-YYYY HH:MI:SS' -end 'DD-MON-YYYY HH:MI:SS']
                   [-group_by <IP|HOST|PROGRAM|USER|SERVICE_NAME>]
                   [-group_format <'DD-MON-YYYY' | 'DD-MON-YYYY HH' | 'DD-MON-YYYY HH:MI' | 'DD-MON-YYYY HH:MI:SS'>]
                   [-csv <result_file.csv>] [-csv_delimiter '<csv_char_delimiter>']
                   [-salve_filter <name_new_logfile_filtered> ]
                   [-filter_only <name_new_logfile_filtered> ]
     
      Where: 
       -log           -> Provide an valid LISTENER log file or Path with multiple log files (Required)
       
       -filter        -> Multiple filters with any supported attribute (IP|HOST|PROGRAM|USER|SERVICE_NAME)
                          Example: user=zabbix,ip=192.168.1.80,service_name=svcprod.domain.com

       -begin         -> The BEGIN and END timestamp to filter Listener log file using date interval.
       -end               Example: -begin '19-AUG-2023 11:00:00' -end '19-AUG-2023 12:00:00'

       -filter_file   -> Provide an helper file to apply filter in batch mode
       -filter_attr   -> Define the supported content type in -filter_file (IP|HOST|PROGRAM|USER|SERVICE_NAME)
                          Example: -filter_file appserver_ip_list.txt -filter_attr IP
        
       -save_filter   -> Create a new Log File with only lines filtered by this session.
       -filter_only   -> similar to -save_filter, but no count connections will be perfomed.
                         Optionally use this option to reuse the new log file many times with pre-applied commom filters for performance improvement.

       -group_by      -> Specify the ATTR used as Group By for the connections count (IP|HOST|PROGRAM|USER|SERVICE_NAME).
                         Default is TIMESTAMP

       -group_format  -> Specify the timestamp format used when -group_by is TIMESTAMP (default). 
                           The default format is DD-MON-YYYY HH:MI (connections per min).
       
       -csv           -> The result will be saved in CSV file instead of print table in the screen.
                         Optionally, provide a custom name for the CSV result file.
                         
       -csv_delimiter -> Allow specify an custom CSV delimiter (default is ',').

Execução

Você pode executar o script em uma máquina com sistema operacional Linux ou MacOS, em ambos os sistema operacionais o interpretador utilizado é o bash. Para cada execução, o script irá apresentar o resumo das opções selecionadas nos parâmetros, o que não for selecionado irá aparecer como vazio.

Exemplo:

./lsnr_miner.sh -log /tmp/all_logs \
 -begin "01-JUL-2024 00:00:00" \
 -end "31-AUG-2024 23:59:59" \
 -group_by "service_name" \
 -filter "user=monitor" \
 -filter_file /tmp/lista_ip.txt -filter_attr "IP" \
 -save_filter /tmp/listener_jul_aug_2024.log

DICA: Opcionalmente pode usar “\” para quebrar linha quando usar muitos parâmetros.

Resultado

============================================= Summary ==================================================
Log Path.............: /tmp/all_logs
Log Type.............: DIR
Filter...............: user=monitor
Filter file..........: /tmp/lista_ip.txt
Filter attr..........: IP
Save Filter..........: save_filter_205356.log
Log Timestamp Begin..: 01-JUL-2024 00:00:00
Log Timestamp End....: 31-AUG-2024 23:59:59
Group By Column......: SERVICE_NAME
Result Type..........: Table
==========================================================================================================


----------------------------------------------------------------------------------------------------------
12/09/2024 20:53:56 | INFO    | Processing the log file listener.log
12/09/2024 20:53:58 | INFO    | Processing the log file listener_2024-07.log
12/09/2024 20:53:59 | INFO    | Processing the log file listener_2024-08.log
12/09/2024 20:53:59 | INFO    | DB Server: appserver3
12/09/2024 20:53:59 | INFO    | Checking filters
12/09/2024 20:53:59 | INFO    | Reading Filter File: /tmp/lista_ip.txt
12/09/2024 20:53:59 | INFO    | Including lines where IP=10.1.4.29
12/09/2024 20:54:00 | INFO    | Including lines where IP=10.1.4.38
12/09/2024 20:54:01 | INFO    | Including lines where IP=10.1.4.78
12/09/2024 20:54:03 | INFO    | Applying filter: USER=monitor
12/09/2024 20:54:04 | INFO    | Applying timestamp filter from '01-JUL-2024 00:00:00' to '31-AUG-2024 23:59:59'
12/09/2024 20:54:06 | INFO    | Creating filtered file as save_filter_205356.log
12/09/2024 20:54:06 | INFO    | Preparing to count...
12/09/2024 20:54:06 | INFO    | Counting connections...
12/09/2024 20:54:06 | INFO    | Completed
----------------------------------------------------------------------------------------------------------


Connections count by SERVICE_NAME:
==============================
Item                Count
==============================
PDB1.dibiei.com      3638
PDB2.dibiei.com      12231
==============================

Exemplos de Casos de uso

Abaixo estão listados alguns exemplos de casos de uso e como fica a combinação de parâmetros para cada um deles, obviamente você pode explorar o script com outras combinações que não estão listadas aqui para atender uma necessidade mais específica.

1) Total de conexões por IP no dia 12/09/2024 que aparecem no arquivo de log “/tmp/listener.log“

./lsnr_miner.sh -log /tmp/listener.log -begin "12-SEP-2024 00:00:00" -end "12-SEP-2024 23:59:59"

Dica: Essa sintaxe tem um resultado equivalente ao do script lsnr_clients.sh, só de que forma offline.

2) Total de conexões por IP no dia 12/09/2024 que aparecem nos arquivos de log dentro do diretorio “/tmp/logs“

./lsnr_miner.sh -log /tmp/logs -begin "12-SEP-2024 00:00:00" -end "12-SEP-2024 23:59:59"

3) Total de conexões por IP no dia 12/09/2024 usando o serviço SVC_CONSULTA

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59"  \
 -filter "SERVICE_NAME=SVC_CONSULTA"

4) Total de conexões por hora no dia 12/09/2024 originadas do IP 10.1.0.25

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59" \
 -group_by "TIMESTAMP" \
 -group_format "DD-MON-YYYY HH" \
 -filter "ip=10.1.0.25"

5) Total de conexões por minuto no dia 12/09/2024 originadas do user weblogic

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59" \
 -group_by "timestamp" \
 -group_format "DD-MON-YYYY HH:MI" \
 -filter "user=weblogic"

6) Total de conexões por segundo no dia 12/09/2024

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59" \
 -group_by "timestamp" \
 -group_format "DD-MON-YYYY HH:MI:SS"

7) Total de conexões por minuto no dia 12/09/2024

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59" \
 -group_by "timestamp" \
 -group_format "DD-MON-YYYY HH:MI"

8) Total de conexões por hora no dia 12/09/2024

./lsnr_miner.sh -log /tmp/listener.log \
 -begin "12-SEP-2024 00:00:00" \
 -end "12-SEP-2024 23:59:59" \
 -group_by "timestamp" \
 -group_format "DD-MON-YYYY HH"

9) Total de conexões por dia desde 01/08/2024

./lsnr_miner.sh -log /tmp/listener.log -begin "31-AUG-2024 00:00:00" -group_by "timestamp"

10) Total de conexões por hostname onde o usuario do SO é weblogic e o program SQL Developer

./lsnr_miner.sh -log /tmp/listener.log \
 -group_by "host" \
 -filter "user=weblogic,program=JDBC Thin Client"

11) Total de conexões por SERVICE_NAME originadas dos 5 IPs abaixo:

10.1.1.10 / 10.1.1.11 / 10.1.1.12 / 10.1.1.13 / 10.1.1.14 / 10.1.1.15

cat /tmp/lista_ip_exemplo.txt
10.1.1.10
10.1.1.11
10.1.1.12
10.1.1.13
10.1.1.14
10.1.1.15
./lsnr_miner.sh -log /tmp/listener.log \
 -group_by "service_name" \
 -filter_file /tmp/lista_ip_exemplo.txt -filter_attr "IP"

Esse tipo de filtro tem o comportamento equivalente ao uso de IN no SQL.

12) Consolidando vários logs de 01/08/2024 à 31/08/2024 em um único arquivo

./lsnr_miner.sh -log /home/oracle/logs/cluster01 \
 -begin "01-AUG-2024 00:00:00" \
 -end "31-AUG-2024 23:59:59" \
 -save_filter /tmp/cluster_2024-08.log \
 -filter_only

13) Reusando o log consolidado gerado acima para gerar as contagens por IP, USER e SERVICE_NAME em CSV

 ./lsnr_miner.sh -log /tmp/cluster_2024-08.log -group_by "IP" -csv /tmp/conn_IP_2024-08.csv
 ./lsnr_miner.sh -log /tmp/cluster_2024-08.log -group_by "USER" -csv /tmp/conn_USER_2024-08.csv
 ./lsnr_miner.sh -log /tmp/cluster_2024-08.log -group_by "SERVICE_NAME" -csv /tmp/conn_SERVICE_NAME_2024-08.csv

Total de conexões por usuario que foram originadas da maquina “appserver1”, usando o programa “SQL Devloper” e o service_name “PDB1.dibiei.com”.

 ./lsnr_miner.sh -log /tmp/listener.log \
 -group_by "user" \
 -filter "service_name=PDB1.dibiei.com,program=SQL Developer,host=appserver1"

Os filtros combinados tem o efeito similar ao “AND” do SQL.

Comportamentos

Nomes de arquivos para -save_filter ou -csv

Os parâmetros -csv e -save_filter podem ser usados com ou sem um nome de arquivo que deseja salvar. Quando não for especificado um caminho, a opção -csv irá usar o default “result_XXXXXX.csv” e a opção -save_filter irá usar usar o default “save_filter_XXXXXX.log”

Quando cair em um caso de exceção conhecido, o script irá abortar a execução e apresentar uma mensagem indicando o que está faltando. Por exemplo, ao tentar utilizar o parâmetro -filter_file sem especificar o -filter_attr:

====================================================================================================
12/09/2024 18:29:49 | ERROR   | -filter_attr is required for -filter_file
====================================================================================================

Intervalo de datas

Quando o parâmetro -begin for especificado sem o parâmetro -end, o final do período será definido automaticamente para a data atual. Quando o parâmetro -end for especificado sem o parâmetro -begin, o inicio do período será definido automaticamente como 01-JAN-1970 no Linux ou 31-DEC-1969 no MacOS.

./lsnr_miner.sh -log all_logs/ -end "20-nov-2023 23:00:00"
....................................................................................................
13/09/2024 11:37:49 | WARNING | -end used without -begin. Using default: 31-DEC-1969 21:00:00
....................................................................................................

Formato do group by

Qualquer valor informado em –group_format será ignorado se o valor de -group_by for diferente de TIMESTAMP.

Filtro

Quando qualquer filtro for informado, na prática o script sempre aplica o filtro com um comportamento similar a um ” LIKE ‘VALOR%’ ” no SQL. Dessa forma, se você informar algo como “IP=10.1.4”, o script vai considerar todos os IPs que começam com esse padrão.

Isso pode ser útil quando você precisa filtrar uma subnet ao invés de um IP específico. Outro caso de uso útil é quando precisa filtrar pelo nome do programa. Por exemplo, ao informar “program=DBeaver”, o script irá filtrar todas as linhas onde o nome do programa começa com DBeaver.

Restrições

Nesta versão atual o script tem algumas restrições que precisam ser consideradas:

  • Quando utilizando uma combinação de filtros separados por virgula, não deve haver espaço após a vírgula.
  • Quando o valor sendo filtrado tiver espaços, colocar todo o filtro em aspas duplas. Exemplo: -filter “program=SQL Developer”.
  • Só é possível aplicar filtros com sinal de igualdade (ATRIBUTO=VALOR), e este sempre tem o comportamento similar de um LIKE (ATRIBUTO LIKE ‘VALOR%’).
  • Quando utilizando uma combinação de filtros, todos eles são considerados como uma condição “AND”, não tendo a opção de fazer algo equivalente a operação “OR” (note que dependendo do caso, a opção -filter_file pode atender fazendo o papel de uma condição “IN”).

Código

#!/bin/bash
# lsnr_miner.sh - v1.23
# Script to analyze Oracle Listener log file by applying advanced filters and provide connection count at different levels.
#
# https://raw.githubusercontent.com/maiconcarneiro/blog-dibiei/main/lsnr_miner.sh
# https://dibiei.blog/2024/09/13/script-lsnr_miner-sh-minerando-log-do-listener-do-oracle/
#
# Author: Maicon Carneiro (dibiei.blog)
#
# Date       | Author             | Change
# ----------- -------------------- ------------------------------------------------------------------------
# 20/02/2024 | Maicon Carneiro    | v1 based on the script "lsnr_clients.sh"
# 21/02/2024 | Maicon Carneiro    | Support to Timestamp filter
# 22/02/2024 | Maicon Carneiro    | Support to save_filter and CSV improvements
# 23/02/2024 | Maicon Carneiro    | Support for Filter using IP and dynamic column width in the result table 
# 29/02/2024 | Maicon Carneiro    | Support multiple log files passing an directory in -log parameter
# 12/09/2024 | Maicon Carneiro    | Support for values with "\" bar during counting 
# 12/09/2024 | Maicon Carneiro    | Support for MacOS (Darwin) with bash 3.0

VERSION="v1.23"
OS_TYPE=$(uname)
FILE_DATE=$(date +'%H%M%S')
CURRENT_DIR=$(pwd)
HELPER_FILE_PREFIX="${CURRENT_DIR}/lsminer.$FILE_DATE"

CONNECTIONS_FILE="${HELPER_FILE_PREFIX}.conn.txt"
FILE_LIST_ITEM="${HELPER_FILE_PREFIX}.list.txt"
COUNT_HELPER_FILE="${HELPER_FILE_PREFIX}.cont.txt"
COUNT_HELPER_FILE_STAGE="${HELPER_FILE_PREFIX}.cont_stage.txt"
SOURCE_HOSTNAME_FILE="${HELPER_FILE_PREFIX}.sourcehost.txt"
LISTENER_LOG_FILES="${HELPER_FILE_PREFIX}.listener_log_files.txt"

LOG_PATH=""
LOG_TYPE=""
LogFileName=""
filter_attr=""
filter_value=""
filter_file=""
group_by="IP"
group_format="DD-MON-YYYY"
BEGIN_TIMESTAMP=""
END_TIMESTAMP=""
SAVE_FILTER_FILE=""
resultFormat="Table"
CSV_DELIMITER=","
FILTER_ONLY=""

SUPPORTED_FILE_CHARACTERS='^[-a-zA-Z0-9_.\/]+$'
SUPPORTED_ATTR="IP|HOST|PROGRAM|USER|SERVICE_NAME"
SUPPORTED_TIMESTAMP_FORMAT="'DD-MON-YYYY' | 'DD-MON-YYYY HH' | 'DD-MON-YYYY HH:MI' | 'DD-MON-YYYY HH:MI:SS'"


# used by printMessage and the result with table format.
_printLine(){
 if [ ! -z "$1" ]; then
   MAX_LENGTH=$2
   if [ -z "$MAX_LENGTH" ]; then
     MAX_LENGTH=100
   fi
   for ((i=1; i<=$MAX_LENGTH; i++)); do
    LINE_HELPER+="$1"
   done;
   echo $LINE_HELPER
   unset LINE_HELPER
 fi
}

# Message log helper
printMessage(){
 MSG_TYPE=$(printf "%-7s" "$1")
 MSG_TEXT=$2
 MSG_DATE=$(date +'%d/%m/%Y %H:%M:%S')
 _printLine $3
 echo "$MSG_DATE | $MSG_TYPE | $MSG_TEXT"
 _printLine $3
}

show_help() {
    echo "
      Usage: $0 -log <listener_log_path>
                   [-filter <ATTR=VALUE> | ATTR1=VALUE1,ATTR2=VALUE2,ATTRn=VALUEn]
                   [-filter_file <filer_file_name> -file_attr <$SUPPORTED_ATTR>]
                   [-begin 'DD-MON-YYYY HH:MI:SS' -end 'DD-MON-YYYY HH:MI:SS']
                   [-group_by <$SUPPORTED_ATTR>]
                   [-group_format <$SUPPORTED_TIMESTAMP_FORMAT>]
                   [-csv <result_file.csv>] [-csv_delimiter '<csv_char_delimiter>']
                   [-salve_filter <name_new_logfile_filtered> ]
                   [-filter_only <name_new_logfile_filtered> ]
     
      Where: 
       -log           -> Provide an valid LISTENER log file or Path with multiple log files (Required)
       
       -filter        -> Multiple filters with any supported attribute ($SUPPORTED_ATTR)
                          Example: user=zabbix,ip=192.168.1.80,service_name=svcprod.domain.com
    
       -begin         -> The BEGIN and END timestamp to filter Listener log file using date interval.
       -end               Example: -begin '19-AUG-2023 11:00:00' -end '19-AUG-2023 12:00:00'

       -filter_file   -> Provide an helper file to apply filter in batch mode
       -filter_attr   -> Define the supported content type in -filter_file ($SUPPORTED_ATTR)
                          Example: -filter_file appserver_ip_list.txt -filter_attr IP
        
       -save_filter   -> Create a new Log File with only lines filtered by this session.
       -filter_only   -> similar to -save_filter, but no count connections will be perfomed.
                         Optionally use this option to reuse the new log file many times with pre-applied commom filters for performance improvement.

       -group_by      -> Specify the ATTR used as Group By for the connections count ($SUPPORTED_ATTR).
                         Default is TIMESTAMP

       -group_format  -> Specify the timestamp format used when -group_by is TIMESTAMP (default). 
                           The default format is DD-MON-YYYY HH:MI (connections per min).
       
       -csv           -> The result will be saved in CSV file instead of print table in the screen.
                         Optionally, provide a custom name for the CSV result file.
                         
       -csv_delimiter -> Allow specify an custom CSV delimiter (default is ',').
       
       Examples:
       
Script lsnr_miner.sh – Minerando Log do Listener do Oracle

      "
    exit 1
}

################################################## Params Begin ######################################################

if [[ ! "$OS_TYPE" =~ ^(Linux|Darwin)$ ]]; then
  printMessage "WARNING" "The OS $OS_TYPE is not supported and can cause unexpected behavior." "."
fi

if [ "$1" = "-v" ]; then
    echo "Version: $VERSION"
    exit 0
fi

if [ $# -lt 2 ]; then
    show_help
fi

while [[ $# -gt 0 ]]; do
    key="$1"
    case $key in
        -log)
            LOG_PATH="$2"
            shift
            shift
            ;;
        -filter)
            filter="$2"
            shift
            shift
            ;;
        -filter_attr)
            filter_attr=$(echo "$2" | tr '[:lower:]' '[:upper:]')
            shift
            shift
            ;;
        -filter_file)
            filter_file="$2"
            shift
            shift
            ;;
        -csv)
           resultFormat="csv"
           if [[ -n "$2" && "$2" != -* ]]; then
             CSV_OUTPUT_FILE="$2"
             shift
            fi
            shift 
            ;;
        -csv_delimiter)
            CSV_DELIMITER="$2"
            shift
            shift
            ;;
        -group_by)
            group_by=$(echo "$2" | tr '[:lower:]' '[:upper:]')
            shift
            shift
            ;;
        -group_format)
            group_format=$(echo "$2" | tr '[:lower:]' '[:upper:]')
            shift
            shift
            ;;
        -begin)
            BEGIN_TIMESTAMP=$(echo "$2" | tr '[:lower:]' '[:upper:]')
            shift
            shift
            ;;
        -end)
            END_TIMESTAMP=$(echo "$2" | tr '[:lower:]' '[:upper:]')
            shift
            shift
            ;;
        -save_filter)
            SAVE_FILTER_FILE="save_filter_$FILE_DATE.log"
            if [[ -n "$2" && "$2" != -* ]]; then
             SAVE_FILTER_FILE="$2"
             shift
            fi
            shift 
            ;;
        -filter_only)
            FILTER_ONLY="YES"
            SAVE_FILTER_FILE="save_filter_$FILE_DATE.log"
            if [[ -n "$2" && "$2" != -* ]]; then
             SAVE_FILTER_FILE="$2"
             shift
            fi
            shift 
            ;;
        *)
            printMessage "ERROR" "Invalid syntax or parameter: $key" "="
            exit 1
            ;;
    esac
done

# check -log
if [ -z "$LOG_PATH" ]; then
  printMessage "ERROR" "The -log parameter is required." "="
  show_help
fi

if [ -f "$LOG_PATH" ]; then
 LOG_TYPE="FILE"
elif [ -d "$LOG_PATH" ]; then
 LOG_TYPE="DIR"
else
 printMessage "ERROR" "The log file path not exists." "="
 show_help
fi


# check -filter_attr
if [ ! -z "$filter_attr" ] && [[ ! "$filter_attr" =~ ^(IP|HOST|SERVICE_NAME|PROGRAM|USER)$ ]]; then
    printMessage "ERROR" "The value for -filter_attr is invalid" "="
    show_help
fi

# check -filter_value
if [ ! -z "$filter_file" ] && [ -z "$filter_attr" ]; then
    printMessage "ERROR" "-filter_attr is required for -filter_file" "="
    exit 1
fi

# check -filter_file
if [ ! -z "$filter_file" ] && [ ! -f "$filter_file" ]; then
    printMessage "ERROR" "The file $filter_file provided in -filter_file don't exist." "=" 
    exit 1
fi

# check -group_by
if [ ! -z "$group_by" ] && [[ ! "$group_by" =~ ^(IP|HOST|TIMESTAMP|SERVICE_NAME|USER|PROGRAM)$ ]]; then
    printMessage "ERROR" "Invalid value to -group_by parameter." "="
    show_help
fi

# check -group_format
if [ ! -z "$group_format"  ]; then
 if [ "$group_by" == "TIMESTAMP" ] && [[ ! "$group_format" =~ ^(DD-MON-YYYY|DD-MON-YYYY HH|DD-MON-YYYY HH:MI|DD-MON-YYYY HH:MI:SS)$ ]]; then
    printMessage "ERROR" "The format provided for -group_format is invalid." "="
    show_help
 fi
fi

# check -save_filter
if [ ! -z "$SAVE_FILTER_FILE" ] && ! echo "$SAVE_FILTER_FILE" | grep -qE "$SUPPORTED_FILE_CHARACTERS"; then
    printMessage "ERROR" "-save_filter cannot have special characters." "="
    exit 1
elif [ -f "$SAVE_FILTER_FILE" ]; then
    printMessage "ERROR" "The file name provided in -save_filter already exists." "="
    exit 1
fi

# check -csv
if [ ! -z "$CSV_OUTPUT_FILE" ] && ! echo "$CSV_OUTPUT_FILE" | grep -qE "$SUPPORTED_FILE_CHARACTERS"; then
    printMessage "ERROR" "-csv file name cannot have special characters." "="
    exit 1
elif [ -f "$CSV_OUTPUT_FILE" ]; then
    printMessage "ERROR" "The file name provided in -csv already exists." "="
    exit 1
fi

# check -csv_delimiter
if [ -z "$CSV_DELIMITER"  ]; then
 CSV_DELIMITER=","
fi

if [ "$resultFormat" = "csv" ] && [ -z "$CSV_OUTPUT_FILE" ]; then
 CSV_OUTPUT_FILE="result_${FILE_DATE}.csv"
fi

# set default value for -end parameter
if [ ! -z "$BEGIN_TIMESTAMP" ] && [ -z "$END_TIMESTAMP" ]; then
 END_TIMESTAMP=$(date +"%d-%b-%Y %H:%M:%S" | tr '[:lower:]' '[:upper:]')
 printMessage "WARNING" "-begin used without -end. Using default: $END_TIMESTAMP" "."
fi

# set default value for -begin parameter
if [ ! -z "$END_TIMESTAMP" ] && [ -z "$BEGIN_TIMESTAMP" ]; then
 if [[ "$OS_TYPE" == "Linux" ]]; then
  BEGIN_TIMESTAMP=$(date -d "1970-01-01" +"%d-%b-%Y %H:%M:%S" | tr '[:lower:]' '[:upper:]')
 else
  BEGIN_TIMESTAMP=$(date -r 0 +"%d-%b-%Y %H:%M:%S" | tr '[:lower:]' '[:upper:]')
 fi 
 printMessage "WARNING" "-end used without -begin. Using default: $BEGIN_TIMESTAMP" "."
fi

# define interval from '1970-01-01' to current date if timestamp filter was not used.
if [[ "$OS_TYPE" == "Darwin" ]]; then
 # Mac OS
  if [ -z "$BEGIN_TIMESTAMP" ]; then
    _start_timestamp=$(date -j -f "%d-%b-%Y %H:%M:%S" "1970-01-01 00:00:00" "+%Y%m%d%H%M%S")
    _end_timestamp=$(date +'%Y%m%d%H%M%S')
  else
    _start_timestamp=$(date -j -f "%d-%b-%Y %H:%M:%S" "$BEGIN_TIMESTAMP" "+%Y%m%d%H%M%S")
    _end_timestamp=$(date -j -f "%d-%b-%Y %H:%M:%S" "$END_TIMESTAMP" "+%Y%m%d%H%M%S")
  fi

else
 # Linux and others
   if [ -z "$BEGIN_TIMESTAMP" ]; then
    _start_timestamp=$(date -d "1970-01-01" +'%Y%m%d%H%M%S')
    _end_timestamp=$(date +'%Y%m%d%H%M%S')
  else
    _start_timestamp=$(date -d "$BEGIN_TIMESTAMP" "+%Y%m%d%H%M%S")
    _end_timestamp=$(date -d "$END_TIMESTAMP" "+%Y%m%d%H%M%S")
  fi

fi


################################################ Params End #################################################

clearTempFiles()
{
   rm -f $CONNECTIONS_FILE
   rm -f $FILE_LIST_ITEM
   rm -f $COUNT_HELPER_FILE
   rm -f $COUNT_HELPER_FILE_STAGE
   rm -f $FILE_LIST_ITEM.uniq
   rm -f $SOURCE_HOSTNAME_FILE
   rm -f $LISTENER_LOG_FILES
}

exitHelper(){
  clearTempFiles
  printMessage "$1" "$2"
  echo ""
  echo ""
  exit 1
}

getEscaped(){
 _textValue=$1
 _escapedTextValue=$(echo "$_textValue" | sed 's/\\/\\\\/g')
 echo "$_escapedTextValue"
}

AWK_1=1
FILTER_PREFIX=""
GROUPBY_INFO_SUMMARY="Log Timestamp"

case "$group_by" in
    "TIMESTAMP")
        GROUPBY_INFO_SUMMARY="Log Timestamp"
        ;;
    "HOST")
        GROUPBY_INFO_SUMMARY="Hostname"
        AWK_1=2
        FILTER_PREFIX="HOST="
        ;;
    "SERVICE_NAME")
        GROUPBY_INFO_SUMMARY="SERVICE_NAME"
        AWK_1=2
        FILTER_PREFIX="SERVICE_NAME="
        ;;
    "PROGRAM")
        GROUPBY_INFO_SUMMARY="Program Name"
        AWK_1=2
        FILTER_PREFIX="PROGRAM="
        ;;
    "USER")
        GROUPBY_INFO_SUMMARY="OS USER"
        AWK_1=2
        FILTER_PREFIX="USER="
        ;;
    "IP")
        GROUPBY_INFO_SUMMARY="IP Address"
        AWK_1=3
        FILTER_PREFIX="HOST="
        ;;
    *)
        ;;
esac


# used to apply timestamp filter after consolidate the logfile
applyIntervalFilter(){

input_file=$3
output_file=$input_file.timestamp.filter

awk -v start="$_start_timestamp" -v end="$_end_timestamp" -F "*"  '{
    # get the first filed in the log file with DD-MON-YYYY HH:MI:SS format
    date_str=$1

    # map month name 'MON' to month number 'MM'
    months["JAN"] = "01"; months["FEB"] = "02"; months["MAR"] = "03"; months["APR"] = "04";
    months["MAY"] = "05"; months["JUN"] = "06"; months["JUL"] = "07"; months["AUG"] = "08";
    months["SEP"] = "09"; months["OCT"] = "10"; months["NOV"] = "11"; months["DEC"] = "12";

    split(date_str, date_parts, /[-: ]/);
    year = date_parts[3];
    month = months[date_parts[2]];
    day = date_parts[1];
    hour = date_parts[4];
    minute = date_parts[5];
    second = date_parts[6];
    log_timestamp=year""month""day""hour""minute""second

    if (log_timestamp >= start && log_timestamp <= end) {
       print
    }
  }' "$input_file" > $output_file

cat $output_file > $input_file
rm -f $output_file

LINES_COUNT=$(cat $input_file | wc -l)
echo "$LINES_COUNT"
}



listDirLogFiles(){

  touch $LISTENER_LOG_FILES
  for FILE in $(grep -s -l -m 1 "CONNECT_DATA" *.log); do
   BEGIN_LOG=$(grep -m 1 "CONNECT_DATA" $FILE | awk -F "*" '{print $1}' | cut -c 1-20 )
   END_LOG=$(tail -1 $FILE | awk -F "*" '{print $1}' | cut -c 1-20 )
   echo "$FILE*$BEGIN_LOG*$END_LOG*" >> $LISTENER_LOG_FILES
  done
  
  awk -v start="$_start_timestamp" -v end="$_end_timestamp" -F "*"  '{
      # get the first filed in the log file with DD-MON-YYYY HH:MI:SS format
      date_str1=$2
      date_str2=$3

      # map month name 'MON' to month number 'MM'
      months["JAN"] = "01"; months["FEB"] = "02"; months["MAR"] = "03"; months["APR"] = "04";
      months["MAY"] = "05"; months["JUN"] = "06"; months["JUL"] = "07"; months["AUG"] = "08";
      months["SEP"] = "09"; months["OCT"] = "10"; months["NOV"] = "11"; months["DEC"] = "12";
      
      # begin log timestamp
      split(date_str1, date_parts1, /[-: ]/);
      year1 = date_parts1[3];
      month1 = months[date_parts1[2]];
      day1 = date_parts1[1];
      hour1 = date_parts1[4];
      minute1 = date_parts1[5];
      second1 = date_parts1[6];
      log_begin=year1""month1""day1""hour1""minute1""second1
      
      # end log timestamp
      split(date_str2, date_parts2, /[-: ]/);
      year2 = date_parts2[3];
      month2 = months[date_parts2[2]];
      day2 = date_parts2[1];
      hour2 = date_parts2[4];
      minute2 = date_parts2[5];
      second2 = date_parts2[6];
      log_end=year2""month2""day2""hour2""minute2""second2

      if ( (start >= log_begin && start <= log_end) || 
           (end >= log_begin && end <= log_end)     || 
           (log_begin >= start && log_begin <= end) || 
           (log_end >= start && log_end <= end) ) {
       print log_begin";"$1
      }
    }' "$LISTENER_LOG_FILES" | sort -t';' -n -k1 > $LISTENER_LOG_FILES.tmp
  
  rm -f $LISTENER_LOG_FILES
  touch $LISTENER_LOG_FILES
  for FILE in $(cat $LISTENER_LOG_FILES.tmp | awk -F ";" '{print $2}'); do
   CHECK_LOG=$(grep -c $FILE $LISTENER_LOG_FILES)
   if [ "$CHECK_LOG" -eq 0 ]; then
    echo "$FILE" >> $LISTENER_LOG_FILES
   fi
  done

  rm -f $LISTENER_LOG_FILES.tmp
  COUNT=$(cat $LISTENER_LOG_FILES | wc -l)
  echo "$COUNT"
}


# get the source hostnames
listSourceHosts(){
FILE_NAME="$1"
grep "status" $FILE_NAME | awk -F "HOST=" '{print $2}' | awk -F ")" '{print $1}' | awk -F "." '{print $1}' | sort -u > $SOURCE_HOSTNAME_FILE
}

printSourceHosts(){
LIST_HOST_NAMES=""
for NAME in $(cat $SOURCE_HOSTNAME_FILE | sort -u); do
 if [ -z "$LIST_HOST_NAMES" ]; then
  LIST_HOST_NAMES="$NAME"
 else
  LIST_HOST_NAMES="$LIST_HOST_NAMES,$NAME"
 fi
done
printMessage "INFO" "DB Server: $LIST_HOST_NAMES"
}

prepareLogFile(){
 LogFileName="$1"
  printMessage "INFO" "Processing the log file $LogFileName"
  listSourceHosts $LogFileName
  grep CONNECT_DATA $LogFileName | grep "establish" >> $CONNECTIONS_FILE
}


#######################################################################
######################### BEGIN EXECUTION #############################
#######################################################################

echo ""
echo "============================================= Summary =================================================="
echo "Log Path.............: $LOG_PATH"
echo "Log Type.............: $LOG_TYPE"
echo "Filter...............: $filter"
echo "Filter file..........: $filter_file"
echo "Filter attr..........: $filter_attr"
echo "Save Filter..........: $SAVE_FILTER_FILE"
echo "Log Timestamp Begin..: $BEGIN_TIMESTAMP"
echo "Log Timestamp End....: $END_TIMESTAMP"
echo "Group By Column......: $group_by"
echo "Result Type..........: $resultFormat"
echo "=========================================================================================================="
echo ""

echo ""
echo "----------------------------------------------------------------------------------------------------------"

# create the connections file with valid lines
if [ "$LOG_TYPE" == "FILE" ]; then
 prepareLogFile $LOG_PATH
else 
 
 cd $LOG_PATH
 LINES_COUNT=$(listDirLogFiles)
 if [ "$LINES_COUNT" -gt 0 ]; then
  for FILE in $(cat $LISTENER_LOG_FILES); do
   prepareLogFile $FILE
  done
  printSourceHosts
 else
  exitHelper "WARNING" "Log files not found." 
 fi

 cd $CURRENT_DIR
fi

printMessage "INFO" "Checking filters"

## filter file loop
if [ ! -z "$filter_attr" ] && [ ! -z "$filter_file" ]; then
 
 printMessage "INFO" "Reading Filter File: $filter_file"
 
 # print filter as 'IP='' but apply grep as 'HOST=''
 _local_filter_attr=$filter_attr
 if [ "$filter_attr" == "IP" ]; then
  _local_filter_attr="HOST"
 fi

 while IFS= read -r filter_line; do
    printMessage "INFO" "Including lines where $filter_attr=$filter_line"

    escapedFilter=$(getEscaped "$_local_filter_attr=$filter_line")
    grep "$escapedFilter" "$CONNECTIONS_FILE" >> $CONNECTIONS_FILE.filter
 done < "$filter_file"
 
 cat $CONNECTIONS_FILE.filter > $CONNECTIONS_FILE
 rm -f $CONNECTIONS_FILE.filter

 lines=$(cat $CONNECTIONS_FILE | wc -l)
 if [ "$lines" -eq 0 ]; then
  exitHelper "ERROR" "No lines after apply the filter."
 fi

fi

## simple filter loop
if [ ! -z "$filter" ]; then
 IFS=','
 for filter_helper in $filter; do
  filter_helperUpper=$(echo "$filter_helper" | tr '[:lower:]' '[:upper:]')
  attr=$(echo $filter_helperUpper | awk -F "=" '{print $1}')
  value=$(echo $filter_helper | awk -F "=" '{print $2}')
  
  printMessage "INFO" "Applying filter: $attr=$value"
  
  if [ "$attr" == "IP" ]; then
   attr="HOST"
  fi
  
  escapedFilter=$(getEscaped "$attr=$value")
  grep "$escapedFilter" $CONNECTIONS_FILE > $CONNECTIONS_FILE.filter
  cat $CONNECTIONS_FILE.filter > $CONNECTIONS_FILE
  rm -f $CONNECTIONS_FILE.filter
 done

 lines=$(cat $CONNECTIONS_FILE | wc -l)
 if [ "$lines" -eq 0 ]; then
  exitHelper "ERROR" "No lines after apply the filter."
 fi

fi

# apply timestamp filter, this filter must be the latest because it is CPU bound
if [ ! -z "$BEGIN_TIMESTAMP" ] && [ ! -z "$END_TIMESTAMP" ]; then

 printMessage "INFO" "Applying timestamp filter from '$BEGIN_TIMESTAMP' to '$END_TIMESTAMP'"
 
 lines=$(applyIntervalFilter "$BEGIN_TIMESTAMP" "$END_TIMESTAMP" $CONNECTIONS_FILE)
 
 if [ "$lines" -eq 0 ]; then
  exitHelper "ERROR" "No lines in the provided interval."
 fi

fi

# preserve an copy of the filtered file
if [ ! -z "$SAVE_FILTER_FILE" ]; then
 printMessage "INFO" "Creating filtered file as $SAVE_FILTER_FILE"
 cp $CONNECTIONS_FILE $SAVE_FILTER_FILE
fi

if [ "$FILTER_ONLY" == "YES" ]; then
  exitHelper "INFO" "Completed"
fi


printMessage "INFO" "Preparing to count..."

GROUP_BY_WIDTH=20
if [ "$group_by" == "TIMESTAMP" ]; then
 GROUP_BY_WIDTH=${#group_format}
fi

if [ "$group_by" == "TIMESTAMP" ]; then
 # list of distinct log timestamp
 cut -c 1-$GROUP_BY_WIDTH $CONNECTIONS_FILE | sort > $FILE_LIST_ITEM
else
 # list of distinct values for GROUP BY and count
 awk -F "*" -v var1="$AWK_1" '{ print $var1 }' $CONNECTIONS_FILE | awk -F "$FILTER_PREFIX" '{print $2}' | awk -F ")" '{print $1}' | sort > $FILE_LIST_ITEM.helper
 sed "s/$FILTER_PREFIX//g"  $FILE_LIST_ITEM.helper | sed '/^$/d' > $FILE_LIST_ITEM
 rm -f $FILE_LIST_ITEM.helper
fi

 printMessage "INFO" "Counting connections..."
 cat $FILE_LIST_ITEM | uniq > $FILE_LIST_ITEM.uniq
 while IFS= read -r line; do

  textFilter=$line
  textFilterEscaped=$(getEscaped "$textFilter")
  CONN_COUNT=$(grep -wc "$textFilterEscaped" $FILE_LIST_ITEM)

  if [ ${#line} -gt "$GROUP_BY_WIDTH" ]; then
   GROUP_BY_WIDTH=$(( ${#line} + 10 ))
  fi

  # result can be csv or table format
  if [ "$resultFormat" == "csv" ]; then
   echo "$line""$CSV_DELIMITER""$CONN_COUNT" >> $COUNT_HELPER_FILE_STAGE
  else
   echo "$line | $CONN_COUNT" >> $COUNT_HELPER_FILE_STAGE
  fi

 done < "$FILE_LIST_ITEM.uniq"


if [ "$group_by" == "TIMESTAMP" ]; then
 # output ordered by timestamp
 sort -k1.8,1.11n -k1.4,1.6M -k1.1,1.2n $COUNT_HELPER_FILE_STAGE >> $COUNT_HELPER_FILE
else
 # output oreded by connections count
 sort -t '|' -r -k 2n $COUNT_HELPER_FILE_STAGE >> $COUNT_HELPER_FILE
fi


printMessage "INFO" "Completed"
echo "----------------------------------------------------------------------------------------------------------"
echo ""


 if [ "$resultFormat" == "csv" ]; then
  echo "group_by${CSV_DELIMITER}conn_count" > $CSV_OUTPUT_FILE
  cat $COUNT_HELPER_FILE >> $CSV_OUTPUT_FILE
  echo "[INFO] CSV result file: $CSV_OUTPUT_FILE"
 else
  LINE_SIZE=$((GROUP_BY_WIDTH + 10))
  echo ""
  echo "Connections count by $GROUPBY_INFO_SUMMARY:"
  _printLine "=" $LINE_SIZE
  echo "Item Count" | awk -v width="$GROUP_BY_WIDTH" '{ printf "%-" width "s%s\n", $1, $2 }'
  _printLine "=" $LINE_SIZE
   awk -F "|" -v width="$GROUP_BY_WIDTH" '{ printf "%-" width "s%s\n", $1, $2 }' $COUNT_HELPER_FILE
  _printLine "=" $LINE_SIZE
  echo ""
 fi

echo ""
clearTempFiles

Caso você tenha algum problema com a execução do script e precisar de alguma ajuda ou dúvida, pode entrar em contato comigo no Linkedin ou pelo Grupo de Usuários Oracle do Brasil (GUOB) no WhatsApp.

By Maicon Carneiro

Related Post

AutoUpgrade Listener Scripts

AutoUpgrade Patching: Como Automatizar o Restart do Listener no Novo Oracle Home (Standalone Server)

Dec 9, 2024 Maicon Carneiro
Listener Scripts

Script oop_listener_helper.sh – Atualizando o LISTENER Durante o Out-Of-Place Patching

Dec 5, 2024 Maicon Carneiro
Performance Scripts

Script para Simular Alta Concorrência de INSERT em Oracle RAC

Nov 26, 2024 Maicon Carneiro

Leave a Reply

Outros posts

Performance

How to Identify the Plan Hash Value With the Shortest Average Execution Time for Each SQL_ID in the Cursor Cache and Create SPM Baselines

18 de March de 2026 Maicon Carneiro
Erros

gridSetup.sh -switchGridHome fails with PRVG-2031 : Owner of file “<GRID_HOME>/bin/oradism” did not match the expected value on node

15 de March de 2026 Maicon Carneiro
Exadata

How to Change the ADMIN Password in the Exadata RoCE Switches

12 de March de 2026 Maicon Carneiro
Multitenant Oracle Data Guard

How to Clone a Non-CDB as a PDB in a CDB with Data Guard Configuration

10 de March de 2026 Maicon Carneiro

Discover more from Blog do Dibiei

Subscribe now to keep reading and get access to the full archive.

Continue reading