Logstash jdbc_static Filter Plugin

The jdbc_static filter plugin in Logstash allows you to enrich events with data from a database using JDBC. It's particularly useful for performing lookups against static or slowly-changing datasets, caching the results for improved performance. This plugin is ideal for scenarios where you need to add additional context or information to your events based on data stored in a relational database.

Syntax

The basic syntax for the jdbc_static filter is as follows:

filter {
  jdbc_static {
    jdbc_driver_library => "/path/to/mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "username"
    jdbc_password => "password"
    statement => "SELECT * FROM mytable WHERE id = :id"
    parameters => { "id" => "field_name" }
    target => "db_lookup"
  }
}

For more detailed information, refer to the official Logstash jdbc_static filter plugin documentation.

Example Use Case

Suppose you have log events containing user IDs, and you want to enrich these events with user information stored in a MySQL database. Here's an example configuration:

filter {
  jdbc_static {
    jdbc_driver_library => "/path/to/mysql-connector-java-5.1.36-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/users_db"
    jdbc_user => "logstash_user"
    jdbc_password => "logstash_pass"
    statement => "SELECT username, email FROM users WHERE id = :id"
    parameters => { "id" => "user_id" }
    target => "user_info"
    jdbc_pool_timeout => 5
    jdbc_validate_connection => true
    jdbc_validation_timeout => 3600
    cache_expiration => 300
  }
}

This configuration will look up the username and email for each user ID in the incoming events and add this information to the event under the "user_info" field.

Common Issues and Best Practices

1. Performance: Be mindful of the database load. Use caching and appropriate cache expiration settings to reduce database queries.
2. Connection issues: Ensure your database is accessible from the Logstash server and that the credentials are correct.
3. Data types: Make sure the data types in your SQL query match the expected types in Logstash.
4. Error handling: Use the tag_on_failure option to mark events that fail the database lookup.
5. Security: Store database credentials securely, preferably using Logstash's secure credential storage.

Frequently Asked Questions

Q: How can I improve the performance of jdbc_static lookups?
A: To improve performance, use caching by setting an appropriate cache_expiration time. Also, ensure your database has proper indexes on the columns used in the lookup query.

Q: Can I use jdbc_static with multiple databases?
A: Yes, you can use multiple jdbc_static filter instances in your Logstash configuration, each connecting to a different database.

Q: How do I handle null values returned from the database?
A: You can use Logstash's conditional statements to check for null values and handle them accordingly in your pipeline.

Q: Is it possible to perform joins or complex queries with jdbc_static?
A: Yes, you can use complex SQL queries including joins in the statement parameter, as long as they return a single row per lookup.

Q: How often does jdbc_static refresh its connection to the database?
A: The connection is maintained based on the jdbc_pool_timeout setting. You can also use jdbc_validate_connection and jdbc_validation_timeout to periodically validate and refresh the connection.