Create visualization with actual status of process ("group by"?/subquery?)

[Joao_Palma (João Palma)]

Hello,

I have a log file, like this :
{"process": "X2", "status": "open", "@timestamp": "2021-01-05T15:34:41.061Z"}
{"process": "X1", "status": "open", "@timestamp": "2021-01-05T15:35:42.061Z"}
{"process": "X5", "status": "Updated", "@timestamp": "2021-01-05T15:36:46.061Z"}
{"process": "X2", "status": "In progress", "@timestamp": "2021-01-05T15:38:48.061Z"}
{"process": "X6", "status": "Closed", "@timestamp": "2021-01-05T15:39:49.061Z"}
{"process": "X2", "status": "Updated", "@timestamp": "2021-01-05T15:40:46.061Z"}
{"process": "X2", "status": "Closed", "@timestamp": "2021-01-05T15:48:49.061Z"}

The app logs multiple status but one process have only one status and it's the most recent log.
And I want a visualization like this:
Open: 1 (X1)
Updated: 1 (X5)
Closed: 2 (X2 and X6)

[wylie (Wylie Conlon)]

What you're asking about is frequently called entity-centric indexing. You have timeseries logs, but you want to convert them into a single status per process. This can be done using the transforms feature of Elasticsearch, or by changing something about how you index data to Elasticsearch.

If the number of documents you have is small, it might be possible to create this visualization using Vega- it is the most customizable visualization in Kibana.

[Joao_Palma (João Palma)]

@wylie

transforms feature

Thanks Wylie
But You I can do this with transforms feature? In SQL I have to do in two queries.. And here I can't see how I can do this..
I try Group By process and agregation with timestamp.max but this isn't I want...

You can tell me how I can do this?

[wylie (Wylie Conlon)]

This is about to get a lot easier in the release of 7.11, but for now it's a little complicated. In 7.11 the transforms feature will get a "latest only" mode: https://www.elastic.co/guide/en/elasticsearch/reference/7.11/put-transform.html

What you can do is set up a transform that:

1. Splits by a date histogram with whatever interval you want
2. Split by status
3. Uses a terms aggregation to get all process names

Then you can create a data table. Bucket by Terms of process.name, and the metric is Top Hits of process.status, sorted by timestamp.