Kibana TSVB - Accessing Last Bucket' Timestamp

I'm attempting to forecast warranty renewal costs for hardware using a TSVB on Kibana 6.5.1.

My use case is to provide a dashboard where someone can select the end-date (start date should be today), and depending on when they select as the end-date, they'll see the cost to renew warranties until that date.

I basically just need a way to get the difference in time between some arbitrary date in the future and a time field in ES. Once I have the time difference per doc (or average), I can convert that to a monetary cost.

My current approach is to take the timestamp of the last bucket and then subtract the warranty-end timestamp for each document from that, however I'm unsure of how to access the timestamp of the last bucket in a TSVB, or elsewhere for that matter.

What I've tried:

Subtracting warranty end date from end of bucket timestamp

  • Summing the positive differences in time (end of time-scope > warranty end date) and multiplying that by a known monetary cost per day:

sum(((clamp(max(params._timestamp) - mean(params._all.devices.timestamps), 0, 1000000000000000000) ) / (1000 * 86400 * 30.4375) ) * params.devices * 10)

Fails

Because params._timestamp returns the end timestamp of the bucket containing the document, therefore the math is all based on the bucket interval. E.g. in the screenshot above, the interval auto-adjusted to 7 days. So the costs shown above are the cost to renew expiring warranties when they expire, but only renew the warranty until the end of the bucket (at most, 7 days).

I've also tried to obtain the maximum value for params._timestamp using max(params._timestamp) and subsequently aggregate the 'Overall Maximum' of this Math aggregation, however it appears that Math aggregations can not be an input to another aggregation:

I know this is kind of a non-standard use case of Kibana, but if I can get it to work, it unlocks a whole bunch of other forecasting I'd like to do..

What Works (Kind of):

Manual interval update to force all documents to share the same bucket (& thus the same params._timestamp)

I can manually update the interval to the entire range, but that's not something I can reasonably expect another user to do. This appears to give me the correct results, but I lose the ability for users to generate their own queries:

Does anyone have any links to docs that may help me figure this out?

Bump

@Marius_Dragomir can I get your eyes here please?

Thanks
Bhavya

1 Like

Interesting problem you're trying to solve and there isn't a straight forward solution for this.
Could you expect an user to change the value of a scripted field which stores the end date? (https://www.elastic.co/guide/en/kibana/current/scripted-fields.html) This way you can easily do the diff and your calculations using that scripted field value.

If not, i'm trying to see if you can calculate the cost for the warranties per bucket, until the end of the bucket and then just do a sum of them, but I'm not sure how your data is formatted. If using just ES, a Moving Function Aggregation should be just what you need, but that's not available in TSVB yet. :frowning:
Do you have a sample dummy document so that I can try to work out something?

1 Like

I provided a few dummy documents in a DM to you @Marius_Dragomir.

Unfortunately, the target audience of this tool can't reasonably be expected to update the scripted field value, as they just want a pretty dashboard with buttons :slight_smile:

Please let me know if you need any additional information. I'm looking forward to seeing if there is a potential solution already available within the Kibana framework.