Problem with line chart

[gve]

Hello,

I've integrated in Kibana datas from an SQL query.

I can create pie charts, metrics, data tables without any problem, but I can not create line charts.

I'va declared in my index pattern the field "last modified" as time-field and now, it is the only field who can display correct line charts.
Others date field ("create" "close") can not be used to create line charts...

Do you have any idea where is my mistake ?
For information, mapping in the index is an automatic mapping.

[gve]

I made some screenshot to explain more clearly than my poor english

So, this is my time-field in my index pattern

And this is an other "date" field, with the same proprieties

When I configure a line chart whith the first field as x-axis value, result is ok.

But when I want use the other "date" field, result is blank (and I don't see the "auto" value in the interval field)

Any idea ?

As I said, I used automatic mapping. Is it a problem ?

[shaunak (Shaunak Kashyap)]

Hi @gve,

Thanks for the screenshots — those are always helpful! What you are trying to accomplish should be possible. A couple of questions/requests:

1. What version of Kibana are you using?

2. On the right hand side of the visualization editor towards the bottom, you will see a little ^ icon. If you click that, a "spy" panel will pop up. This panel is useful for debugging. In this panel there are five tabs. Please click on the "Request" tab and paste its contents here, then please do the same with the "Response" tab.

Thanks,

Shaunak

[gve]

Hi shaunak,

Thank you for your interest.

I'm using the last version (5.1.1) of Elasticsearch, Kibana et Logstash.

Here is the result of the Request tab :

{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "*",
            "analyze_wildcard": true
          }
        },
        {
          "range": {
            "dernier update": {
              "gte": 1467323564738,
              "lte": 1483138364738,
              "format": "epoch_millis"
            }
          }
        }
      ],
      "must_not": []
    }
  },
  "_source": {
    "excludes": []
  },
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "creation",
        "interval": "1M",
        "time_zone": "Europe/Paris",
        "min_doc_count": 1
      }
    }
  }
}

And for the Response tab :

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 719,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "2": {
      "buckets": [
        {
          "key_as_string": "2015-03-01T00:00:00.000+01:00",
          "key": 1425164400000,
          "doc_count": 1
        },
        {
          "key_as_string": "2016-02-01T00:00:00.000+01:00",
          "key": 1454281200000,
          "doc_count": 1
        },
        {
          "key_as_string": "2016-04-01T00:00:00.000+02:00",
          "key": 1459461600000,
          "doc_count": 5
        },
        {
          "key_as_string": "2016-05-01T00:00:00.000+02:00",
          "key": 1462053600000,
          "doc_count": 6
        },
        {
          "key_as_string": "2016-06-01T00:00:00.000+02:00",
          "key": 1464732000000,
          "doc_count": 30
        },
        {
          "key_as_string": "2016-07-01T00:00:00.000+02:00",
          "key": 1467324000000,
          "doc_count": 88
        },
        {
          "key_as_string": "2016-08-01T00:00:00.000+02:00",
          "key": 1470002400000,
          "doc_count": 85
        },
        {
          "key_as_string": "2016-09-01T00:00:00.000+02:00",
          "key": 1472680800000,
          "doc_count": 151
        },
        {
          "key_as_string": "2016-10-01T00:00:00.000+02:00",
          "key": 1475272800000,
          "doc_count": 129
        },
        {
          "key_as_string": "2016-11-01T00:00:00.000+01:00",
          "key": 1477954800000,
          "doc_count": 114
        },
        {
          "key_as_string": "2016-12-01T00:00:00.000+01:00",
          "key": 1480546800000,
          "doc_count": 109
        }
      ]
    }
  },
  "status": 200
}

There is too many response... I asked a graph for the last 6 months.
Values from the 6 last months seem to be good, but older are not "normal" values (I have to check that, but less that 10 is not a normal result.

[gve]

Just for confirmation, here is a line graph made with own script :

As you ca see, data for December is good, November one is abnormal, October to July are OK, and others are just totally inconsistents

[gve]

Ok, I check the data in discovery.

If I filter just for the lasts 6 months, I have actually 1 entry in March because the filter of last 6 months is applied to the "dernier update" field... but I want filter the last 6 months to the "creation" field.

This is a problem.
How can I told Kibana, when I have several data field wich one is the good one to filter by date ?

[shaunak (Shaunak Kashyap)]

You can use the filter bar at the top of Kibana:

[gve]

Whoaw... I've got a lot of thinks to discover about filters and query !

However, problem is still there, because my request still have a reference to the "dernier update" field

  "must": [
    {
      "range": {
        "creation": {
          "gte": "2016-01-01",
          "lte": "2017-01-01"
        }
      }
    },
    {
      "range": {
        "dernier update": {
          "gte": 1467265892872,
          "lte": 1483167092872,
          "format": "epoch_millis"
        }
      }
    }
  ],

I'm going to create differents index to be able quickly to have my graph, because it's look like it is impossible for Kibana to work on 2 differents time-field in a same index.

[shaunak (Shaunak Kashyap)]

I'm going to create differents index to be able quickly to have my graph, because it's look like it is impossible for Kibana to work on 2 differents time-field in a same index.

Yeah, at this point I would also suggest something along these lines: create two separate index patterns. Note that you are not required to create a time-based index pattern. If you don't create a time-based index pattern, you won't have to specify a time field, which means you could use different time fields in different visualizations without any "interference" from the index pattern's time field. Of course, in such a case (no time field on index pattern) there will be no time picker displayed in the top right of Kibana either — just something to keep in mind.

[gve]

Thank you for your answer.
For the moment, I created 2 differents index. I'll try with an index pattern without time-field to check that also.