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
}
}
}
}
}
}
}
}
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.
The perfect answer. It worked Rob!
Also, thanks a lot for the extra tips for indices :) :)