前回覚えたSQL Serverの条件分岐を使って、nagios/icinga用のSlowQuery監視スクリプトを書いてみた。
前提として、こちらを参考に、SlowQuery取得の設定が必要。
NRPE経由で監視する場合は、MAX_PACKETBUFFER_LENGTH等の値の変更をしたら幸せになれます。
[bash]
#!/bin/bash
CMDNAME=`basename $0`
while getopts h:u:p:c:w: OPT
do
case $OPT in
"h" ) FLG_H="TRUE" ; VALUE_H="$OPTARG" ;;
"u" ) FLG_U="TRUE" ; VALUE_U="$OPTARG" ;;
"p" ) FLG_P="TRUE" ; VALUE_P="$OPTARG" ;;
"w" ) FLG_W="TRUE" ; VALUE_W="$OPTARG" ;;
"c" ) FLG_C="TRUE" ; VALUE_C="$OPTARG" ;;
* ) echo "Usage: $CMDNAME [-h HOST_NAME] [-u User] [-p PASS] [-c warnning] [-c critical]" 1>&2
exit 1 ;;
esac
done
_HOST=$VALUE_H
_USER=$VALUE_U
_PASS=$VALUE_P
_WARN=$VALUE_W
_CRIT=$VALUE_C
# 何分前の以降に出たSLOW LOGを対象とする
_TIMESTAMP=’00:05′
#echo "$_HOST, $_USER, $_PASS, $_WARN, $_CRIT"
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
# ここからT-SQL処理
TDSVER=8.0 /usr/local/freetds/bin/tsql -H $_HOST -p 1433 -U $_USER -P $_PASS << _EOF > /tmp/${_HOST}_slowlog.tmp
DECLARE @VERSION_FULL nvarchar(max);
DECLARE @VERSION int;
SET @VERSION_FULL = (SELECT convert (nvarchar(max),SERVERPROPERTY(‘productversion’)));
SET @VERSION = substring(@VERSION_FULL, 1, 2)
DECLARE @DATE VARCHAR(16);
SET @DATE = (select CONVERT(VARCHAR,GETDATE()-‘$_TIMESTAMP’,120));
IF @VERSION = 10
BEGIN
SELECT REPLACE((
SELECT
DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) AS timestamp,
DB_NAME(CAST(event_data as XML).value(‘(/event/action[@name="database_id"]/value)[1]’, ‘sysname’)) AS database_name,
CAST(event_data as XML).value(‘(/event/action[@name="sql_text"]/value)[1]’, ‘nvarchar(max)’) AS sql_text,
CAST(event_data as XML).value(‘(/event/data[@name="duration"]/value)[1]’, ‘bigint’) AS duration,
CAST(event_data as XML).value(‘(/event/data[@name="cpu"]/value)[1]’, ‘bigint’) AS cpu_time
FOR XML PATH (”)), ‘ ‘, ‘ ‘) AS RESULT
from sys.fn_xe_file_target_read_file(
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\Highload_Query*.xel’,
‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\Highload_Query*.xem’ ,
NULL,
NULL)
where DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) >= @DATE
END
ELSE
BEGIN
IF @VERSION = 11
BEGIN
SELECT
DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) AS timestamp,
DB_NAME(CAST(event_data as XML).value(‘(/event/action[@name="database_id"]/value)[1]’, ‘sysname’)) AS database_name,
CAST(event_data as XML).value(‘(/event/data[@name="batch_text"]/value)[1]’, ‘nvarchar(max)’) AS batch_textt,
CAST(event_data as XML).value(‘(/event/data[@name="duration"]/value)[1]’, ‘bigint’) AS duration,
CAST(event_data as XML).value(‘(/event/data[@name="cpu_time"]/value)[1]’, ‘bigint’) AS cpu_time
from sys.fn_xe_file_target_read_file(
‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\Highload_Query*.xel’,
NULL,
NULL,
NULL)
where DATEADD(hour,9 , CAST(event_data as XML).value(‘(/event/@timestamp)[1]’, ‘datetime’)) >= @DATE;
END;
GO
quit
_EOF
IFS_BACKUP=$IFS
IFS=$’\n’
_TIME=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/timestamp>.*/"\1"/p’`)
_DBNAME=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/database_name>.*/"\1"/p’`)
_CPUTIME=(`cat /tmp/${_HOST}_slowlog.tmp |sed -n -e ‘s/.*\(.*\)<\/cpu_time>.*/"\1"/p’`)
_DURATION=(`cat /tmp/${_HOST}_slowlog.tmp | sed -n -e ‘s/.*\(.*\)<\/duration>.*/"\1"/p’`)
_QUERY=(`cat /tmp/${_HOST}_slowlog.tmp | tr ‘\n’ ‘ ‘| sed -e ‘s/<\/cpu_time>/<\/cpu_time>\n/g’| sed -n -e ‘s/.*\(.*\)<\/sql_text>.*/"\1"\n/p’`)
func_message (){
for (( i=0; $i<`echo ${#_TIME[@]}`; i++ ));
do
echo "———-"
echo "TIME: ${_TIME[$i]}"
echo "DBNAME: ${_DBNAME[$i]}"
echo "CPU_TIME: ${_CPUTIME[$i]} ms"
echo "DURATION: ${_DURATION[$i]} us"
echo "${_QUERY[$i]}"
done
IFS=$IFS_BACKUP
}
# check
if [ $_CRIT -le `echo ${#_TIME[@]}` ]
then
echo "CRIT: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
func_message
exit 2
elif [ $_WARN -le `echo ${#_TIME[@]}` ]
then
echo "WARN: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
func_message
exit 1
elif [ $_WARN -gt `echo ${#_TIME[@]}` ]
then
echo "OK: SlowQuery is `echo ${#_TIME[@]}` | SlowQuerys=`echo ${#_TIME[@]}`;$_WARN;$_CRIT"
exit 0
else
echo "status unknown"
exit 3
fi
[/bash]
使い方
./check_sqlserver_slowquery -h ホスト名orIPアドレス -u ユーザー名 -p パスワード -w ワーニング閾値 -c クリティカル閾値
閾値以下の場合は
OK: SlowQuery is 0 | SlowQuerys=0;3;4
とか表示される。
閾値以上の場合は
CRIT: SlowQuery is 6 | SlowQuerys=6;3;4
———-
TIME: “2016-01-08T10:42:00.427”
DBNAME: “hoge”
CPU_TIME: “3932” ms
DURATION: “4011259” us
“～～遅いクエリ～～”
———-
TIME: “2016-01-08T10:42:07.283”
DBNAME: “hoge”
CPU_TIME: “4431” ms
DURATION: “4481414” us
“～～遅いクエリ～～”
等が表示されます。