Combine connect and disconnect documents with transform?

Elastic Stack Elasticsearch
2

This should be possible given your document structure!

Here's an example configuration:

POST _transform/_preview
{
  "source": {
    "index": [
      "transform_transaction_source"
    ]
  },
  "pivot": {
    "group_by": {
      "id": {
        "terms": {
          "field": "connectionID.keyword"
        }
      }
    },
    "aggregations": {
      "connected": {
        "filter": {
          "term": {
            "event.keyword": "connected"
          }
        },
        "aggs": {
          "start": {
            "min": {
              "field": "timestamp"
            }
          }
        }
      },
      "disconnected": {
        "filter": {
          "term": {
            "event.keyword": "disconnected"
          }
        },
        "aggs": {
          "end": {
            "max": {
              "field": "timestamp"
            }
          }
        }
      },
      "diff": {
          "bucket_script": {
            "buckets_path": {
              "connected": "connected>start",
              "disconnected": "disconnected>end"
            },
            "script": "params.disconnected - params.connected"
          }
        }
    }
  }
}

It groups by the unique ids, then uses nested aggregations to identify the start and end timestamps. Using a bucket_script aggregation we can calculate the duration. Besides the bucket script this can be built using the Transform wizard UI in the Kibana management section. I used the the "copy-to-clipboard" option to copy the config to Kibana Dev Tools to add the bucket_script agg. Here's the output of that preview call:

{
  "preview": [
    {
      "connected": {
        "start": "2024-12-27T08:00:00.000Z"
      },
      "disconnected": {
        "end": "2024-12-27T09:00:00.000Z"
      },
      "diff": 3600000,
      "id": "8adca1fb"
    }
  ],
  "generated_dest_index": {
    "mappings": {
      "_meta": {
        "_transform": {
          "transform": "transform-preview",
          "version": {
            "created": "10.0.0"
          },
          "creation_date_in_millis": 1735312462757
        },
        "created_by": "transform"
      },
      "properties": {
        "disconnected.end": {
          "type": "date"
        },
        "connected": {
          "type": "object"
        },
        "disconnected": {
          "type": "object"
        },
        "connected.start": {
          "type": "date"
        },
        "id": {
          "type": "keyword"
        }
      }
    },
    "settings": {
      "index": {
        "number_of_shards": "1",
        "auto_expand_replicas": "0-1"
      }
    },
    "aliases": {}
  }
}
2 Likes