Finding past due orders in Kibana by subracting one date from another

Elastic Stack Kibana
1

Environment: LS2.0, ES2.0, Kib4.2 and Sh2.0

We are capturing order flow data in an Index complete with time stamps of the major order fulfillment events. We want to identify few key orders that are past due.

I want to compare the two time stamps and if the difference is greater than 2 days (or 48hrs) then I want to show that order line. In my example we have an arrivalTimeStamp and OnBoardingTimestamp; if the difference btween these dates is more than 2 days then the order is past due and we need to see it.

How can I do that in the Query field in Kibana? Please help

I'm attaching an image of what I've tried.

Orders Past due in Kibana.jpg1123×605 111 KB

2

You can't compare fields in the same document in the query field, but you should be able to set up a script field in Kibana that performs the subtraction and creates a "virtual field" in each document. Then you can place a condition on that field.

3

Ok - I got the value computed using the Virtual field. Had to enable dynamic scripting in ES on the ES engine and restart.

4

Now that I have the computed the value that I need in virtual field Diff_Arrival_to_OrderDesk - I still can't query/select by that value in the Query field. I tried Diff_Arrival_to_OrderDesk > 20. Nothing found.

It's a numeric field, how do I query on it?

Diff_Arrival_OrderDesk.png1189×436 31.8 KB

5

The query string syntax for range queries is documented here: https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html#_ranges

6

Documentation mentions this:
count:[10 TO *]

I have been trying this with no success:
Diff_Arrival_to_OrderDesk:[1 TO *]
Diff_Arrival_to_OrderDesk:>1
Diff_Arrival_to_OrderDesk:(>=1 AND <50)

I'm close but I'm missing something.

7

It seems that I can apply the range syntax to other numeric fields that exist in the document, but I can't get it to work on that scripted field that I've added.

Please help me get over this hump.... still reading the DSL Query String documentation but don't understand how to construct the syntax yet.

8

Should the "range" queries work on virtual fields?!

9

Anybody, please?

10

jjdepaul,

Once I did as you did and enabled scripting on my es instance, I was able to accomplish your goal. There were two tricky bits that I had to work through. First, I needed to add a filter to your search in order to add criteria to your scripted field. Second, I needed to make my scripted field a little more complicated in order to account for the undefined onboardingTimestamp values shown in your sample.

I did the following steps:

Create a scripted field:

((doc['orderOnboardTimestamp'].value ? doc['orderOnboardTimestamp'].value : doc['orderArrivalTimestamp'].value + (1000 * 60 * 60 * 24 * 9999)) - doc['orderArrivalTimestamp'].value) / 1000 / 60 / 60 / 24

This calcuates the difference between the onboard and arrival dates. If there is no onboard date, then the arrival date with an offset of 9999 days is used instead.

In discover, add a filter

Find any value in the newly created calculatedShipTime field and add a filter for it.

Edit the filter

  • Hover over the filter, and click on the edit icon (furthest on the right)

Give it a meaningful Filter Alias, and modify the script of the filter

  • Change the Filter Alias to 'Older than two days'
  • Change the value param to 2
  • Modify the comparison operator at the end of the 'script' value from '==' to '>='
  • Click 'Done'

Save the search

1 Like
11

Wonderful writeup - thank you so much - will try asap.

12

This worked Brilliantly, thank you!

13

One other question on this old topic: is there a way to get the current timestamp into this equation to replace the 9999?

14

Another question in this space - is there a way to make this filter select a RANGE of values?! I tried several combinations of (1 TO 20) and [1 TO 20], but no luck

filter range.jpg1071×293 35 KB

15

This is something i need now too. It is very strange to have to do these sort of hacks. Any other explanation why is range filter not working out-of-the-box?

I have scripted field:
(doc['date1']) ? ((doc['date2']) ? doc['date2'].value - doc['date1'].value : 0): 0

And i get integer numbers (differenfce in ms) in discover/visualizations.

But I cannot use this scripted fields in range filter in kibana query (doesn't return nothing):

scripted_field:[1 TO 100]

or any other format.

Is this bug in kibana/ES?

Thanks,
Eddie