於Windows環境下安裝ELK及其介接SQL Server測試

本文是按照「使用 Elasticsearch 為 SQL Server 完成專案的純文字搜尋功能(一)- 安裝 ElasticSearch」所做的測試,

期望可透過Logstash服務來驅動JDBC,將資料儲存至Elasticsearch,並於Kibana站台上讀取SQL Server 資料表中的資料(DB Log)

ELK包的版本:

elasticsearch-7.4.2

kibana-7.4.2-windows-x86_64

sqljdbc_4.2

執行環境:WIN10、SQL SERVER 2014

本文不加以介紹ELK(可參考ELK介紹及架構),僅著重於安裝及操作設定。

提示:基於可以研究ELK服務讀取的路徑,本文作測試時,並未設定ELK的環境變數

進行以下測試前,請先下載並解壓縮所有的ELK工具至D:\ELK,如下圖啟動ElasticSearch服務:切換到ElasticSearch解壓縮目錄的bin資料夾,並執行「elasticsearch」指令,結果如下:ElasticSearch啟動正常的畫面測試,如下圖。

修改Kibana的設定檔:檔案位置為 D:\ELK\kibana-7.4.2-windows-x86_64\config\config/kibana.yml,移除註解,如下圖標示處。​
啟動Kibana服務:切換到kibana的解壓縮目錄的bin資料夾,並執行「kibana」指令,結果如下:完成上述操作即是把Kibana服務給啟動起來,就可以進入Kibana的首頁(http://localhost:5601/),如下圖。完成上述操作即是把Kibana服務給啟動起來,就可以進入Kibana的首頁(http://localhost:5601/),如下圖。

index建立語法參考:(命名一個名為「ting_index」的索引,按下▶,即會產生右邊的執行結果)

  • number_of_shards: 索引分片數(靜態屬性,第一次設定後,則無法再透過PUT方法做修改,欲修改,必須使用DELETE方法,刪掉整個index,再重新PUT)
  • number_of_replicas: 分片副本數(動態屬性,可以被修正)

指令:

PUT ting_index
{
  "settings": {
    "index":{
      "number_of_shards" : "3",
      "number_of_replicas" : "2"
    }      
  }
}

分片數及副本數的定義可參考:https://juejin.im/post/5c3e9813518825552880084a

使用GET方法做查詢,結果如下:(按下▶,右邊窗格會顯示其Index的json內容)

修改number_of_replicas屬性,寫法如下:(輸入完按下▶,右邊窗格會顯示acknowledged為true,表示修改成功,可以再次使用GET方法做檢查)

於SQL SERVER執行資料表及其資料新增語法,參考以下:

create table AppLog
(
	[LogID] varchar(20),
	[Tid] varchar(20),
	[ParentTid] varchar(20),
	[Timestamp] datetime,
	[UserID] varchar(10),
	[ExecutionTime] numeric(16,8),
	[Direction] char(1),
	[SessionID] varchar(100),
	[MenuID] varchar(50),
	[Source] char(1),
	[Level] char(1),
	[AppID] varchar(20),
	[HostIP] varchar(50),
	[HostName] varchar(100),
	[ClientIP] varchar(50)
	primary key([LogID])
)

insert into AppLog
values
(
	'L2549442046X8003',
	'T2549442046X8002',
	'P2549442046X8000',
	getdate(),
	'USER001',
	0.0146566,
	1,
	'yqrdvh9aosf0votbngzfu23b',
	'StockInfo',
	1,
	1,
	'TingSystem.MVC',
	'192.168.123.1',
	'DESKTOP-BAS9PLU',
	'192.168.123.1'
)

設定JDBC:並將JDBC壓縮包解壓縮至D:\ELK,並找到其中的jdbc driver檔案。(下圖為4.2版的driver名稱)

將上述jdbc driver檔案放入Logstash的jar庫中(資料夾位置:D:\ELK\logstash-7.4.2\logstash-core\lib\jars),如下圖。承上,若未將該jar檔加入,則在執行logstash -f指令時,會出現以下。

 Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
 Exception: LogStash::PluginLoadingError

新增客製的.conf檔,自行設定其中的資料庫連線參數,儲存在logstach的解壓縮目錄中。(本範例將此檔命名為SQLlog.conf,並存於D:\ELK\logstash-7.4.2\config\custom)

SQLlog.conf的內容:

input {
	jdbc {
		jdbc_connection_string => "jdbc:sqlserver://localhost:1433;databaseName=LogDB;integratedSecurity=false;"
		jdbc_driver_library => ""
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_user => "sa"
		jdbc_password => "sa"
		statement => "select * from [dbo].[AppLog]"
	}
}
output {
	elasticsearch {
		hosts => ["localhost:9200"]
		index => "ting_index"
	}
}

承上圖,jdbc_driver_library屬性設定為空,是期望在執行logstash指令時,會抓預設的jar庫的路徑(D:\ELK\logstash-7.4.2\logstash-core\lib\jars),來正常讀取jdbc driver檔案,或者可由讀者自行設定指定位置。

補充:本範例使用SQL Server通訊埠1433,故在設定前可先檢查SQL組態工具上的TCP/IP設定,如下圖。承上,若有修改組態參數,要記得重新啟動SQL Server服務,如下圖。(操作logstash服務才會執行正確)

開啟CMD,使用logstash -f指令,來載入.conf檔(完整logstash指令說明,可參考:https://www.elastic.co/guide/en/logstash/current/running-logstash-command-line.html)

執行結果:

承上圖,若JDBC連線無誤,將會列出SQL腳本執行的時間,並顯示「Logstash shut down」,離開Logstash

回到Kibana的Dev tools頁面,執行index的GET查詢,結果如下:

補充:若上圖標示的hits屬性中為空時,要回去檢查logstash -f 在執行時,是否有錯誤訊息輸出。

心得:

  • 當有修改.conf,並重新執行logstash指令測試時,當發生錯誤,ElasticSearch內的資料仍會保持上一次正常執行的結果。若要測試最新的資料結果,可以在執行logstash指令前,直接把整個INDEX整個DELETE掉,再重新PUT,如下指令。
  • 若logstash指令執行結果產生中文的Error亂碼時,可於 D:\ELK\logstash-7.4.2\logs\logstash-plain.log 檔案中找到中文明碼的訊息。
DELETE ting_index
PUT ting_index
{
  "settings": {
    "index":{
      "number_of_shards" : "3",
      "number_of_replicas" : "2"
    }      
  }
}

完成後,可接續參考「以SQL Server作為資料來源的Kibana儀錶板基礎設定

參考來源:

使用 Elasticsearch 為 SQL Server 完成專案的純文字搜尋功能(一)- 安裝 ElasticSearch

https://codertw.com/%E7%A8%8B%E5%BC%8F%E8%AA%9E%E8%A8%80/394410/

https://medium.com/@xiaoxiaosam007/using-elastic-search-connect-to-sql-database-and-text-querying-by-kibana-5303cadf0557