# Need to populate geo location based on US State name column using logstash,es and kibi **URL:** \ **Category:** Logstash\ **Created:** [March 15, 2017, 7:21pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755 "2017-03-15T19:21:26Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 15, 2017, 7:21pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/1 "2017-03-15T19:21:26Z")
I have a field with the name of USA states, I need to add another field which should have the geo location for that state. So, that I can use the Tile map in Kibi based on these newly generated Geo location. My Logstash file looks like this... input { jdbc { jdbc\_driver\_library =\> "My\_path\ojdbc6.jar" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "My\_string" jdbc\_user =\> "myuser" jdbc\_password =\> "pwd" statement =\> "SELECT state\_name from table1" type =\> "string" schedule =\> "\* \* \* \* \*" } } filter { mutate { convert =\> { "state\_name" =\> "string" } } } output { elasticsearch { hosts =\> "[http://localhost:9220](http://localhost:9220)" action =\> "index" index =\> "logstash-testxx" workers =\> 1 template =\> "my\_path\elasticsearch-template-es2x.json" manage\_template =\> true template\_overwrite =\> true } stdout {} } ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [March 15, 2017, 7:42pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/2 "2017-03-15T19:42:43Z")
Construct a text file (e.g. a CSV) that maps state names to lat/lon values, then use the translate filter to look up the state name field. ---
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 15, 2017, 8:47pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/3 "2017-03-15T20:47:29Z")
The issue still persists. However, I tried to use the 'if else' condition to manually hardcode the 'lat and long' position for each state in the config file. It adds a new column but doesn't recognizes it as a geo\_point column. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [March 15, 2017, 8:53pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/4 "2017-03-15T20:53:39Z")
ES needs to be configured via an index template to map a particular field as geo\_point. Logstash's default index template contains one geo\_point field, so either use that field name or hack the index template to suit your needs. ---
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 16, 2017, 2:04pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/5 "2017-03-16T14:04:18Z")
I tried using the field location but still not able to find the solution... I just need to add lat and lang position in a newly added field based on the existing "clnt\_nm" so that KIBI can recognize those newly added fields as GEO\_POINT fields....... here is the copy of my logstash config file input { jdbc { jdbc\_driver\_library =\> "my\_pathforojdbc6.jar" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "jdbc:oracle:thin:@mystring" jdbc\_user =\> "xxxx" jdbc\_password =\> "xxx" statement =\> "SELECT clnt\_nm from table1" type =\> "string" schedule =\> "\* \* \* \* \*" } } filter { if [clnt\_nm] == "CA" { mutate { add\_field =\> { "location" =\> "california" } } } else { mutate { add\_field =\> { "location" =\> " " } } } if [client\_name] == "CA" { mutate { add\_field =\> { "lat" =\> "36.7783" } } } else { mutate { add\_field =\> { "lat" =\> "0" } } } } mutate { convert =\> { "lat" =\> "float" "CLNT\_NM" =\> "string" } } mutate { rename =\> {"lat" =\> "[location][lat]"} } } output { elasticsearch { hosts =\> "[http://localhost:9220](http://localhost:9220)" action =\> "index" index =\> "logstash-testing\_4" workers =\> 1 template =\> "my\_templatefile" manage\_template =\> true template\_overwrite =\> true } stdout {} } ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [March 17, 2017, 6:25am UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/6 "2017-03-17T06:25:35Z")
What does the resulting document look like in ES? Copy/paste from the JSON tab in Kibana's Discover view (or equivalent; I want to see the raw JSON document). Also, what do the mappings for your index look like? Use ES's get mapping API. ---
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 27, 2017, 3:52pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/7 "2017-03-27T15:52:54Z")
> [@magnusbaeck](#): > > Copy/paste from the JSON tab in Kibana's Discover Are you talking about this ? { "index": "logstash-testing\_4\*", "query": { "query\_string": { "analyze\_wildcard": true, "query": "_" } }, "filter": [], "highlight": { "pre\_tags": [ "@kibana-highlighted-field@" ], "post\_tags": [ "@/kibana-highlighted-field@" ], "fields": { "_": {} }, "require\_field\_match": false, "fragment\_size": 2147483647 } } ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [March 28, 2017, 5:26am UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/8 "2017-03-28T05:26:50Z")
No, that's the query. I'm talking about the document itself. What are you sending to ES? ---
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 28, 2017, 1:37pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/9 "2017-03-28T13:37:42Z")
Here is the .json template file { "template" : "_", "settings" : { "index.refresh\_interval" : "5s" }, "mappings" : { "default" : { "\_all" : {"enabled" : true, "omit\_norms" : true}, "dynamic\_templates" : [ { "message\_field" : { "path\_match" : "message", "match\_mapping\_type" : "string", "mapping" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "fielddata" : { "format" : "disabled" } } } }, { "string\_fields" : { "match" : "_", "match\_mapping\_type" : "string", "mapping" : { "type" : "string", "index" : "not\_analyzed", "omit\_norms" : true, "fielddata" : { "format" : "disabled" }, "fields" : { "raw" : {"type": "string", "index" : "not\_analyzed", "doc\_values" : true, "ignore\_above" : 256} } } } }, { "float\_fields" : { "match" : "_", "match\_mapping\_type" : "float", "mapping" : { "type" : "float", "doc\_values" : true } } }, { "double\_fields" : { "match" : "_", "match\_mapping\_type" : "double", "mapping" : { "type" : "double", "doc\_values" : true } } }, { "byte\_fields" : { "match" : "_", "match\_mapping\_type" : "byte", "mapping" : { "type" : "byte", "doc\_values" : true } } }, { "short\_fields" : { "match" : "_", "match\_mapping\_type" : "short", "mapping" : { "type" : "short", "doc\_values" : true } } }, { "integer\_fields" : { "match" : "_", "match\_mapping\_type" : "integer", "mapping" : { "type" : "integer", "doc\_values" : true } } }, { "long\_fields" : { "match" : "_", "match\_mapping\_type" : "long", "mapping" : { "type" : "long", "doc\_values" : true } } }, { "date\_fields" : { "match" : "_", "match\_mapping\_type" : "date", "mapping" : { "type" : "date", "doc\_values" : true } } }, { "geo\_point\_fields" : { "match" : "_", "match\_mapping\_type" : "geo\_point", "mapping" : { "type" : "geo\_point", "doc\_values" : true } } } ], "properties" : { "@timestamp": { "type": "date", "doc\_values" : true }, "@version": { "type": "string", "index": "not\_analyzed", "doc\_values" : true }, "geoip" : { "type" : "object", "dynamic": true, "properties" : { "ip": { "type": "ip", "doc\_values" : true }, "location" : { "type" : "geo\_point", "doc\_values" : true }, "latitude" : { "type" : "float", "doc\_values" : true }, "longitude" : { "type" : "float", "doc\_values" : true } } } } } } } ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [March 28, 2017, 2:19pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/10 "2017-03-28T14:19:19Z")
No, that's not what I'm asking for! I want to see the log events the Logstash is sending based on what it's fetching from the database. ---
**Author:** ![navtej.billing](https://avatars.discourse-cdn.com/v4/letter/n/a9a28c/32.png) [@navtej.billing](https://discuss.elastic.co/u/navtej.billing)\ **Post date:** [March 28, 2017, 2:45pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/11 "2017-03-28T14:45:35Z")
input { jdbc { jdbc\_driver\_library =\> "C:\oracle\product\11.2.0\client\_64\ojdbc6.jar" jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "jdbc:oracle:thin:@xxxxxx:0000/xxxxxx" jdbc\_user =\> "ssss" jdbc\_password =\> "xxxxxxxx" statement =\> "SELECT col\_1,col\_2,col\_3 from table\_1 " schedule =\> "\* \* \* \* \*" } } filter { mutate { convert =\> { "col\_1" =\> "integer" } } date { match =\> ["CREATE\_TS", "YYYY-MM-dd HH:mm:ss.SSS-SS ", "YYYY-MM-dd HH:mm:ss.SS-SS ", "YYYY-MM-dd HH:mm:ss.S-SS "] target =\> "ttimestamp" } } output { elasticsearch { hosts =\> "[http://localhost:9220](http://localhost:9220)" action =\> "index" index =\> "logstash-testing" workers =\> 1 template =\> "C:\logstash-5.2.0\vendor\bundle\jruby\1.9\gems\logstash-output-elasticsearch-6.2.4-java\lib\logstash\outputs\elasticsearch\elasticsearch-template-es2x.json" manage\_template =\> true template\_overwrite =\> true } stdout {} } I am not getting what is that you are requesting for, can you please specify the file format you are looking. Like a .json file of a .conf file ? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 25, 2017, 2:46pm UTC](https://discuss.elastic.co/t/need-to-populate-geo-location-based-on-us-state-name-column-using-logstash-es-and-kibi/78755/12 "2017-04-25T14:46:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.