JDBC - SQL Query Issue

Elastic Stack Logstash
1

Hi! I have a database in MySQL and I want to fetch some info based on IP addresses I get from my client logs. There are ip_from(VARCHAR 50), ip_to(VARCHAR 50), ip_from_int(INT 10), and ip_to_int(INT 10) plus a name(VARCHAR 255) column in my desired table. I have written the filter as below and I want to fetch the name if the IP address is between ip_from and ip_to. But it keeps telling me that it cannot recognize INET_ATON as a function in JDBC. Is there a way I can get my result? the "clientip" is in message field.

filter {
  jdbc_static {
    loaders => [
      {
        id => "iptable"
        query => "select ip_from, ip_to, name from <db_table_name>"
        local_table => "iptable"
       }
    ]
    local_db_objects => [
      {
        name => "iptable"
        index_columns => ["name"]
        columns => [
          ["ip_from", "varchar(50)"],
          ["ip_to", "varchar(50)"],
          ["name", "VARCHAR(255)"]
        ]
      }
    ]
    local_lookups => [
      {
        id => "get-data-name"
        query => "SELECT name FROM iptable WHERE inet_aton(ip_from) <= inet_aton(:cip) and inet_aton(ip_to) >= inet_aton(:cip)"
        parameters => { "cip" => "[clientip]"}
        target => "result"
      }
    ]
    add_field => { data_name => "%{[result][0][name]}" }
    remove_field => ["result"]

    loader_schedule => "0 */2 * * *"
    jdbc_user => "<user>"
    jdbc_password => "<password>"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://<mysql-ip>:<port>/<db>"
    staging_directory => "/var/logstash/jdbc_static/import_data/"
  }
 }

Here's the exception:

:exception=>"Java::JavaSql::SQLSyntaxErrorException: 'INET_ATON' is not recognized as a function or procedure.

I'd really appreciate if someone could help!
@Badger @magnusbaeck

2

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.