sqlbeat

Sqlbeat

Fork of sqlbeat for pulling SQL query data into WorkWave Sonar.

sqlbeat is a fully customizable Elastic Beat for MySQL/Microsoft SQL Server/PostgreSQL servers - this beat can ship the results of any query defined in the config file to Elasticsearch.

Current status

Sqlbeat still in beta.

To Do

• Update to more recent libbeat (the current bound version is 5.0.0-snapshot)
• Add SSPI support for MSSQL
• Replace configuration arrays with objects to better support multiple queries
  • Include default settings (apply to all queries unless over-ridden)
  • Run queries at different times
  • Output results to separate elasticsearch indexes and types
  • Use separate elasticsearch templates for each query
• (Thinking about it) Add option to save connection string in the config file - will open support for all SQLDrivers.
• Add sample queries and template file

Features

• Connect to MySQL / Microsoft SQL Server / PostgreSQL and run queries
• single-row queries will be translated as columnname:value.
• two-columns will be translated as value-column1:value-column2 for each row.
• multiple-rows each row will be a document (with columnname:value) - no DELTA support.
• show-slave-delay will only send the "Seconds_Behind_Master" column from SHOW SLAVE STATUS; (For MySQL use)
• Any column that ends with the delatwildcard (default is __DELTA) will send delta results, extremely useful for server counters. ((newval - oldval)/timediff.Seconds())

How to Build

Sqlbeat is written in golang. To build, you will need to install and configure a golang environment.

Golang supports cross-compilation. So while sqlbeat is only needed/used under windows at WorkWave, you may build sqlbeat on any go platform.

The Makefile was developed on a mac, so there may well be unintended OS dependencies. If you wish to develop under Windows (not such a bad idea), you will probably need to install cygwin or similar. Since sqlbeat depends on libbeat (the Makefile includes a shared make includefile with a bunch of shared targets), you will probably need to install some type of unix environment on your PC (for example, cygwin).

The sqlbeat project uses older versions of some dependent libraries. golang would prefer to use current versions. The project uses glide to ensure that the required version of each dependency (including especially libbeat) is installed and referenced. Be sure to run glide update (as described below) before building or you may run into syntax / version compatibility errors. There is a to-do (above) to update sqlbeat to a more recent version of libbeat.

Pre-requisites

• Install Golang
• Install Glide
• Install gnumake (if not on a unix box)

Building

After installing the pre-requisites, add this project to your gopath.

go get github.com/workwave/sqlbeat

This will add the project and any go dependencies to your gopath src directory.

Once glide and make are installed, update the dependencies and run make.

glide update
make 

If you see syntax errors from the build, be sure that you have the proper version of the dependencies -- make sure that glide update completes successfully.

Creating a Distribution

As with other elastic beats, sqlbeat is distributed as a zip file that includes the executable, sample configuration files and a couple of powershell scripts to help install the executable as a service under windows.

The makefile dist target uses golang gox cross-compiler to build the windows executable, bundles it with the other files and zips it up. The makefile includes a

Deploying and Running

WorkWave deploys this beat as part of sonar. The configuration files in this project are for testing purposes only.

Configuration

Edit sqlbeat configuration in sqlbeat.yml .

You can:

• Choose DB Type
• Add queries to the queries array
• Add query types to the querytypes array
• Define Username/Password to connect to the DB server
• Define the column wild card for delta columns
• Password can be saved in clear text/AES encryption

Notes on password encryption: Before you compile your own mysqlbeat, you should put a new secret in the code (defined as a const), secret length must be 16, 24 or 32, corresponding to the AES-128, AES-192 or AES-256 algorithm. I recommend deleting the secret from the source code after you have your compiled mysqlbeat. You can encrypt your password with mysqlbeat-password-encrypter just update your secret (and commonIV if you choose to change it) and compile.

Template

Since Sqlbeat runs custom queries only, a template can't be provided. Once you define the queries you should create your own template.

Testing

Just run sqlbeat -c sqlbeat.yml and you are good to go.

To enable debugging to console:

sqlbeat -c sqlbeat.yml -e -d "*"

License

The license for the forked repository was an Apache v2 license as of the time of the fork (August 5, 2017). The LICENSE is included in this repository for reference.