Using metadata for MySQL lookups

Elastic Stack Logstash
1

Continuing the discussion from Enriching winlogbeat data with the jdbc_static filter:

I changed my approach and configuration from the previous thread, therefore i created a new thread because i still have the same problem.

I have the following JDBC_STATIC lookup for syslog which works perfectly:

filter {
  if "syslog" in [tags] {
    jdbc_static {
      loaders => [
        {
          id => "elkDevIndexAssoc"
          query => "select * from elkDevIndexAssoc"
          local_table => "elkDevIndexAssoc"
        }
      ]
      local_db_objects => [
        {
          name => "elkDevIndexAssoc"
          index_columns => ["cenDevIP"]
          columns => [
            ["cenDevSID", "varchar(255)"],
            ["cenDevFQDN", "varchar(255)"],
            ["cenDevIP", "varchar(255)"],
            ["cenDevServiceName", "varchar(255)"]
          ]
        }
      ]
      local_lookups => [
        {
          id => "localObjects"
          query => "select * from elkDevIndexAssoc WHERE cenDevIP = :host"
          parameters => {host => "[host]"}
          target => "cendotEnhanced"
          tag_on_failure => [ "sql_failure" ]
        }
      ]
      # using add_field here to add & rename values to the event root
      add_field => { cendotFQDN => "%{[cendotEnhanced[0][cendevfqdn]}" }
      add_field => { cendotSID => "%{[cendotEnhanced[0][cendevsid]}" }
      add_field => { cendotServiceName => "%{[cendotEnhanced[0][cendevservicename]}" }
      remove_field => ["cendotEnhanced"]
      jdbc_user => "username"
      jdbc_password => "password"
      jdbc_driver_class => "com.mysql.jdbc.Driver"
      jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.11.jar"
      jdbc_connection_string => "jdbc:mysql://84.19.155.71:3306/logstash?serverTimezone=Europe/Stockholm"
      #jdbc_default_timezone => "Europe/Stockholm"
      loader_schedule => "*/5 * * * *"
      add_tag => [ "sql_successful" ]
      tag_on_failure => [ "sql_failure" ]
      #tag_on_default_use => [ "sql_failure" ]
    }
      if ! [cendotFQDN] {
           mutate {
                  add_tag => [ "sql_failure" ]
           }
       }
   }
}

However if i use the same config with beats, i get an error complaining about the hosts field:

[2018-07-30T10:02:02,610][WARN ][logstash.filters.jdbc.lookup] Parameter field not found in event {:lookup_id=>"localObjects", :invalid_parameters=>["[host]"]}

How would i change the parameter to work with winlogbeat? I want to make a lookup on the IP where the logs originated from.

2

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