listener.log分时段统计连接数

admin · 发表于 2021-8-7 15:26:33

有的时候需要从listener.log分析连接数情况：
截取特定时间段的日志

sed -n '/开始时间/,/结束时间/p' listener.log

复制代码

同一天按小时统计连接数

fgrep "13-JAN-2015 " listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 }' |sort |uniq -c|sort -k 3

复制代码

同一小时按分钟统计连接数

fgrep "13-JAN-2015 11:" listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c|sort -n

复制代码

同一小时按秒统计连接数

fgrep "29-NOV-2023 16:42" listener.log |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 ":" $3 }' |sort |uniq -c|sort -k 3

复制代码

同一小时按分钟统计连接失败数

fgrep "11-JAN-2015 11:" listener.log |awk '{ if ( $NF != 0 ) print $0 }'|awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c|sort -n

复制代码

同一小时按ip统计连接数

fgrep "11-JAN-2015 11:" listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort -n

复制代码

每分钟ip请求数

fgrep "11-JAN-2015 11:30" listener.log|fgrep "establish"|awk -F* '{print $3}'|awk -F= '{ print $4}'|sed -e 's/......$//g'|sort |uniq -c|sort -n

复制代码

全天每小时IP请求数

fgrep "09-JAN-2015 " istener.log|fgrep "establish"|awk -F* '{print $1 " " $3}'|awk -F= '{ print $1 " " $4}'|sed -e 's/......$//g'| awk '{print $1 " " $2 " " $4}'|cut -b-14,21- |sort |uniq -c

复制代码

指定时间指定主机上不同主机USER的请求统计

fgrep "04-JUL-2018 15:" listener.log|fgrep " <span style="color: rgb(0, 0, 0); font-size: 14px;">133.96.65.45" | awk -F= '$11 ~ /USER/ {print $12 }' | sed 's/).*$//'|sort|uniq -c|sort -n

复制代码

指定时间指定条件的PROGRAM的请求统计

fgrep "01-NOV-2018 " listener.log|sed 's/^.*PROGRAM//;s/).*$//;s/^.*:...\*//;s/).*$//;s/\*.*$//'|sort|uniq -c|sort -n

复制代码

vi cnt.awk

BEGIN{FS="*";p="NULL";c="NULL";n=0;}
{
if(match($0,"CONNECT_DATA") != 0){
n++;
p=c;
c=$1;
if(p!=c){
print p,"-",n;
n=0;
}
}
}

复制代码

awk cnt.awk listener.log

15-FEB-2022 23:53:17 - 1
15-FEB-2022 23:54:17 - 1
15-FEB-2022 23:54:47 - 1
15-FEB-2022 23:55:17 - 1
15-FEB-2022 23:56:17 - 1
15-FEB-2022 23:57:17 - 1
15-FEB-2022 23:58:17 - 1
15-FEB-2022 23:59:17 - 1
16-FEB-2022 00:00:00 - 2
16-FEB-2022 00:00:17 - 1
16-FEB-2022 00:01:17 - 1
16-FEB-2022 00:02:17 - 1
16-FEB-2022 00:03:17 - 1
16-FEB-2022 00:04:17 - 1
16-FEB-2022 00:04:49 - 1
16-FEB-2022 00:05:17 - 1
16-FEB-2022 00:06:17 - 1
16-FEB-2022 00:07:17 - 1
16-FEB-2022 00:08:17 - 1
16-FEB-2022 00:08:51 - 1

复制代码

admin · 发表于 2023-12-12 09:01:44

你可以使用以下的shell命令来统计listener.log中不同主机的连接次数：
grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" listener.log | sort | uniq -c
这行命令会首先使用grep命令匹配listener.log中的IP地址，并使用sort和uniq命令来对IP地址进行排序和去重，并且使用uniq -c来统计每个IP地址出现的次数

这个命令将会：

使用grep命令和正则表达式\b([0-9]{1,3}\.){3}[0-9]{1,3}\b在listener.log文件中匹配并提取IP地址。
\b用于匹配单词边界，确保我们只获取完整的IP地址。
([0-9]{1,3}\.){3}用于匹配3个由数字和点号组成的分组，表示IP地址的前三部分。
[0-9]{1,3}用于匹配最后一个IP地址部分，即最后一个数字。
使用sort命令对提取的IP地址进行排序。
使用uniq -c命令统计每个唯一IP地址出现的次数，并在每行前面显示计数。
请确保将listener.log替换为实际的监听器日志文件路径。运行命令后，你将会得到一个包含IP地址和计数的列表。

如果你想要从 listener.log 文件中提取 program，并统计它们的数量，可以使用以下命令：

grep -oE "PROGRAM=[^)]+" listener.log | sort | uniq -c
该命令会执行以下操作：

使用 grep 命令和正则表达式 PROGRAM=[^)]+ 从 listener.log 文件中匹配并提取 program。
PROGRAM= 匹配 program 字符串。
[^)]+ 匹配不包含 ) 的任意字符，确保我们只提取一个完整的 program。
使用 sort 命令对提取的 program 进行排序。
使用 uniq -c 命令统计每个唯一的 program 出现的次数，并在每行前面显示计数。
请确保将 listener.log 替换为实际的监听器日志文件路径。执行该命令后，你将获得一个包含 program 和计数的列表。

如果你想要从 listener.log 文件中提取 user，并统计它们的数量，可以使用以下命令：

grep -oE "USER=[^)]+" listener.log | sort | uniq -c
该命令会执行以下操作：

使用 grep 命令和正则表达式 USER=[^)]+ 从 listener.log 文件中匹配并提取 user。
USER= 匹配 user 字符串。
[^)]+ 匹配不包含 ) 的任意字符，确保我们只提取一个完整的 user。
使用 sort 命令对提取的 user 进行排序。
使用 uniq -c 命令统计每个唯一的 user 出现的次数，并在每行前面显示计数。
请确保将 listener.log 替换为实际的监听器日志文件路径。执行该命令后，你将获得一个包含 user 和计数的列表

admin · 发表于 2023-12-12 09:03:53

要统计特定时间段内不同主机的连接次数，你可以使用以下的shell命令：

grep '开始时间' listener.log -A $(($(grep -n '结束时间' listener.log | cut -d: -f1) - $(grep -n '开始时间' listener.log | cut -d: -f1))) | grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" | sort | uniq -c
请将"开始时间"和"结束时间"替换为你想要统计的时间段。这个命令会首先使用第一个grep命令找到开始时间行所在的位置，并通过-A参数来匹配从开始时间到结束时间之间的行。然后使用第二个grep命令匹配IP地址，并使用sort和uniq命令来统计每个IP地址出现的次数。

请确保时间格式与listener.log中的日期和时间格式匹配，否则可能无法正确匹配到时间段内的内容。

admin · 发表于 2023-12-20 17:40:13

#!/bin/sh
#usage:view listener status/config;sqlnet.ora;du size and analyze connect count by hour/minute/second to diag connection storm.
#yu.c.zhang@oracle.com 20231219
#modification:add by ip/program/user. 20240113
#modification:add by program+host+user+ip;no_timed item sort by count desc. 20240115
#define select prompt
PS3="please select a num from menu："
#define logo
function csslogo {
echo -e "\033[31m*************************************************************************************************************************\033[0m"
echo
echo -e " \033[31m ${bold}ORACLE CSS DATABASE TOOLBOX\033[0m"
echo -e
echo -e "\033[31m b\033[0m: back to above menu"
echo -e "\033[31m q\033[0m: exit the program"
echo -e "\033[31m*************************************************************************************************************************\033[0m"
}
#define function to get lsnr
function get_lsnr () {
LSNR_COUNT=$(ps -ef|grep "/bin/tnslsnr"|grep -v grep|wc -l)
if [ $LSNR_COUNT -eq 0 ]
then
echo No LISTENER Running !
exit
fi
if [ $LSNR_COUNT -gt 0 ]
then
clear
csslogo
echo
echo "Select the listener you want to operate: [Enter the Number]"
echo "----------------------------------------------------------"
select listeners in $( ps -ef | grep "/bin/tnslsnr"| grep -v grep | awk -v OFS=':' '{print $1,$8,$9}' )
do
integ='^[1-9]+$'
if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${LSNR_COUNT} ];then
echo "Error: Not a valid number!"
else
lsnr_owner=`echo ${listeners}|awk -v FS=':' '{print $1}'`
lsnr_name=`echo $listeners|awk -v FS=':' '{print $3}'|tr "[:upper:]" "[:lower:]"`
tnslsnr=`echo $listeners|awk -v FS=':' '{print $2}'`
lsnr_path=`dirname $tnslsnr`
lsnr_home=`dirname $lsnr_path`
lsnr_logdir=`$lsnr_home/bin/lsnrctl <<EOF | grep log_directory | awk '{print $6}'
set current_listener $lsnr_name
show log_directory
EOF`
lsnr_trcfile=`$lsnr_home/bin/lsnrctl<<EOF | grep trc_directory | awk '{print $6"/"$1".log"}'
set current_listener $lsnr_name
show trc_directory
EOF`
if [ $USER != $lsnr_owner ];then
echo "Please execute as LISTENER ownr:${lsnr_owner} !"
exit
fi
break
fi
done
fi
}
#define function to view config and status
menul_view () {
while true; do
clear
csslogo
echo -e "====ORACLE-TOOLBOX-listener-analyze==="
echo -e "\t1.vies status"
echo -e "\t2.vies service"
echo -e "\t3.view listener.ora"
echo -e "\t4.view sqlnet.ora"
echo -e "\t5.view file size"
echo "================"
read -p $'\e\033[33;5m$please enter option：\e\033[0m' choice
case $choice in
1)
echo
echo -e "\t\033[31mlisnrctl status\033[0m"
echo "-------------------------------------------"
$lsnr_path/lsnrctl status $v_lsnr_name
read -p "execute succesfully,enter any to continue..."
;;
2)
echo
echo -e "\t\033[31mlisnrctl service\033[0m"
echo "-------------------------------------------"
$lsnr_path/lsnrctl service $v_lsnr_name
read -p "execute succesfully,enter any to continue..."
;;
3)
echo
echo -e "\t\033[31mlistener.ora\033[0m"
echo "-------------------------------------------"
cat `$lsnr_path/lsnrctl status $lsnr_name|grep "Parameter File"|awk '{print $4}'`
read -p "execute succesfully,enter any to continue..."
;;
4)
echo
echo -e "\t\033[31msqlnet.ora\033[0m"
echo "-------------------------------------------"
cat $lsnr_home/network/admin/sqlnet.ora
read -p "execute succesfully,enter any to continue..."
;;
5)
echo
echo -e "\t\033[31mLOG DIR USED in MB\033[0m"
echo "-------------------------------------------"
du -sm $lsnr_logdir
echo
echo -e "\t\033[31mTRACE FILE USED in MB\033[0m"
echo "-------------------------------------------"
du -sm $lsnr_trcfile
read -p "execute succesfully,enter any to continue..."
;;
b)
menul;;
q)
exit;;
*)
echo "invalid option！"
sleep 1;;
esac
done
}
#define function to analyze connect count
menul_analyze () {
while true; do
clear
csslogo
echo -e "====ORACLE-TOOLBOX-listener-analyze==="
echo -e "\t1.analyze by hour"
echo -e "\t2.analyze by minite"
echo -e "\t3.analyze by second"
echo -e "\t4.analyze by ip "
echo -e "\t5.analyze by program"
echo -e "\t6.analyze by user"
echo -e "\t7.analyze by program+host+user+ip"
echo -e "\t8.analyze by specified ip and second"
echo "================"
read -p $'\e\033[33;5m$please enter option：：\e\033[0m' choice
case $choice in
1)
echo -e "please type time like [$(date '+%d-%b-%Y'| tr '[:lower:]' '[:upper:]')]"
read -p 'enter your time:' v_date
echo
echo " count by hour"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile | fgrep "establish" | awk '{print $1 " " $2}' | awk -F: '{print $1 }' | sort | uniq -c | sort -k 3
read -p "execute succesfully,enter any to continue..."
;;
2)
echo -e "please type time like [$(date '+%d-%b-%Y %H'| tr '[:lower:]' '[:upper:]')]"
read -p 'enter your time:' v_date
echo
echo " count by minute"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 }' |sort |uniq -c|sort -k 3
read -p "execute succesfully,enter any to continue..."
;;
3)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')]"
read -p 'enter your time:' v_date
echo
echo " count by second"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile |fgrep "establish" |awk '{print $1 " " $2}' |awk -F: '{print $1 ":" $2 ":" $3 }' |sort |uniq -c|sort -k 3
read -p "execute succesfully,enter any to continue..."
;;
4)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')] and cut until day/hour/minute"
read -p 'enter your time:' v_date
echo
echo " count ip_address"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile|fgrep "establish"|grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" | sort | uniq -c|sort -k1,1nr
echo
read -p "execute succesfully,enter any to continue..."
;;
5)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')] and cut until day/hour/minute"
read -p 'enter your time:' v_date
echo
echo " count program"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile|fgrep "establish"|grep -oE "PROGRAM=[^)]+" | sort | uniq -c|sort -k1,1nr
echo
read -p "execute succesfully,enter any to continue..."
;;
6)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')] and cut until day/hour/minute"
read -p 'enter your time:' v_date
echo
echo " count user"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile|fgrep "establish"|grep -oE "USER=[^)]+" | sort | uniq -c|sort -k1,1nr
echo
read -p "execute succesfully,enter any to continue..."
;;
7)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')] and cut until day/hour/minute"
read -p 'enter your time:' v_date
echo
echo " count program+host+user+ip"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile|fgrep "establish"|grep -oP "PROGRAM=(\S+)\)$HOST=(\S+)$\(USER=(\S+).*?HOST=([\d\.]+)"|sort|uniq -c|sort -k1,1nr
echo
read -p "execute succesfully,enter any to continue..."
;;
8)
echo -e "please type time like [$(date '+%d-%b-%Y %H:%M'| tr '[:lower:]' '[:upper:]')] and cut until day/hour/minute"
read -p 'enter your time:' v_date
read -p 'enter your ip_address:' v_address
echo
echo " count time"
echo "--------- ----------------------------------------------------------"
fgrep "$v_date" $lsnr_trcfile|fgrep "establish"|fgrep $v_address|awk -F* '{print $1 " " $3}'|awk -F= '{ print $1 " " $4}'|sed -e 's/......$//g'| awk '{print $1 " " $2 " " $4}'|sort |uniq -c
read -p "execute succesfully,enter any to continue..."
;;
b)
menul;;
q)
exit;;
*)
echo "invalid option！"
sleep 1;;
esac
done
}
#define main program
function menul() {
while true; do
clear
csslogo
echo -e "====ORACLE-TOOLBOX-listener==="
echo -e "\t1.status and config file"
echo -e "\t2.analyze conn rate"
echo -e "================"
read -p $'\e\033[33;5m$please enter option：：\e\033[0m' choice
case $choice in
1)
get_lsnr
menul_view
read -p "execute succesfully,enter any to continue..."
;;
2)
get_lsnr
menul_analyze
read -p "execute succesfully,enter any to continue..."
;;
b)
menua
;;
q)
exit
;;
*)
echo "invalid option！"
sleep 1
;;
esac
done
}
#execute main program
menul

复制代码

		自动登录	找回密码
密码			立即注册

[日常管理] listener.log分时段统计连接数