本文是按照「使用 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/