MySQL string field to ip using Logstash

[RRSR (Raj Rajeshwar Singh Rathore)]

Hi,

I am using Logstash to create ElasticSearch index from MySQL data using jdbc plugin. There a field named ip in my MySQL which in ES index is coming as String. I want this field to be of datatype "ip".

logstash.conf :

input {
  jdbc { 
    jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb"
    jdbc_user => "root"
    jdbc_password => "secret"
    jdbc_driver_library => "C:/apps/mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    statement => "sleect * from abc"
    id => "my_id"
  }
}

output {
  stdout {codec => rubydebug}
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "mss_ddos"
    document_id => "%{my_id}"
    action => index
  } 
}

And my index is having _source as :

"ip": "1.1.1.1",
"start_time": "2018-05-05T12:00:51.000Z",
"@version": "1",
"end_time": "2018-05-05T12:00:51.000Z",
"@timestamp": "2018-05-07T10:11:10.797Z",

[magnusbaeck (Magnus Bäck)]

Update your index template so that ip is mapped as an IP address.

[RRSR (Raj Rajeshwar Singh Rathore)]

@magnusbaeck - I don't have any template defined. Is creating a template a must for such a change?

[magnusbaeck (Magnus Bäck)]

Technically you can include the desired mappings in an explicit index creation request or add a mapping of the field after the index creation (assuming the field hasn't already been mapped), but using an index template is recommended.

[RRSR (Raj Rajeshwar Singh Rathore)]

@magnusbaeck - I have created a template :

PUT _template/ddos_template
{
  "index_patterns": ["test_index"],
  "mappings": {
    "doc": {
      "_source": {
        "enabled": true
      },
      "properties": {
        "ip": {
          "type": "ip"
        }
      }
    }
  }
}

And created the index from scratch but still the type of ip field is coming as string rather than ip :

[RRSR (Raj Rajeshwar Singh Rathore)]

Actually, that worked by using the template approach but as I was checking the changes in an old Kibana index hence the changes were not reflected there.
Once I deleted the old there also and created a new index in Kibana too with the new ES index the datatype came as ip only.