Urgent - Problem when parsing date

soufiane1 · July 2, 2019, 9:28am

Hello everyone,

I am trying to load a csv file which contains a date field where data is sorted like the following example (01/07/2019 14:11), but receiving this elasticsearch error,

"error"=>{"type"=>"mapper_parsing_exception", "reason"=>"failed to parse field [Date et heure] of type [date] in document with id 'NmPZsWsBNbHi5-N-Z-Sm'", "caused_by"=>{"type"=>"date_time_exception", "reason"=>"Invalid value for MonthOfYear (valid values 1 - 12): 30"}}}}}

Here is my config :

filter
{
	csv
	{
		separator => ","
		
		columns => ["Date et heure","Utilisateur","Code","Libelle evenement","Code retour","Application","Code site","Type de table","Objet Start","Usage cache","Valeur avant modif","Valeur apres modif","SDT"]
	}

		
	mutate{
	
	
		convert => { 
			
			"Utilisateur" => "string" 
			"Code" => "integer" 
			"Libellé évènement" => "string" 
			"Code retour" => "string" 
			"Application" => "string" 
			"Code site" => "string" 
			"Type de table" => "string"
			"Objet Start" => "string" 
			"Usage cache" => "string" 						
			"Valeur avant modif" => "string" 
			"Valeur après modif" => "string"
			"SDT" => "string"
		
		}
		
		
		#Gestion des accents
		rename => { "Libelle evenement" => "Libellé évènement"  
					"Valeur apres modif" => "Valeur après modif" }
}

I have defined the same date format in the mapping ( dd/MM/YYYY HH:mm)

dadoonet · July 2, 2019, 10:58am

What is the exact mapping of the index? Not the one you defined but the one you can get with:

GET indexname/_mapping

In the output part can you add a stdout output plugin so we can read what exactly is sent to elasticsearch?

soufiane1 · July 2, 2019, 11:04am

this is what I get :

{
  "monbeaunode_1" : {
    "mappings" : {
      "properties" : {
        "@timestamp" : {
          "type" : "date"
        },
        "Application" : {
          "type" : "keyword"
        },
        "Code" : {
          "type" : "keyword"
        },
        "Code retour" : {
          "type" : "keyword"
        },
        "Code site" : {
          "type" : "keyword"
        },
        "Date et heure" : {
          "type" : "date",
          "format" : "DD/MM/YYYY HH:mm"
        },
        "Libellé évènement" : {
          "type" : "keyword"
        },
        "Objet Start" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "Objet start" : {
          "type" : "keyword"
        },
        "SDT" : {
          "type" : "keyword",
          "null_value" : "-"
        },
        "Type de table" : {
          "type" : "keyword"
        },
        "Usage cache" : {
          "type" : "keyword"
        },
        "Utilisateur" : {
          "type" : "keyword"
        },
        "Valeur après modif" : {
          "type" : "keyword"
        },
        "Valeur avant modif" : {
          "type" : "keyword",
          "null_value" : "NULL"
        }
      }
    }
  }
}

And here is my entire config :

###############################################################################################################

input
{
	file
	{
		path => "C:/Users/BEKRISO/KIBANA7.0.1/INPUT/9r_piste_audit.csv"
		start_position => "beginning"
		sincedb_path => "C:/Users/BEKRISO/KIBANA7.0.1/sincedb"
		codec => plain{charset =>"UTF-8"}
	}
}

############################################################################################################################

filter
{
	csv
	{
		separator => ","
		
		columns => ["Date et heure","Utilisateur","Code","Libelle evenement","Code retour","Application","Code site","Type de table","Objet Start","Usage cache","Valeur avant modif","Valeur apres modif","SDT"]
	}

		
	mutate{
	
	
		convert => { 
			
			"Utilisateur" => "string" 
			"Code" => "integer" 
			"Libellé évènement" => "string" 
			"Code retour" => "string" 
			"Application" => "string" 
			"Code site" => "string" 
			"Type de table" => "string"
			"Objet Start" => "string" 
			"Usage cache" => "string" 						
			"Valeur avant modif" => "string" 
			"Valeur après modif" => "string"
			"SDT" => "string"
		
		}
		
		
		#Gestion des accents
		rename => { "Libelle evenement" => "Libellé évènement"  
					"Valeur apres modif" => "Valeur après modif" }
					
		#suppression des champs inutiles  
		remove_field => [ "message", "host", "path","@version", "@timestamp"]
		
		#Suppression des carriage returns(\r) du dernier champs
		gsub => [ "message", "[\r]", "" ]	
			
	
	}
	
	date {  match => [ "Date et heure", "DD/MM/YYYY HH:mm" ] }
	 
		#Remplacement des champs vide("NULL") par V1 car dans ESHMA V1 la version n'etait pas precisée
		#gsub => [ "Valeur avant modif", "NULL", "V1" ]	
		
		#if [Valeur avant modif] == "NULL" {	mutate {	replace => [ "[Valeur avant modif]" , "V1" ]	}	}	
				
}

##############################################################################################################################

output
{
	stdout { codec => rubydebug }
	
	elasticsearch
	{
		hosts => "cas0000658713:9200"
		index => "monbeaunode_1"
	}

}

Kibana display "19/07/1970 01:11" for "19/07/2019 01:11", so it seems like it doesn't receive the year value,

dadoonet · July 2, 2019, 11:38am

What is the output that you are getting with the stdout plugin? Could you share it please?

Also, I did not notice first but adding Urgent in the title does not bring any value or specific attention to your thread. Please don't do this.
Specifically I don't think that your problem is more urgent than an issue with a production cluster which is down for 2 days for example...

soufiane1 · July 2, 2019, 11:55am

dadoonet · July 2, 2019, 12:22pm

Please don't post images of text as they are hardly readable and not searchable.

Instead paste the text and format it with </> icon. Check the preview window.

soufiane1 · July 2, 2019, 1:44pm

{
           "Utilisateur" => nil,
         "Type de table" => nil,
           "Code retour" => "05",
         "Date et heure" => "04/09/2018 12:53",
           "Application" => "9R",
           "Objet Start" => "RB-ZZZZ-0",
           "Usage cache" => "NON",
    "Valeur avant modif" => "V1",
                   "SDT" => nil,
    "Valeur après modif" => nil,
                  "Code" => 1,
             "Code site" => "0990",
     "Libellé évènement" => "Appel à la passerelle par une application cliente",
            "@timestamp" => 2018-01-04T11:53:00.000Z
}
{
           "Utilisateur" => nil,
         "Type de table" => nil,
           "Code retour" => "00",
         "Date et heure" => "04/09/2018 12:53",
           "Application" => "9R",
           "Objet Start" => "RB-FI05-0",
           "Usage cache" => "NON",
    "Valeur avant modif" => "V1",
                   "SDT" => nil,
    "Valeur après modif" => nil,
                  "Code" => 1,
             "Code site" => "0990",
     "Libellé évènement" => "Appel à la passerelle par une application cliente",
            "@timestamp" => 2018-01-04T11:53:00.000Z
}
{
           "Utilisateur" => nil,
         "Type de table" => "AS",
           "Code retour" => "00",
         "Date et heure" => "04/09/2018 12:52",
           "Application" => "9R",
           "Objet Start" => "TA-FI59-0",
           "Usage cache" => "NON",
    "Valeur avant modif" => "V1",
                   "SDT" => nil,
    "Valeur après modif" => nil,
                  "Code" => 1,
             "Code site" => "0990",
     "Libellé évènement" => "Appel à la passerelle par une application cliente",
            "@timestamp" => 2018-01-04T11:52:00.000Z
}
{
           "Utilisateur" => nil,
         "Type de table" => nil,
           "Code retour" => "02",
         "Date et heure" => "04/09/2018 08:04",
           "Application" => "TEST_ESHMA",
           "Objet Start" => "RB-NM252-0",
           "Usage cache" => "NON",
    "Valeur avant modif" => "V1",
                   "SDT" => nil,
    "Valeur après modif" => nil,
                  "Code" => 1,
             "Code site" => "0990",
     "Libellé évènement" => "Appel à la passerelle par une application cliente",
            "@timestamp" => 2018-01-04T07:04:00.000Z
}

dadoonet · July 2, 2019, 3:41pm

But one of the documents is generating a value 30 for the month. Could you share the document which is related to that? I can't reproduce any problem without the document which is having problems...

I guess you are using Elasticsearch 7.x, right?

system · July 30, 2019, 3:41pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Date Time Issue with Index Elasticsearch	6	2739	June 15, 2020
Failed to parse date field, date_time_parse_exception Elasticsearch	7	2558	February 17, 2021
ElasticSearch Date Mapping Issue Elasticsearch	2	404	July 5, 2017
Unable to parse Date Elasticsearch	1	672	July 5, 2017
Date Mapping Parse Failure Elasticsearch	3	2499	December 23, 2020

Urgent - Problem when parsing date

Related topics