Calculate difference in time between two documents in same bucket on Data Table Visualization

[lmit (Lance Mitchell)]

I am struggling to build a Data Table visualization below that captures the timestamp of a prior doc in the same bucket where a field value changed from the current doc, so that I can show the difference in time.

For example below, show the time difference since the status field changed to UP in the Name field bucket??

Can anyone help point the way?

[wylie (Wylie Conlon)]

You requested a way to "show the time difference since the status field changed to UP". To calculate this, you will need to tell Elasticsearch to compare either several documents or several buckets, both of which have limited support in Kibana and Elasticsearch in general because they aren't using the index in the way that is expected- those kinds of queries are potentially very slow. Here are the options that you can consider, which have different tradeoffs:

• The easiest option is to change your index to contain the information you need. So if you need "time when this started being UP" you can write that information to Elasticsearch. You would do the calculation using your other tools and write the data in the shape you need. This might be extra work, but gives you a lot of control.

• Similarly, maybe you can use a continuous Data Transform, which will let you turn your data into an entity-centric model, where the entity is the Name. You might be able to group by Terms of Name and Terms of Status. I'm not convinced that this will give you the exact kind of visualization you want, because of the way that this works with filtering, but it's really easy to set up and try for yourself. You can always look at the data using Discover in a table format.

• Canvas can definitely support this, since it has the ability to run multiple queries and gives you control over the post-processing and display. If you want to try this in Canvas I would recommend starting a new thread to discuss.

• While it can't be visualized using the built-in Kibana tools, the bucket script aggregation should solve this problem. Your bucket script would be:

  • Top level aggregation is Terms of Name. Each of the following will be within each Name.
  • Two filters: Status: 'Up', Status: 'Down'
  • Within each filter, add a metric aggregation for Minimum timestamp
  • Within the Terms aggregation, add a bucket script that subtracts the current timestamp from the Minimum Timestamp

  I haven't tested this out, but this seems like the simplest option to me.

[lmit (Lance Mitchell)]

Thank you wylie for responding.

I think the easiest option for my level of Elasticsearch experience is to try your first suggestion, which is to use logstash along with a plugin to look for the inbound status field change and add the additional metric data before shipping the modified document to Elasticsearch.

Wish me luck