Warm tip: This article is reproduced from stackoverflow.com, please click
c# elasticsearch nest

DateRange search is not working in Elastic search NEST api

发布于 2020-03-27 10:29:15

I have a table of logs records and I want to conduct a simple search by date.

For example, I wanted to search all the queries before 01.06.2019 00:00:00 (mm.DD.yyyy hh:mm:ss) and I wrote this query:

var query = client.Search<SearchEventDto>(s => s
                .AllIndices()
                .AllTypes()
                .Query(q => q
                    .MatchAll() && +q
                    .DateRange(r =>r
                        .Field(f => f.timestamp)
                        .LessThanOrEquals(new DateTime(2019,06,01, 0, 0, 0))
                    )
                )
            ); 

My Dto looks like this:

 public class SearchEventDto : IDto
    {
        [KendoColumn(Hidden = true, Editable = true)]
        public string id { get; set; }

        [KendoColumn(Order = 2, DisplayName = "Level")]
        public string level { get; set; }
        [KendoColumn(Order = 4, DisplayName = "Message")]
        public string message { get; set; }

        [KendoColumn(Hidden = true)]
        public string host { get; set; }
        [KendoColumn(Order = 3, DisplayName = "Source")]
        public string src { get; set; }

        [KendoColumn(Order = 1, DisplayName = "Timestamp", UIType = UIType.DateTime)]
        public DateTime timestamp { get; set; }

        [KendoColumn(Hidden = true)]
        public DateTime time { get; set; }

    }

Unfortunately, it is returning all the records without filtering anything. Where am I going wrong in this?

Thanks in advance!

PS: ES version: 6.7.0, NEST: 6.8

PS: I have integrated the logs with Nlog. So, now every day it inserts a new index with the date as the name. Here is a mapping for 219-06-28 (I am using the @timestamp):

{
    "logstash-2019-06-28": {
        "mappings": {
            "logevent": {
                "properties": {
                    "@timestamp": {
                        "type": "date"
                    },
                    "host": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    },
                    "level": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    },
                    "message": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    },
                    "src": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    },
                    "time": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        }
                    }
                }
            }
        }
    }
}
Questioner
Srijani Ghosh
Viewed
399
Rob 2019-07-03 23:27

I'll post what we have figured out in comments as an answer as I think there are couple things which could be improved to increase performance and readability.

Solution:

Query from the question was using .Field(f => f.timestamp) which was translated by NEST to use timestamp field not @timestamp. Simple change to .Field("@timestamp") would resolve the problem as this is the proper field name in index mapping.

{
    "logstash-2019-06-28": {
        "mappings": {
            "logevent": {
                "properties": {
                    "@timestamp": {
                        "type": "date"
                    },
                    ..
                }
            }
        }
    }
}

We could also mark timestamp property with PropertyName attribute to tell NEST to use @timestamp as a name instead of timestamp

 public class SearchEventDto : IDto
    {
        [KendoColumn(Order = 1, DisplayName = "Timestamp", UIType = UIType.DateTime)]
        [PropertyName("@timestamp")]
        public DateTime timestamp { get; set; }

    }

and query

var query = client.Search<SearchEventDto>(s => s
                .AllIndices()
                .AllTypes()
                .Query(q => q
                    .MatchAll() && +q
                    .DateRange(r =>r
                        .Field(f => f.timestamp)
                        .LessThanOrEquals(new DateTime(2019,06,01, 0, 0, 0))
                    )
                )
            ); 

would be just working as well.

Improvements:

Query only specific indices:

var query = client.Search<SearchEventDto>(s => s
        .AllIndices()
        .AllTypes()
        ..

By using AllIndices() we are telling elasticsearch to try to gather documents from all of the indices, we could change it a little bit to query only indices with logs data:

var query = client.Search<SearchEventDto>(s => s
                .Index("logstash-*")
                .Type("logevent")
                ..

Use filter context for date range filter:

.Query(q => q.Bool(b => b.Filter(f => f.DateRange(..))))

This way your query should be faster as it doesn't care about calculating search relevance score. You can read more about it here.

Hope that helps.