How sort by date/time on Kibana Discover

Below is an example of what I am seeing:

I dont' see the "time" choice as shown in this thread:

I'd like to sort by descending date/time ]the field createdDate (even by default if possible).

It's one of the fields in every message:
Kibana_Sort_Fields

Thanks,
Neal

Each of your fields is a regular text type. There might be a way to sort with those, I don't know.
But I would rather recommend mapping your data into more usable fields. That is, eg. mapping the createdDate data into a date type field.

Furthermore, if you tell Kibana that the createdDate field is the time field when creating an index, it will automatically sort by that, you won't have to do anything else.

ELK becomes really powerful when you properly map your data into fields.

Thanks; I'm calling a C# routine someone else wrote as a commonly used library by our middleware team. We basically stuff message in ElasticSearch rather than writing to file system. Other people here may know better how to search, but I'm still new.

I'm not sure how createdDate is being populated. So far I assumed it must be automatic, but I did notice that didn't have the underscore in front of it.

This is basically our code (I just omiitted the connection info and the try/catch):

             // using Eleasticsearch.Net; library 
             var shared = new ElasticClient(config);
             var index = new StringBuilder();
             // Make lower case, prefix with environment, and add datetime on end of index name. 
             index.Append(environment.ToLower());
             index.Append("-").Append(indexType).Append("-");
             index.Append(DateTime.Now.ToString("yyyy.MM.dd"));
           
             if (!shared.IndexExists(index.ToString().ToLower()).Exists)
             {
                 CreateIndexRequest request = new CreateIndexRequest(index.ToString().ToLower());
                 TypeMapping typeMapping = new TypeMapping();
                 typeMapping.DateDetection = false;
                 request.Mappings = new Mappings();
                 request.Mappings.Add("object", typeMapping);
                 shared.CreateIndex(request);
             }
             
             var result = shared.Index(message, idx => idx.Index(index.ToString().ToLower()));

You can see we are not specifying createdDate anywhere in there. So do you have any idea how it gets there?

I've got a few ideas on how we can improve what they do, but first I have to understand the basics.

So to add one more idea; we are storing a variety of messages, in each a different index. Sometimes I pick a specific index on the left side, but most of the time I pick something like local-* or local-project-*. So the display is showing a different messages from different indexes. The messages usually have XML or JSON in them as a big blob field called "message".

Even though it didn't get there, I see createdDate on all of them. I cannot tell in what order the messages are being displayed, but I want to see them in createdDate order.

Mapping datatypes happens in Elasticsearch. Unfortunately I'm not familiar with C#, but that doesn't really matter now. We have the field in ES and it has a format, therefore it can be mapped.
Here's how:

Field datatypes
Date datatype

I'm missing the big picture here. 'createdDate" is showing as a field with a little 't' by it. That means "text" right? It's yyyymmdTHH:MM:SS, so I don't see why I cannot sort by it? Regardless of the language, you can see we didn't put that date there, so it must be a date recorded by ElasticSearch. Why do I have to turn it into a "datatype" to sort on it? Can I not sort on text fields?
Thanks,
Neal

When I look at it in JSON, it looks like this:
{
"_index": "local-atlas-trace-gateway-version-2018.04.04",
"_type": "object",
"_id": "mM3tkWIBQE6KH_QaT66H",
"_version": 1,
"_score": 1,
"_source": {
"message": "Version 1",
"createdDate": "2018-04-04T13:31:09.6853566-05:00",
"transactionType": "atlas-trace-gateway-version"
}
}

All I passed to store this was the message ("Version 1") and the index name: "atlas-trace-gateway-version".

Neal

Probably you can, I never tried until now.

es01

You can add fields from the list left to the result list, then you can sort by them. Maybe that will work with your data. Once you have the field as a column in the results, you can sort by it like on the picture above.
Still I would recommend trying to map createdDate as a date datatype, it would enable a lot of other options (like statistics through visualize).

Maybe text fields cannot be sorted??

Kibana_Column_Headings
When I mouse over, there is an "X" to remove column and a right arrow that says "move column to the right". There is no up/down arrow for sorting.

Grasping at straws, I also tried this:


The error is not exactly crystal clear.

Neal

Check the text datatype, it says:

Text fields are not used for sorting [...]

Keyword type strings can be sorted though, but then you would have to first map the field as keyword.

They are typically used for filtering [...], for sorting, and for aggregations.

The server_name field I used above is a keyword type field.

Ok, I'm still confused who is storing createdDate and why "they' didn't store it as a date. We didn't pass it, so it seems like E.S. is storing it; and I'm boggled why it wasn't stored as a date type.

I tried this:
PUT local-atlas-trace-gateway-version-2018.04.04
{
"mappings": {
"_doc": {
"properties": {
"createdDate": {
"type": "date"
}
}
}
}
}

And got this response:
{
"error": {
"root_cause": [
{
"type": "resource_already_exists_exception",
"reason": "index [local-atlas-trace-gateway-version-2018.04.04/oUgotAyITMGJapsUqsA34A] already exists",
"index_uuid": "oUgotAyITMGJapsUqsA34A",
"index": "local-atlas-trace-gateway-version-2018.04.04"
}
],
"type": "resource_already_exists_exception",
"reason": "index [local-atlas-trace-gateway-version-2018.04.04/oUgotAyITMGJapsUqsA34A] already exists",
"index_uuid": "oUgotAyITMGJapsUqsA34A",
"index": "local-atlas-trace-gateway-version-2018.04.04"
},
"status": 400
}

Cannot I not modify the field on an existing index?

Newbie Neal

You're venturing into an area I myself haven't been before :slight_smile:

As far as I know, no, you can't just alter existing data. When creating new mapping, old data will remain as it was. To get the old data with the new mapping, you would have to load the data (index it) again.
I don't know if it's possible by reindexing, it might be worth checking it out here.

But first, I'd recommend creating an index template. The template ensures that new events (data) will be mapped the way you want them to be. I didn't check but I have a hunch it's not possible to have the same field with two different datatypes, meaning you can't have one createdDate as text and another createdDate as date. But as I said: I didn't check into this (I'm a beginner user myself).

Thanks, I'll be digging deeper. However, I did find we had a wrapper routine around the code I mentioned above; they were named similarly so I got mixed up. The wrapper adds the createdDate and transactionType (which is the index name), and then builds the JSON document.

I learned how to inspect the index by doing the "get indexname". This shows that my "createdDate" is in fact a date time. Not sure why Kibana shows "t" next to it - was guessing t=text and d=date, but not sure.
So it's still a mystery why it doesn't have the up/down arrow to allow sort when you make it a column. Any ideas?

Thanks
Neal

{
  "local-neal-test-kibananew2c-2018.04.06": {
    "aliases": {},
    "mappings": {
      "document2": {
        "properties": {
          "createdDate": {
            "type": "date"
          },
          "message": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "transactionType": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    },
    "settings": {
      "index": {
        "creation_date": "1523026621059",
        "number_of_shards": "5",
        "number_of_replicas": "1",
        "uuid": "3DzKTpm7TYKk_u35pj3eBA",
        "version": {
          "created": "6000099"
        },
        "provided_name": "local-neal-test-kibananew2c-2018.04.06"
      }
    }
  }
}

It should have a clock next to the field.

es02

Well, "I'll be switched" (as they used to say).

If I create an index pattern on "local-neal-test-kibananew4a-", it works.
I was using an index pattern on "local-neal-
" it doesn't, even though all the data is displayed. I presume that's because not all messages in local-neal-* have the exact same type.

When I use the management tab to create the index pattern: local-neal-test-kibananew4a-*
it pops up the "Time Filter field name" box":

Kibana_Create_IndexPatternSpecific

I had never seen that before. I guess I need to read up on what index patterns really do.
I thought Kibana would be super user-friendly, but that are apparently tricks to be learned.

We have naming standards for our indexes. What I was hoping to do was watch the "trace" of my business process across different indexes and be able to get them in date/time sequence, since they all had the same createdDate field. Apparently this won't work??

Neal

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