From Elasticsearch aggregations to Kibana dashboard

[bianca6]

Hi,

IN A FEW WORDS:

I would like to display on a Kibana dashboard some duration calculated from my logs with the help of Elasticsearch aggregation.

PRECISELY:

I have some logs like this (simplified):

timestamp = 10, id = 1, logtype = Start
timestamp = 12, id = 1, logtype = Stop

timestamp = 11, id = 1, logtype = Start
timestamp = 13, id = 1, logtype = Stop

timestamp = 10, id = 2, logtype = Start
timestamp = 11, id = 2, logtype = Stop

I would like to group them by id then logtype and finally sum the timestamp.

This part is done, I managed to aggregate all of that with the Kibana dev tools console, the result looks like this (simplified):

"buckets" : [

    "id": 1,
    "start": {
        "doc_count":2,
        "sum_timestamp":21
    },
    "stop": {
        "doc_count":2,
        "sum_timestamp":25
    },

    "id": 2,
    "start": {
        "doc_count":1,
        "sum_timestamp":10
    },
    "stop": {
        "doc_count":1,
        "sum_timestamp":11
    }

]

Now the real deal, I have two more things to do:

1. Substract the "sum_timestamp"s of the logtypes "stop" and "start" in order to get a duration
2. Display the whole aggregation into Kibana (not a time series, something like a statistical distribution for each duration)

And this is where I need your help.
I have no idea how to substract some previous aggregations, and more importantly, how to display the result into a Kibana dashboard.

Maybe I see the problem from a wrong point of view and it's absolutely not possible? Anyway, I am open to all suggestions

[bianca6]

I found the solution, thanks to the "Transform".

As we can read here: Transforming data | Elasticsearch Guide [8.0] | Elastic

Transforms enable you to convert existing Elasticsearch indices into summarized indices, which provide opportunities for new insights and analytics. For example, you can use transforms to pivot your data into entity-centric indices that summarize the behavior of users or sessions or other entities in your data. Or you can use transforms to find the latest document among all the documents that have a certain unique key.

Which is exactly what I needed. From logs to a group of events, and everything stored in a new index I can use later with Kibana.

Lots of example can be found here:
https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-examples.html

With the oversimplified example I wrote, it will be something like this (maybe not the best, but it works):

PUT _transform/my_first_transform
POST _transform/_preview
{
  "source": {
    "index": "my_old_index"
  },
  "dest": {
    "index": "my_new_index"
  },
  "pivot": {
    "group_by": {
      "ids": {
        "terms": {
          "field": "id"
        }
      }
    },
    "aggregations": {
      "logtype": {
        "terms": {
          "field": "logtype.keyword"
        }
      },
      "ts_min": {
        "min": {
          "field": "timestamp"
        }
      },
      "ts_max": {
        "max": {
          "field": "timestamp"
        }
      },
      "start": {
        "filter": {
          "term": {
            "logtype.keyword": "Start"
          }
        },
        "aggs": {
          "sum_start": {
            "sum": {
              "field": "timestamp"
            }
          }
        }
      },
      "stop": {
        "filter": {
          "term": {
            "logtype.keyword": "Stop"
          }
        },
        "aggs": {
          "sum_stop": {
            "sum": {
              "field": "timestamp"
            }
          }
        }
      }
      "duration": {
        "bucket_script": {
          "buckets_path": {
            "my_starts": "start>sum_start.value",
            "my_stops": "stop>sum_stop.value"
          },
          "script": "params.my_stops - params.my_starts"
        }
      }
    }
  }
}

The POST is for a preview only, the PUT is for the real execution.

Then, you will find your transform here:
Kibana > Stack Management > Data > Transforms

And run it with Actions > Start.

I don't know yet how to make it automatic, maybe someone does.