# Enriching winlogbeat data with the jdbc\_static filter

**URL:** <https://discuss.elastic.co/t/enriching-winlogbeat-data-with-the-jdbc-static-filter/142113>\
**Category:** Logstash\
**Created:** [July 30, 2018, 7:49am UTC](https://discuss.elastic.co/t/enriching-winlogbeat-data-with-the-jdbc-static-filter/142113 "2018-07-30T07:49:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![victor.nilsson](https://avatars.discourse-cdn.com/v4/letter/v/eb8c5e/32.png) [@victor.nilsson](https://discuss.elastic.co/u/victor.nilsson)\
**Post date:** [July 30, 2018, 7:49am UTC](https://discuss.elastic.co/t/enriching-winlogbeat-data-with-the-jdbc-static-filter/142113/1 "2018-07-30T07:49:08Z")

</div>

Hi

I have a MySQL database with the following table structure:

ID, FQDN, IP, SERVICE

I want to enrich the logs recevied from our Windows servers using winlogbeat. I'd like to add the following fields:

1. ID =\> ID
2. FQDN =\> host
3. SERVICE =\> service

I have the following configuration which i have not yet gotten to work:

```
filter {
  if "beats" 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 cenDevFQDN = :computer_name"
          parameters => {host => "[cenDevFQDN]"}
          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"]
           }
       }
   }
}

```

Here i try to get all of the information about a device by searching for its FQDN in our database. I saw that in kibana, the field "computer\_name" is the FQDN of our devices.

> query =\> "select \* from elkDevIndexAssoc WHERE cenDevFQDN = :computer\_name"

This part is what's failing:

> parameters =\> {host =\> "[cenDevFQDN]"}

In the logstash logs i get the following:

> [2018-07-30T09:48:04,239][WARN][logstash.filters.jdbc.lookup] Parameter field not found in event {:lookup\_id=\>"localObjects", :invalid\_parameters=\>["[cenDevFQDN]"]}

What am i doing wrong here?

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [August 7, 2018, 3:14pm UTC](https://discuss.elastic.co/t/enriching-winlogbeat-data-with-the-jdbc-static-filter/142113/2 "2018-08-07T15:14:55Z")

</div>

AFAICT the problem lies with your `parameters => {host => "[cenDevFQDN]"}` line.  
Each of these parameters lines up like this:

- The left hand side (you have host) must be the same as the substitution string (minus the colon) in your statement, you have `computer_name`.
- the right hand side must refer to a field in an event that is coming from winlogbeat, [from the docs](https://www.elastic.co/guide/en/beats/winlogbeat/current/exported-fields-eventlog.html) I presume it is `computer_name`.

Based on this, I think the parameters line should be:  
`parameters => {computer_name => "[computer_name]"}`

The [docs have been updated recently to better explain this](https://www.elastic.co/guide/en/logstash-versioned-plugins/current/v1.0.5-plugins-filters-jdbc_static.html#v1.0.5-plugins-filters-jdbc_static-local_lookups).

Perhaps changing you statement and the parameters will make it more obvious (in 6 months time):

```auto
  query => "select * from elkDevIndexAssoc WHERE cenDevFQDN = :substitute_this"
  parameters => {substitute_this => "[computer_name]"}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 4, 2018, 3:15pm UTC](https://discuss.elastic.co/t/enriching-winlogbeat-data-with-the-jdbc-static-filter/142113/3 "2018-09-04T15:15:08Z")

</div>

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