Count status but only for last events

Elastic Stack Kibana
1

Hello,

I come here to ask you for help. Thank you in advance to those who will answer me :slight_smile:
I have a index with processing status by city. Status are : UP -> DEGRADED -> DOWN
Here is an example of a collected log.

        {
          "city_name" : "BANGKOK",
          "@timestamp" : "2022-01-01T00:10:00.000Z",
          "site_status" : "DEGRADED"
        },
        {
          "city_name" : "BANGKOK",
          "@timestamp" : "2022-01-01T03:15:00.000Z",
          "site_status" : "DOWN"
        },
		{
          "city_name" : "BRUSSELS",
          "@timestamp" : "2022-01-01T03:51:00.000Z",
          "site_status" : "DEGRADED"
        },
		{
          "city_name" : "BANGKOK",
          "@timestamp" : "2022-01-01T06:33:00.000Z",
          "site_status" : "DEGRADED"
        },
		{
          "city_name" : "BRUSSELS",
          "@timestamp" : "2022-01-01T09:20:00.000Z",
          "site_status" : "DOWN"
        },
		{
          "city_name" : "BANGKOK",
          "@timestamp" : "2022-01-01T10:00:00.000Z",
          "site_status" : "UP",
        }

Now I need to do some visualisations in Kibana. My Kibana version is 7.16.3.

I want to count the number of city in each status, but only for the last status of each city!
For example, Bangkok was Degraded and Down in the past but is now Up (the last status). So I want something like this :

DOWN = 1
DEGRADED = 0
UP = 1

I am not very familiar with Kibana (just a little). Despite research on the forum and internet, I really can't do what I want :cry:

I especially tried to use "Max Bucket" in a metric viz but without success

image
image532×528 22.4 KB

I would really appreciate if someone could give me advice on how to do this.

Gueri

2

OK so I did something with data table like this

image
image771×505 29.3 KB

The result is good but I would have preferred something more visual like colored metrics
image
image

But I can't :roll_eyes:

3

I guess your data need some tranformations
Tranform in Elastic in a good fit for your requirement to build a new index for your entity (city_name)

4

Hi,
Here is what I did to fix my problem.
First solution: I added in the index a boolean to indicate if the event is the last one for city_name. This involves to do an update by query in my processing logstash but it works.
Second solution and the best in my opinion: I used Elasticsearch scripted upserts to create a second index where city_name is the document id. So I have the events history in the first index and the last status with compute values in the second one.

5

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