Create Visualization - Get Data for last month


The purpose is to create a visualization

Lets assume the following table.

2021-01-14 11_41_26-Cortana

The Input table contains the user name and user login timestamp.
The generated output should be only those users who have NOT logged in the current month.

Could you please suggest on what should be done to make it work in a Kibana Visualization.

That sounds quite complex. I think the only option is to use Vega, unless someone has better idea?

I’d create two queries – one getting all the user names of all times and the other one only from the last month. Then I’d use a bucket_selector pipeline aggregation to filter out the values that are in two buckets. If you decide to follow this path, it’d be easier to firstly use dev tools to make sure the data you receive from Elasticsearch looks correct and then move to Vega to create visualization on top of this data. This is not trivial though so you might wanna reconsider ingesting your data in different format to make it easier if it’s possible. Plus if you have a lot of data, this approach is not performant.

Here's a similar example: Data set difference between fields on different indexes

I think this is a good use case for data transforms in Elasticsearch. You can create a transform that determines for every user whether or not they have logged in.

Just to provide a link: We call this the "latest" use case, the documentation contains an example implementation. This can be used in a continuous transform which will keep a persistent view on the data in a separate index. In addition you need a last seen field. To finally get the desired output you query the transform destination using a range filter, e.g. : last_seen < now() - 30d.

Spoiler alert: We know this is quite complicated to setup at the moment, in a future release transform will provide an easier way for latest.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.