SQLServer+Logstash+Elasticsearch+kibana 5.6.3

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