Hello!
First of all, let me describe my data source:
I have business items that run through multiple processes.
I've got a historic SQL table, in which all actions (item status changed, item processed, item viewed,...) regarding those items are stored.
Here is a minimal example of that table:
| Item | Action | Date |
|---|---|---|
| 1 | process a started | 2024-06-01 |
| 2 | process a started | 2024-06-02 |
| 2 | process c started | 2024-06-03 |
| 1 | process b started | 2024-06-04 |
| 1 | finished | 2024-06-05 |
| 2 | finished | 2024-06-06 |
Using Logstash, i load this table into elasticsearch. Then, performing a transform i created in painless implementing the business logic, grouping by ID and using buckets, i achieve the following structure for my target Index in elasticsearch:
{
"buckets": [
{
"duration_in_days": 3,
"process": "a",
},
{
"duration_in_days": 1,
"process": "b",
},
],
"item_id": 1
},
{
"buckets": [
{
"duration_in_days": 1,
"process": "a",
},
{
"duration_in_days": 3,
"process": "c",
},
],
"item_id": 2
},
My end goal is to create a visualisationwhich visualizes the duration of each process per document, e.g. a table in Kibana that looks like this:
| Item | Process | Duration |
|---|---|---|
| 1 | a | 3 |
| 1 | b | 1 |
| 2 | a | 1 |
| 2 | c | 3 |
But also i want it to be possible to use the filter controls in Kibana to filter the table by Process and by Item, e.g. if i set the Filters to Item = 1 and Process = 'a', i want the table to only contain that row:
| Item | Process | Duration |
|---|---|---|
| 1 | a | 3 |
Right now, with my strategy, this is not possible, since my in my structure i create a document in the index for each Item with buckets for each process. So if i filter by process, all documents containing that process in their buckets will be shown.
How can i adapt my strategy to achieve my goal? Can this be done in kibana? Do i have to adjust the transform script to create one document within the target index for each item-process combination?
Any help would be very much appreciated, thanks in advance!
