Comparing two date fields in Kibana

Elastic Stack Kibana
1

Hi. I would like to be able to use Kibana's Discover functionality to compare two fields, something like date1 LT date2.

Does this sound possible? I have searched for the correct syntax with no luck so far. Both fields are date types in ES.

Thanks

Ollie

2

Do you want to do a direct comparison on the field values or do a range query?

3

Thanks Mark.

I want to do a direct comparison between the two fields. ie find data in the index where date1 is less than date2.

Does that make sense?

All the best.

Ollie

4

Hi all.

Just checking to see if anyone has a solution to this? In a relational system I would do something in a query like WHERE date1 GT date2
thus comparing data from the two columns. Is something like this an option in Lucene or ES?

Thanks

5

Feels like I should be able to simplify it , but something like this should work:

{"constant_score":{"filter":{"script" : { "script" : "doc['date1'].value > doc['date2'].value"}}}}

You can use limited query DSL directly in the search bar.

6

That's great Pieter, thanks for your reply.

I'll try to make that work in the Kibana search bar.

Regards.

Ollie

7

I tested it in my Kibana , although my test fields were numbers - but I'm sure it will work with dates.

One thing to note though is that you'll need to have groovy scripting enabled on your elasticsearch for it to work.
I dont think it Is by default - sorry, for got about that.

Just edit your /etc/elasticsearch/elasticsearch.yml file and
add add script.disable_dynamic: true to the bottom. Or change from false to true if it already exists.

Of course enabling dynamic scripting does have some security (amongst other) implications... so consider this scripting page.

8

Thanks Pietar.

That explains the errors I was getting. We'll make that change and I'll report back.

Regards.

Ollie

9

Hi Pietar.

Having thought about the security implications we would like to investigate getting this to work in the Kibana search bar first. Am I correct in thinking that we would not have to allow scripting in that case? Also, and I have researched this far and wide and found nothing to help, could someone please provide an example of the syntax one would use to compare two fields in the Kibana search bar? I have looked at the query string query documentation but found no obvious solutions.

Many thanks all.

Ollie.

10

Of course having posted the previous message we then immediately decided that allowing scripting would be OK so having enabled it this syntax now works in the Kibana search bar:

{"constant_score":{"filter": {"script": {"script": "doc['REPORTED_ON'].value > doc['DUE_DATE'].value"}}}}

Can anyone suggest how I can also filter out documents where DUE_DATE is not null?

Thanks

Ollie

11

Hi all.

We are still trying to get the code

{"constant_score":{"filter": {"script": {"script": "doc['REPORTED_ON'].value > doc['DUE_DATE'].value"}}}}

to filter out where DUE_DATE is not null. I was wondering if anyone had a solution?

Many thanks

Ollie