SQLServerの拡張イベントで出力したスロークエリのログをLogstashで読み込み、Elasticsearchに取り込み、kibanaで可視化します。
準備するファイル
elasticのサイトから
elasticsearch-5.6.3.zip
kibana-5.6.3-windows-x86.zip
logstash-5.6.3.zip
javaのサイトから
java Version 8 Update 144
jre-8u144-windows-x64.exe
microsoftのサイトから
Microsoft JDBC Driver 6.2 for SQL Server
sqljdbc_6.2.1.0_jpn.exe
1.javaのインストール
インストール後、PATHを設定します。
JAVA_HOME
C:\Program Files\Java\jre1.8.0_144
Pathに下記を追加
;C:\Program Files\Java\jre1.8.0_144\bin
2.JDBCドライバのインストール
解凍後、C:\App\Microsoft JDBC Driver 6.2 for SQL Serverに配置します。
C:\App\Microsoft JDBC Driver 6.2 for SQL Server\sqljdbc_6.2\jpn\auth\x64 配下にある、
「sqljdbc_auth.dll」を、1でインストールしたJavaフォルダの配下にコピーします。
C:\Program Files\Java\jre1.8.0_144\bin
Windows認証で接続するためのもの
3.Elasticsearch5.6.3のインストール
ダウンロードしたelasticsearch-5.6.3.zipを適当なフォルダに解凍します。
(例では、C:\Appに解凍しました)
elasticsearch.ymlファイルを変更します。
cluster.name: [クラスタ名 適当な名前] network.host: [ElasticsearchのIPアドレス] http.port: 9200
サービス登録します。
C:\App\elasticsearch-5.6.3\bin\elasticsearch-service.bat install
インストール後は、サービスを開始します。
また、必要に応じてスタートアップの種類を「自動」に変更します。
4.Logstash5.6.3のインストール
ダウンロードしたlogstash-5.6.3.zipを適当なフォルダに解凍します。
(例では、C:\Appに解凍しました)
logstash.ymlファイルはデフォルトのままにしました。
5.kibana5.6.3のインストール
ダウンロードしたkibana-5.6.3-windows-x86.zipを適当なフォルダに解凍します。
(例では、C:\Appに解凍しました)
kibana.ymlファイルを変更します。
C:\App\kibana-5.6.3-windows-x86\config\kibana.yml
server.port: 5601 server.host: "127.0.0.1" (or kibanaのIPアドレス) elasticsearch.url: "http://[ElasticsearchのIPアドレス]:9200" elasticsearch.preserveHost: true kibana.index: ".kibana" kibana.defaultAppId: "discover"
C:\App\kibana-5.6.3-windows-x86\bin\kibana.batを実行するとkibanaが起動します。
6.elasticseachにスロークエリ用のtemplateを作成
2ファイル作成します。
C:\script\ES_slowlog.json
{
"template": "slowquery-*",
"order": 0,
"settings": {
"index" : {
"number_of_shards" : 1,
"number_of_replicas" : 1
}
},
"mappings": {
"_default_": {
"properties" : {
"@timestamp" : { "type" : "date" },
"servername" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"servicename" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"class" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"cpu_time" : { "type" : "float" , "index" : "not_analyzed" },
"physical_reads" : { "type" : "long" , "index" : "not_analyzed" },
"logical_reads" : { "type" : "long" , "index" : "not_analyzed" },
"writes" : { "type" : "long" , "index" : "not_analyzed" },
"row_count" : { "type" : "long" , "index" : "not_analyzed" },
"duration_sec": { "type" : "float" , "index" : "not_analyzed" },
"sql_text" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"statement" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"hostname" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"loginname" : { "type" : "string", "fielddata": true, "analyzer": "keyword" },
"log_timestamp" : { "type" : "date" }
}
}
}
}
}
C:\script\テンプレート登録用.bat
cd /d "%~dp0"
SET ES_SERVER=[elasticsearchのIP]:9200 ← 設定
SET template_script=Invoke-WebRequest -Uri "http://%ES_SERVER%/_template/slowquery -Method POST -InFile ES_slowlog.json"
REM ESにテンプレートを作成
REM curl -XPUT http://%ES_SERVER%/_template/slowquery --data-binary @ES_slowlog.json
powershell -Command "%template_script%"
コマンドプロンプトからbatを実行します。
powershellを実行しているので、ポリシーを変更しておく
C:\script\テンプレート登録用.bat
7.スロークエリ収集用のバッチ作成
C:\script\ES_slowquery_DB1.conf
※UTF-8で保存する
input {
jdbc {
jdbc_driver_library => "C:\App\Microsoft JDBC Driver 6.2 for SQL Server\sqljdbc_6.2\jpn\mssql-jdbc-6.2.1.jre8.jar" #←2で配置したjdbcドライバを指定
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://192.168.11.34:1433;integratedSecurity=true;" #←監視するSQLSERVER、Windows認証で接続
jdbc_user => ""
jdbc_password => ""
statement => "
select CONVERT(varchar(30), xml_data.value('(/event/@timestamp)[1]','datetime') , 127) + 'Z' as '@timestamp'
, @@SERVERNAME as servername
, @@SERVICENAME as servicename
, xml_data.value('(event/@name)[1]', 'varchar(50)') AS class
, xml_data.value('(/event/data[@name=''cpu_time'']/value)[1]','bigint')/1000.0 cpu_time
, xml_data.value('(/event/data[@name=''physical_reads'']/value)[1]','bigint') physical_reads
, xml_data.value('(/event/data[@name=''logical_reads'']/value)[1]','bigint') logical_reads
, xml_data.value('(/event/data[@name=''writes'']/value)[1]','bigint') writes
, xml_data.value('(/event/data[@name=''row_count'']/value)[1]','bigint') row_count
, xml_data.value('(/event/data[@name=''duration'']/value)[1]','bigint')/1000000.0 duration_sec
, xml_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)') sql_text
, xml_data.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)') statement
, xml_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') hostname
, xml_data.value('(/event/action[@name=''username'']/value)[1]','varchar(max)') loginname
from
(select object_name as event, CONVERT(xml, event_data) as xml_data
-- スロークエリの出力先ファイルを指定
from sys.fn_xe_file_target_read_file ('G:\\work\\ExtendedEvents_log\\slowquery*.xel', 'G:\\work\\ExtendedEvents_log\\slowquery*.xem', null, null)
) v
where DATEADD(hh, 9, xml_data.value('(/event/@timestamp)[1]','datetime')) >= convert(varchar(15),dateadd(MI,-20,getdate()),120)+'0:00'
order by '@timestamp' asc
"
}
}
filter {
#indexを日付ごとに分けるため
ruby {
# code => "event.set('index_day', event.timestamp.time.localtime.strftime('%Y.%m.%d'))"
code => "event.set('index_day', event.get('[@timestamp]').time.localtime.strftime('%Y.%m.%d'))"
}
#重複取り込みを避けるためにユニークなIDを作成
ruby {
code => "event.set('computed_id', event.get('[@timestamp]').to_s + '_' + event.get('servername').to_s + '_' + event.get('class').to_s)"
}
}
output {
elasticsearch {
hosts => ["${ES_SERVER}:9200"]
index => "slowquery-%{index_day}"
document_type => "slowquery"
document_id => "%{computed_id}"
}
stdout { codec => rubydebug }
}
C:\script\ES_slowquery_DB1.bat
cd /d "%~dp0"
REM インデックス作成・変更
SET ES_SERVER=[ElasticsearchのIPアドレス]
SET LOGSTASH_PATH=C:\App\logstash-5.6.3
REM logstashのpath.settings指定用にスラッシュ区切り版を作成
set LOGSTASH_PATH_sura=%LOGSTASH_PATH:\=/%
call %LOGSTASH_PATH%\bin\logstash.bat -f ES_slowquery_DB1.conf --path.settings=/%LOGSTASH_PATH_sura%/config --path.data data
:END
8.バッチの実行
C:\script\ES_slowquery_DB1.bat
実行ユーザにSQLServerのログイン権限、VIEW SERVER STATEの権限がついていること
9.kibanaのSetting
Management - Index Patterns でslowquery用のパターンを作成します。
あとは、10分おきにES_slowquery_DB1.batを実行するようにタスクを設定する