# Using Transforms and including values which only exist on some events in the source index

**URL:** <https://discuss.elastic.co/t/using-transforms-and-including-values-which-only-exist-on-some-events-in-the-source-index/285067>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [September 24, 2021, 10:31am UTC](https://discuss.elastic.co/t/using-transforms-and-including-values-which-only-exist-on-some-events-in-the-source-index/285067 "2021-09-24T10:31:08Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nsouth](https://avatars.discourse-cdn.com/v4/letter/n/ecccb3/32.png) [@nsouth](https://discuss.elastic.co/u/nsouth)\
**Post date:** [September 24, 2021, 10:31am UTC](https://discuss.elastic.co/t/using-transforms-and-including-values-which-only-exist-on-some-events-in-the-source-index/285067/1 "2021-09-24T10:31:08Z")

</div>

Hi, I am trying to use Transforms to create an entity-centric index of my command logs. I need help including data that only appears on some documents. Each "command" is represented by multiple events, correlated by a `command_unique_id`. Each event has a different `event_type` and has different details about the command.

Here's a pseudo-representation of documents in my source index:

```auto
command_unique_id-1, timestamp, event_type-0 (start), username
command_unique_id-1, timestamp, event_type-1, networkstats, superfluousField
command_unique_id-1, timestamp, event_type-2 (end), authAuditInfo

```

I need the destination index to contain the following columns:

- `command_unique_id`
- `time_duration_ms` (timestamp difference between the start event (type 0) and the end event (type 2)
- **Cherry-picked fields that only exist per-event type.**

Here's a pseudo-representation of what I want the destination to look like:

```auto
command_unique_id-1, time_duration_ms, username, networkstats, authAuditInfo

```

So far, I have the following pivot transform defined and it calculates the `time_duration_ms`. _ **What I need help with is including the data that only exists for certain `event_types`.** _ For example, how do I include `username` which only exists for `event_type = 0`? I also welcome any general feedback about my approach, as I am a novice. Thank you!

```auto
POST /_transform/_preview
{
  "source": {
    "index": "mydata-raw*"
  },
  "pivot": {
    "group_by": {
      "command_unique_id": {
        "terms": {
          "field": "command_unique_id"
        }
      }
    },
    "aggregations": {
      "start-command": {
        "filter": {
          "term": {
            "event_type": "0"
          }
        },
        "aggs": {
          "time": {
            "min": {
              "field": "@timestamp"
            }
          }
        }
      },
      "end-command": {
        "filter": {
          "term": {
            "event_type": "2"
          }
        },
        "aggs": {
          "time": {
            "max": {
              "field": "@timestamp"
            }
          }
        }
      },
      "time_duration_ms": {
        "bucket_script": {
          "buckets_path": {
            "start": "start-command>time.value",
            "end": "end-command>time.value"
          },
          "script": "params.end - params.start"
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [September 27, 2021, 8:48am UTC](https://discuss.elastic.co/t/using-transforms-and-including-values-which-only-exist-on-some-events-in-the-source-index/285067/2 "2021-09-27T08:48:59Z")

</div>

If you use a version prior 7.14 I suggest to have a look at `scripted_metric` and painless, see the [painless based top hits example in the docs](https://www.elastic.co/guide/en/elasticsearch/reference/7.15/transform-painless-examples.html#painless-top-hits).

It becomes easier with 7.14 and above with `top_metrics`, see this [example](https://www.elastic.co/guide/en/elasticsearch/reference/7.15/transform-examples.html#example-customer-names). Despite the name top _metric_, the output field must not be a _metric_, but can be a keyword field. Only the _sort_ field must be of type metric.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 25, 2021, 8:49am UTC](https://discuss.elastic.co/t/using-transforms-and-including-values-which-only-exist-on-some-events-in-the-source-index/285067/3 "2021-10-25T08:49:58Z")

</div>

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