Elasticsearch not populating timestamp / unable to change timestamp filter in kibana visualization

Elastic Stack Elasticsearch
1

I have Elasticsearch / Kibana 7.6.2 running in docker

I originally loaded a new index from a csv file using kibana upload import csv which created the new index

I've created dozens of visualizations on a dashboard which all worked fine

I've cleared the data in the index using _delete_by_query

and reloaded the data from a JSON file using curl myindex/_bulk

That all worked BUT the @timestamp has not populated in my index and so now all the visualizations can't find any data as it seems to be fixed on using the @timestamp as a date filter. I can't create a new visualization as I cannot get the default filter to use my date field (which is populated fine)

I reloaded using

myindex/_bulk
{ "index" : {} }
{"RECORDS" : -27620,"KPI_DATE" : "2020-04-01","KPI_TYPE" : "Net Sales","DEPARTMENT" : "Sales","BRANCH" : "My Company","BRAND" : "My Company","ORDER" : "1110"}
{ "index" : {} }
{"RECORDS" : -20880,"KPI_DATE" : "2020-04-06","KPI_TYPE" : "Net Sales","DEPARTMENT" : "Sales","BRANCH" : "My Company","BRAND" : "My Company","ORDER" : "1110"}
{ "index" : {} }
{"RECORDS" : -3981.5,"KPI_DATE" : "2020-02-21","KPI_TYPE" : "NET +/-","DEPARTMENT" : "Sales","BRANCH" : "My Company","BRAND" : "My Company","ORDER" : "1110"}

The data all imported but the @timestamp has not populated

GET myindex/_search?size=0
{
  "aggs": {
    "min_date": {"min": {"field": "KPI_DATE", "format": "yyyy-MM-dd"}},
    "max_date": {"max": {"field": "KPI_DATE", "format": "yyyy-MM-dd"}},
    "min_timestamp": {"min": {"field": "@timestamp"}},
    "max_timestamp": {"max": {"field": "@timestamp"}}
  }
}

Correctly shows the min / max KPI_DATE but @timestamp is null

  "aggregations" : {
    "max_date" : {
      "value" : 1.5873408E12,
      "value_as_string" : "2020-04-20"
    },
    "min_date" : {
      "value" : 1.4278464E12,
      "value_as_string" : "2015-04-01"
    },
    "max_timestamp" : {
      "value" : null
    },
    "min_timestamp" : {
      "value" : null
    }
  }

I have tried to update the @timestamp using

POST myindex/_update_by_query
{
  "query": {"match_all": {}},
  "script": {
    "source": "ctx._source._timestamp = ctx._source.KPI_DATE"
  }
}

Which returns an error saying the field is a metadata field which I can't update, Field [_timestamp] is a metadata field and cannot be added inside a document. Use the index API request parameters

My mapping looks like this, the date looks fine :

{
  "mapping": {
    "_doc": {
      "_meta": {
        "created_by": "ml-file-data-visualizer"
      },
      "properties": {
        "@timestamp": {
          "type": "date"
        },
        "BRANCH": {
          "type": "keyword"
        },
        "BRAND": {
          "type": "keyword"
        },
        "DEPARTMENT": {
          "type": "keyword"
        },
        "KPI_DATE": {
          "type": "date",
          "format": "iso8601"
        },
        "KPI_TYPE": {
          "type": "keyword"
        },
        "ORDER": {
          "type": "integer"
        },
        "RECORDS": {
          "type": "float"
        }
      }
    }
  }
}

It automatically added the @timestamp which I can't figure out if it's deprecated or not from various things I've been reading trying to fix this over the last few hours!

So how do I proceed?! I'd rather not start from scratch!

I either need to be able to change the kibana default filter away from using @timestamp (I'm sure it set this automatically) or I need to be able to get it to populate the @timestamp field in my index.

I've been searching for several hours now and feel I must be missing something obvious!

Thanks in advance!

2

Ok, partially figured this out .... I can't see how to populate the @timestamp field in my original index BUT I can see the Kibana visualizations use a kibana index pattern which looks at the elasticsearch index ... that specifies the date field

I can create a new kibana index pattern, export my dashboard and visualizations, edit the JSON file to replace the ID of the kibana index pattern (and actually set a static index pattern ID so I can move between dev and prod environments easily) and then import my dashboard and vizualizations which will now look at the new kibana index pattern which looks at my date field rather than the @timestamp field

3

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