# Logstash filter to match jdbc identifier with yaml file

**URL:** <https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921>\
**Category:** Logstash\
**Created:** [February 25, 2020, 9:02pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921 "2020-02-25T21:02:02Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ads](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@ads](https://discuss.elastic.co/u/ads)\
**Post date:** [February 25, 2020, 9:02pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/1 "2020-02-25T21:02:02Z")

</div>

Hi,

I am using ES and logstash version 7.6.0. I am trying to plot a set of UK postcodes into maps but am not having much success. I have a set of dates and postcodes from a jdbc call. This works using the following query:

```
statement => "SELECT DELPCODE, DELDATE FROM [DATA].[dbo].[XXX]"
    parameters => {
      "delpcode" => "postcode"
      "deldate" => "delivery_date"
    }

```

To add the lat/long and generate a GeoPoint I am using a translate filter and a template. Similar to this write up: [http://www.sanjeevnandam.com/blog/logstash-convert-zipcodepostal-code-to-geo\_point-latitudelongitude](http://www.sanjeevnandam.com/blog/logstash-convert-zipcodepostal-code-to-geo_point-latitudelongitude).

However, because of the jdbc connection, I don't think the translation is correctly matching the DB fields because all my lat/long values are showing as 0.

The yaml file looks like this:

```
# head -n2 postcodes.yaml 
AB10 1XG:57.144165160000000,-2.114847768000000
AB10 6RN:57.137879760000000,-2.121486688000000

```

And the filter looks like this:

```
filter {
  # Convert to string
  mutate {
    convert => {
      delpcode => "string"
    }
  }

  # Match postcodes with yaml lat/long
   translate {
     field => "delpcode"
     dictionary_path => "/usr/local/downloads/postcodes.yaml"
   }

  mutate {
    #split into an array
    split => {"translation" => ","}
  }

  # Create new lat/lon fields
  mutate {
    add_field => ["latitude","%{[translation][0]}"]
    add_field => ["longitude","%{[translation][1]}"]
  }

  mutate {
    convert => { "longitude" => "float" }
    convert => { "latitude" => "float" }
  }

  mutate {
    rename => {
      "delpcode" => "postcode"
      "deldate" => "delivery_date"
      "longitude" => "[location][lon]"
      "latitude" => "[location][lat]"
    }
  }
}

```

Any help greatly appreciated.

Thanks,

Adam

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 25, 2020, 9:35pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/2 "2020-02-25T21:35:01Z")

</div>

field names are case sensitive. You have a field called [DELPCODE], not [delpcode].

---

<div class="post-metadata">

**Author:** ![ads](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@ads](https://discuss.elastic.co/u/ads)\
**Post date:** [February 26, 2020, 7:07am UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/3 "2020-02-26T07:07:38Z")

</div>

Thanks for the quick reply. Unfortunately that didn't change the output, is there a way to get logstash to print the variables so that I can see what is being matched?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 26, 2020, 2:36pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/4 "2020-02-26T14:36:02Z")

</div>

If you add

```
output { stdout { codec => rubydebug } }

```

then logstash will print the events to stdout and you will be able to see what the fields look like.

---

<div class="post-metadata">

**Author:** ![ads](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@ads](https://discuss.elastic.co/u/ads)\
**Post date:** [February 29, 2020, 6:14pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/5 "2020-02-29T18:14:59Z")

</div>

After a number of iterations I realised that I incorrectly formatted the yaml file. It requires a space after the colon. After modifying, the lat / long coordinates are now being populated.

Thanks for your help

---

<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:** [March 28, 2020, 6:15pm UTC](https://discuss.elastic.co/t/logstash-filter-to-match-jdbc-identifier-with-yaml-file/220921/6 "2020-03-28T18:15:09Z")

</div>

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