Viewing Real-time data streams (MySql, Logstash & WebSockets)

Posted by Hendrik Jacobs June 01, 2021

Viewing Real-time data streams (MySql, Logstash & WebSockets) - Part 2

Part 1

Before You Start

hint: Always backup anything before making a change to it

How To

You can also find these instructions here: https://github.com/HaanstootZA/sql-monitor

Open Command Line

WINDOWS+R
type CMD
press enter

Run Application/Command

Open command line
type `
press enter

Environment Variables

Before appending any values it is advised to copy the existing value into a text file and save that.

Run the 'rundll32 sysdm.cpl,EditEnvironmentVariables' command.

Adding a new value

new
enter variable name.
enter variable value.

Appending an item to an existing value.

open the existing item.
append a semicolon (';') and the value you want to add to the existing text.
Click the "OK" button.

Linux v Windows

A lot of open source applications are written with Linux in mind so it's useful to know some of the differences you might face when it comes to configuration.

Linux

Path Separators: backslash ('/')
Quotation Marks: single quotes (' ')
Closing Console Applications: CTRL+D

Windows

Path Separators: forward slash (''\")
Quotation Marks: double quotes (" ")
Closing Console Applications: CTRL+C

Preparing the environment

You should keep track of the versions for all the software you install so that you can use it in the paths below.

Installing Java

Download the jre8 installer from https://download.java.net/openjdk/jdk11/ri/openjdk-11+28_windows-x64_bin.zip.

Extract the contents of the folder to C:\Program Files\Java\.

Testing Java.

Open a command prompt instance
Navigate to C:\Program Files\Java
Run the following command within the command line.

java -version

You should see the text.

java version and the Java Version you installed

Logstash for Windows

Installation

Download the Logstash installer from https://www.elastic.co/downloads/logstash-oss.
To install Logstash follow these steps.

Create the folder "C:\Elastic".
Unzip the contents of the downloaded file into "C:\Elastic".
You should see the following folder "C:\Elastic\logstash-[version]".

Testing your installation.

Open command line.
Navigate to the new folder you created by executing the following command.

cd C:\Elastic\logstash-[version]

Execute the following command.

bin\logstash -e "input { stdin {} } output { stdout {} }"

Set the following Environment Variables.

Create/Replace "JAVA_HOME" with "C:\Elastic\logstash-[version]\jdk" (or the latest installation path for Java).
Append "PATH" with "JAVA_HOME\bin".

Configuration

Backup the following files (copy-paste and rename the extensions to .bak)

C:\Elastic\logstash-[version]\config\logstash.yml
C:\Elastic\logstash-[version]\config\pypelines.yml
C:\Elastic\logstash-[version]\Gemfile

Create the following files

C:\Elastic\logstash-[version]\config\example.mysql.cfg

Replace the contents of the files as below

C:\Elastic\logstash-[version]\config\logstash.yml

log.level : info

C:\Elastic\logstash-[version]\config\pipelines.yml

- pipeline.id: example-mysql-pipeline
path.config: config/example.mysql.cfg
pipeline.workers: 1

C:\Elastic\logstash-[version]\config\example.mysql.stdout.cfg

input {
      jdbc {
          jdbc_driver_library => "./jdk/connectors/mysql-connector-java-8.0.25.jar"
          jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
          jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/sys"
          jdbc_user => "root"
          jdbc_password => "password"
          schedule => "*/3 * * * * *"
          statement => "SELECT Id, Item, Price FROM LogWatcherTest WHERE Id > :sql_last_value"
          tracking_column => "id"
          use_column_value => true
      }
  }
  output {
      websocket {
          id => "sqlite_web_socket_output_id"
          host => "0.0.0.0"
          port => "3232"
          codec => "json"
      }
  }

Installing the WebSocket Plugin

Add the below line to the end of the "C:\Elastic\logstash-[version]\Gemfile" file

gem "logstash-output-websocket", :path => "./logstash-output-websocket"

Copy the logstash-output-websocket folder from https://github.com/HaanstootZA/sql-monitor/tree/main/logstash into "C:\Elastic\logstash-[version]"
Within your logstash folder run the following commands within command prompt

bin/logstash-plugin install --no-verify

Installing MySQL

Download the MySQL windows installer from https://dev.mysql.com/downloads/installer/.
Run the downloaded installer.
Choose Developer Default as your option.
Wait for the installation to ask you for a username and password.
Set your root user password to "password".
Copy the JDBC driver file
From C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.25.jar
To C:\Elastic\logstash-7.13.0\jdk\connectors
Open an instance of MySQL Workbench.
Execute the below create table statement within your MySQL session.
CREATE TABLE sys.logWatcherTest (
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Item VARCHAR(255) NOT NULL,
Price DECIMAL(19, 3) NOT NULL );
Executing the applications

Logstash

Run the following commands in command line.

cd C:\Elastic\logstash-[version]
run bin\logstash

Don't close the window until you are finished with playing around.

HTML WebSocket Client

Download the WebSocket Application from the github repository.
Open the index.html file in a browser and leave it running.

Running the Demo

Open a MySQL workbench instance

Run the following SQL query.
INSERT INTO LogWatcherTest (Item, Price) VALUES ("Makita Drill", "12,2");

Finally check your logstash output for the row that was just inserted as well as your browser for a new line within the site.

Hendrik Jacobs