Geo point for new ECS mapped Geo fields using JDBC

[metalshanked]

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

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]}"
  }

[Badger]

@metalshanked

it did not work

What do you mean by that?

[metalshanked]

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?

[leandrojmp (Leandro Pereira)]

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 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.

[metalshanked]

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!

[leandrojmp (Leandro Pereira)]

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].

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.

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.

[metalshanked]

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