Importing SQL data inside Elasticsearch/Kibana using Logstash

Prerequisite:

• Configure the Elasticsearch instance.
• Configure the Kibana instance.
• Install the sqljdbc driver library.

In order to import the SQL data we need to configure the import file. So let’s start by first describing the configurations inside the import file. The first part of your configuration file would be about your inputs.

Inputs are Logstash plugins responsible for ingesting data. There are a lot of options around this input, and the full documentation can be found here. For now, let’s assume you want to send the contents of the SQL data.

input { 
	 jdbc { 
		jdbc_connection_string => "jdbc:sqlserver://<SQL_CONNECTION_STRING>$"
		jdbc_user => "user"
		jdbc_password => "password"
		jdbc_driver_library => "<jdbc_driver_library_location>\sqljdbc41.jar"
		jdbc_driver_class => "Java::com.microsoft.sqlserver.jdbc.SQLServerDriver"
		statement => "<SQL SELECT STATEMENT> "
	 } 
}

jdbc_connection_string: JDBC connection string. This is a required setting. Value type is string. There is no default value for this setting.
jdbc_user: SQL user name
jdbc_password: SQL password
jdbc_driver_library: JDBC driver library path to third party driver library. In case of multiple libraries being required you can pass them separated by a comma.
jdbc_driver_class: The JDBC driver class to load
statement: The SQL select statement. If undefined, Logstash will complain, even if codec is unused.

Logstash Filter.

A filter performs intermediary processing on an event. Filters are often applied conditionally depending on the characteristics of the event. The full documentation can be found here.

filter {  
	date {
		match => [ "signupdate" , "dd/MMM/yyyy:HH:mm:ss -0600" ]
		timezone => "CET"
	}	
}

Once you configured the input and the filters you are now ready to configure the output.

Logstash Output
The output is used to send the SQL data to the Elasticsearch Instance.
The full documentation can be found here.

output {
	elasticsearch {
		index => "<index_name>"		
		document_id => "%{id}"
		hosts => ["<Elasticsearch_Instance>"]		
	}
}

index: The index to write events to. This can be dynamic using the %{foo} syntax. The default value will partition your indices by day so you can more easily delete old data or only search specific date ranges. Indexes may not contain uppercase characters.
document_id: The document ID for the index. Useful for overwriting existing entries in Elasticsearch with the same ID.
hosts: The Elasticsearch instance.

The full file should look like this:

input { 
	 jdbc { 
		jdbc_connection_string => "jdbc:sqlserver://<SQL_CONNECTION_STRING>$"
		jdbc_user => "user"
		jdbc_password => "password"
		jdbc_driver_library => "<jdbc_driver_library_location>\sqljdbc41.jar"
		jdbc_driver_class => "Java::com.microsoft.sqlserver.jdbc.SQLServerDriver"
		statement => "<SQL SELECT STATEMENT> "
	 } 
 }
filter {  
	date {
		match => [ "signupdate" , "dd/MMM/yyyy:HH:mm:ss -0600" ]
		timezone => "CET"
	}	
}
output {
	elasticsearch {
		index => "<index_name>"		
		document_id => "%{id}"
		hosts => ["<Elasticsearch_Instance>"]		
	}
}

Once you created the configuration file, you could copy this file inside Logstash bin folder.

In order to start the import process, start the command prompt window with admin rights. Navigate to Logstash bin folder and run the following command:

logstash -f <configuration_file_name>.conf