Elasticsearchを使った、テーブル件数、サイズの履歴保存

DBサーバを運用していく中で、DBやテーブルの件数やサイズを確認したいタイミングがあるでしょう。
また、現在の状態だけではなく、過去の状態はどうだったかも必要になる場合も出てきます。
Elasticsearchを利用して、定期的にテーブルの件数やサイズを保存しておくことで、
あとから確認できるように準備したいと思います。
データ保存用のバッチを作成します。

準備するもの
Elasticsearch 5.6.3
kibana 5.6.3
logstash 5.6.3
JAVA 8
Microsoft SQL Server 用 JDBC Driver
https://docs.microsoft.com/ja-jp/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15
Microsoft JDBC Driver 7.4

Elasticsearch、kibanaなどは設定済みとして進めます。
Elasticsearch　192.168.0.37:9200
SQLServer 　　 192.168.0.36:1433　←このDBサーバの情報を取得します。

バッチは、ElasticsearchとSQLServerに接続できる環境上にあればよいのですが、今回はElasticseachのサーバに格納しました。

DBTableSize.bat

@setlocal enabledelayedexpansion

cd /d "%~dp0"

SET d=%date%
SET yyyy=%d:~-10,4%
SET mm=%d:~-5,2%
SET dd=%d:~-2,2%

SET EXENAME=%0

REM ES取り込み先インデックス名
SET TABLESIZE_INDEX_NAME=dbtable-db2-%yyyy%.%mm%

SET ES_SERVER=192.168.0.37
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 DBTableSize.conf --path.settings=/%LOGSTASH_PATH_sura%/config | FINDSTR "ERROR"
SET RETCODE=!ERRORLEVEL!

echo _logstash:!RETCODE!
REM 「ERROR」があったら、0
IF "!RETCODE!" EQU "0" (
  eventcreate /T ERROR /id 100 /d "%EXENAME% 失敗 Logを確認してください。%LOGSTASH_PATH%\logs"
)

TABLESIZE_INDEX_NAME　Elasticsearchに作成するインデックス名です。「dbtable-」はテンプレート用のプレフィックス。サーバ名と年月を入れます。

ES_SERVER　ElasticsearchのIPアドレス

LOGSTASH_PATH　logstashの格納フォルダ

DBTableSize.conf

input {
  jdbc {
    jdbc_driver_library => "C:\App\Microsoft JDBC Driver 7.4 for SQL Server\sqljdbc_7.4\jpn\mssql-jdbc-7.4.1.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://192.168.0.36:1433;integratedSecurity=true;"
    jdbc_user => ""
    jdbc_password => ""
    statement => "
sp_executesql N'
SET NOCOUNT ON
create table #tbname (
   [sampledate] datetime
  ,[server_name] varchar(50)
  ,[db_name] varchar(50)
  ,[table_name] varchar(128)
  ,[rows] decimal(13,0)
  ,[pages] decimal(13,0)
  ,[size] decimal(13,0)
 )
 
 
  EXEC sp_MSForEachDB
''use ?;
IF ''''?'''' <> ''''master'''' AND ''''?'''' <> ''''model'''' AND ''''?'''' <> ''''msdb'''' AND ''''?'''' <> ''''tempdb''''
BEGIN
insert into #tbname 
( [sampledate],[server_name],[db_name],[table_name],[rows],[pages],[size])
SELECT
CONVERT(varchar,getdate(),120)
,@@SERVERNAME
,DB_NAME()
,object_name(id)as ''''Table Name'''' 
,rowcnt as ''''Number of Rows''''
,dpages as ''''Number of Pages'''',(dpages * 8  )/1024 ''''サイズ(MB)''''
FROM sysindexes
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, ''''IsUserTable'''') = 1

END

''

SELECT * FROM #tbname
ORDER BY 1,2,3,4
'
"
  }
}
filter {

}
output {
  elasticsearch {
    hosts => ["${ES_SERVER}:9200"]
    index => "${TABLESIZE_INDEX_NAME}"
    document_type => "dbtable"
    template => "DBTableSize.json"
  }


  stdout {  }
}

jdbc_driver_library　java8用jdbcドライバのパスです。

jdbc_connection_string　DBサーバの接続文字列で、Windows認証です。SQL認証にする場合は、適宜変更してください。

statement　DB、テーブルサイズ取得用のSQL文です。

DBTableSize.json

{
  "template": "dbtable-*",
  "order": 0,
  "index_patterns": [
    "dbtable-*"
  ],
  "settings": {
    "index" : {
        "number_of_shards" : 1, 
        "number_of_replicas" : 0 
    }
  },
  "mappings": {},
  "aliases": {}
}

Elasticsearchのテンプレート

作成したDBTableSize.batを定期的に実行します。

タスクスケジューラに日次タスクとして登録してください。

kibanaの設定

Managementから、Index Patternsを登録します。

Discoverから値を確認できます。

Visualizeにvisualizationを登録しておくと視覚的に確認できます。