# Geo point for new ECS mapped Geo fields using JDBC **URL:** \ **Category:** Logstash\ **Created:** [May 1, 2021, 1:28am UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846 "2021-05-01T01:28:16Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![metalshanked](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/metalshanked/32/87159_2.png) [@metalshanked](https://discuss.elastic.co/u/metalshanked)\ **Post date:** [May 1, 2021, 1:28am UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/1 "2021-05-01T01:28:16Z")
Hello, I am trying to map Geo Lat and Long for internal IPs to ECS geo fields such as [source][geo][location] using logstash. The standard geoip filter works well for external ips and i see it nicely mapped to the ecs geo subfields in the index but i am not able to figure out how to populate the [source][geo][location] ecs field for internal IP from the JDBC connection i setup. Specifically, not sure how to map the lat and and long values from the JDBC obtained table to the ECS geo fields. I tried the below but it did not work ```auto jdbc_static { loaders => [ { id => "remote-servers" query => "select ip, lat,long from ip_enrich" local_table => "servers" } ] local_db_objects => [ { name => "servers" index_columns => ["ip"] columns => [ ["ip", "varchar(50)"], ["lat", "float"], ["long", "float"] ] } ] local_lookups => [ { id => "local-servers" query => "SELECT lat,long FROM servers WHERE ip = :ipfromevent" parameters => { ipfromevent => "[source][ip]" } target => "server" } ] # using add_field here to add & rename values to the event root add_field => { "[source][geo][location]" => "%{[server][0][lat]},%{[server][0][long]}" } ``` ---
**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:** [May 1, 2021, 2:22am UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/2 "2021-05-01T02:22:01Z")
> [@metalshanked](#): > > it did not work What do you mean by that? ---
**Author:** ![metalshanked](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/metalshanked/32/87159_2.png) [@metalshanked](https://discuss.elastic.co/u/metalshanked)\ **Post date:** [May 1, 2021, 5:00am UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/3 "2021-05-01T05:00:14Z")
hehe i knew when i wrote "it did not work" that someone would pick on that. did not work here means it did not give the expected result since i am doing something wrong...here it gave me the literal value --\> "%{[server][0][lat]},%{[server][0][long]}" in the [source][geo][location] field in Elastic. But my main/specific question here is --\> How do i map "lat" and "long" fields from an external source (Eg:- JDBC) to ECS field [source][geo][location] which is already mapped as a Geopoint type in Elastic? ---
**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\ **Post date:** [May 1, 2021, 5:57am UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/4 "2021-05-01T05:57:48Z")
If you are getting the literal value of `%{[server][0][lat]},%{[server][0][long]}` in the field `[source][geo][location]` after your `add_field`, this means that the event didn't have the fields `[server][0][lat]` and `[server][0][long]`. You will need to check which queries are not working right, the one from your remote server or the one in the local lookup. There are [some ways](https://www.elastic.co/guide/en/elasticsearch/reference/current/geo-point.html#geo-point) to map coordinates to a geo\_point field, one of them is what you are already doing, create a string in the format `"lat,lon"`, but in your case the fields you are adding have no value, so you get the literal string. ---
**Author:** ![metalshanked](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/metalshanked/32/87159_2.png) [@metalshanked](https://discuss.elastic.co/u/metalshanked)\ **Post date:** [May 1, 2021, 5:25pm UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/5 "2021-05-01T17:25:20Z")
Thanks @leandrojmp. Yes, you are right there. I see the server array as [] for these events. But all the docs online including the 5 ways to map Geopoint you sent seem to be for the "older" way for doing geopoint? I mean i see the below JSON structure in the [source][geo][location] ECS field for the external IPs getting populated by the official geoip filter. So the basic question i have is do i need to "construct" this JSON to populate this field with the lat and long values for my internal IPs and if so, how? Any sample would be much appreciated! ![image](https://us1.discourse-cdn.com/elastic/original/3X/2/f/2f8625dcbf566e2cc866cb5d836efad0d0aaa087.png) ---
**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\ **Post date:** [May 2, 2021, 5:39pm UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/6 "2021-05-02T17:39:34Z")
Well, it will depends on the mapping you are using. In elasticsearch there are two geo data types, the `geo_point`, which is used in most of the cases, and the `geo_shape`, which is used in some cases to improve geo shape queries. The ECS schema defines that the `geo.location` field has the `geo_point` data type. [[documentation](https://www.elastic.co/guide/en/ecs/current/ecs-geo.html#field-geo-location)]. The example you shared in your screenshot is for a field that has the `geo_shape` data type, it needs to be created in [this format](https://www.elastic.co/guide/en/elasticsearch/reference/current/geo-shape.html#geo-point-type). But from the documentation of the ECS schema, this field is not correctly mapped, `*.geo.location` needs to be a `geo_point`, not a `geo_shape`, I do not know what you used to create this example, but if it was some tool by elastic this could be a mistake or that they are changing the ECS, someone from elastic could answer this better. For example, the mapping the filebeat uses, stills have `source.geo.location` as a `geo_point`. But to answer your question, if in your mapping the field `source.geo.location` is mapped as a `geo_point`, then you are already creating it the correct way. ---
**Author:** ![metalshanked](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/metalshanked/32/87159_2.png) [@metalshanked](https://discuss.elastic.co/u/metalshanked)\ **Post date:** [May 3, 2021, 2:07pm UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/7 "2021-05-03T14:07:33Z")
Thanks for this detailed info, @leandrojmp ! I used the default out of the box settings for the "geoip" filter and the output is elasticsearch with settings "ecs\_compatibility" as v1 with ilm settings turned on. This created all the elastic mappings and template automatically and the started populating the geo fields for source and destination from the "geoip" filter. Not sure if some other setting needs to be changed to have it set a "geopoint" instead of "geoshape" for the source/destination.geo.fields Will tinker more on the "geoShape" type you mentioned and report. Thanks again ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 31, 2021, 2:08pm UTC](https://discuss.elastic.co/t/geo-point-for-new-ecs-mapped-geo-fields-using-jdbc/271846/8 "2021-05-31T14:08:18Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.