Logstash filter to match jdbc identifier with yaml file

[ads (Adam)]

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.

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

[Badger]

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

[ads (Adam)]

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?

[Badger]

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.

[ads (Adam)]

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