Metric visualization of aggregated, then filtered, data

I have an index of documents each representing a login event, from a particular device (deviceId) at a particular time (loginTime).

I want to create a Metric Visualization showing how many devices have been logged in from, which I can easily do as a Unique Count of deviceId. But I want that Metric to be filterable based on when the devices were first logged in from (i.e., activated). So for example, I want to see how many devices were first activated during the month of July, 2020.

If I simply filter the underlying index based on the loginTime, it will first limit my documents to those from July, 2020, and then give me a count, even if a particular device also had a login/document from June, 2020, and should not have been included.

What I want to do is:

  1. First aggregate my documents to give me the minimum loginTime for each device
  2. Then apply the Kibana Dashboard Time Filter to those results, based on the aggregated minimum loginTime
  3. Then get a total count of the matching devices and display it in my dashboard

I'm guessing that, if this is at all possible, it will probably involve using Vega. But I'm not quite sure how to put it all together. Any guidance will be appreciated.

I'm using Kibana 7.4.2.

hi @Reuven_Weiser

this seems partly a Kibana question, but also an Elasticsearch one.

The issue it seems is that you need to issue at least two elasticsearch queries in sequence. One to determine min login-time per device (1). Then somehow have the global time-filter apply to those results (running a total-agg on just those devices in the frame). ((2) and (3)).

I am not sure how to that with Vega, as you would only be able to issue a single ES-query.

Is there a way you can create a new index/index-pattern for (1) (e.g. using a data-transform

Then you can simply create an agg that will wrap (2) and (3).

YMMV, there may be other/better approaches.

@thomasneirynck - yes, if all else fails, that was going to be my approach (a new index with just one entry per device, with the first loginTime). After that, the Metrics Visualization would be easy. Was hoping there was a simpler way. Thanks.

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