Using Elastic search connect to SQL database and text querying by Kibana

It’s mainly about the introduction about setting and configuration. The last part is about data importing from SQL server database.

Environment

AWS EC2 instance
Windows Server 2016
JRE jre-8u211-windows-x64

It’s easier to use Linux.

Nothing is absolutely correct or better. All trade-offs.

Downloads

ELK 7.5.0

Elasticsearch

https://www.elastic.co/cn/downloads/elasticsearch https://www.elastic.co/guide/en/elasticsearch/reference/current/windows.html

Logstash

https://www.elastic.co/cn/downloads/logstash

Kibana

https://www.elastic.co/cn/downloads/kibana

MS SQL JDBC

https://www.microsoft.com/en-us/download/details.aspx?id=54671

Then installation

Elasticsearch
Follow the MSI instruction

Logstash
Unzip the file and press \bin\logstash.bat

Kibana
Unzip the file and press \bin\kibana.bat

And ya.. you just finished the installation. It reminds me about the “good” old days of 5.0.0. What a life XD

I assume that we all set JRE env. well. Just a snapshot for you.

The it’s the issue itself. It may be seemed easy but it costed me time figuring out the solution. Believe me. Before invention of Laplace, you can never imagine the equation.

Elastic stack also claimed it’s an “issue”. It will be fixed in the later version.

Solution：Copy JDBC driver .jar file to \logstash-7.5.0\logstash-core\lib\jars

Logstash is able to read SQL database via JDBC driver now.

OK. It’s the last step. We need to set the configuration of Logstash to import data into elasticsearch.

Create a Logstash config: sqlserver.conf (rename as your willing)
in \logstash-7.5.0\config

Content:

input {
jdbc {
jdbc_connection_string => “jdbc:sqlserver://localhost:1433;databaseName=SAMXIAODEMO;integratedSecurity=false;”
jdbc_driver_library => “”
jdbc_driver_class => “com.microsoft.sqlserver.jdbc.SQLServerDriver”
jdbc_user => “SQL DB account”
jdbc_password => “SQL DB password”
statement => “SELECT * FROM DB.SCHEMA.TABLE”
}
}
output {
elasticsearch {
hosts => [“localhost:9200″]
index => “your index name”
}
}

Please do NOT change the line: jdbc_driver_library => “”
And you’re welcome. it seemed so “EASY”. right?

And operate the cmd in folder logstash
> bin\logstash.bat -f config\sqlserver.conf

If you still fail to connect to database, please refer to the link：

https://discuss.elastic.co/t/error-com-microsoft-sqlserver-jdbc-sqlserverdriver-not-loaded-are-you-sure-youve-included-the-correct-jdbc-driver-in-jdbc-driver-library/204440

OK. That’s it. Let’s check the count of records. Make sure the result is correct in ELK.

GET “your index name”/_count

No problem!

上一篇

https://datamansamxiao.wordpress.com/2019/12/05/%e7%94%a8-elk-elasticsearch-%e5%ad%98%e5%8f%96-sql-rdb-%e8%b3%87%e6%96%99-kibana-%e6%96%87%e5%ad%97%e6%9f%a5%e8%a9%a2%e5%8a%9f%e8%83%bd-1/

下一篇

https://datamansamxiao.wordpress.com/2019/12/05/%e7%94%a8-elk-elasticsearch-%e5%ad%98%e5%8f%96-sql-rdb-%e8%b3%87%e6%96%99-kibana-%e6%96%87%e5%ad%97%e6%9f%a5%e8%a9%a2%e5%8a%9f%e8%83%bd-3/

Originally published at http://datamansamxiao.wordpress.com on December 6, 2019.