Transforms: How to aggregate multiple events into one event based on shared field?

Elastic Stack Elasticsearch
1

Hi, I have the following events writing to the same index in ES:

{
  "@timestamp": "2023-10-15T17:06:05.137039490Z",
  "ssn": null,
  "z4date": "1697207822",
  "criminalnotes": null,
  "reason": null,
  "notes": null,
  "lname": "LASTNAME",
  "fullnotes": "",
  "icn": null,
  "zid": 391695,
  "fname": "FIRSTNAME",
  "mname": null,
  "status": "new",
  "workingdate": null,
  "criminal": "",
  "email": null,
  "nname": null,
  "fbname": null,
  "@version": "1",
  "city": "CITY"
}

{
  "@timestamp": "2023-10-15T17:05:29.274347904Z",
  "zid": 391695,
  "skill": "Engineer",
  "@version": "1"
}

{
  "@timestamp": "2023-10-15T17:05:29.274347904Z",
  "zid": 391695,
  "skill": "HelpDesk",
  "@version": "1"
}

zid is the unique key between all events. How can I use transforms to aggregate all the fields for a specific zid? I'm hoping to have an output similar to this:

{
  "@timestamp": "2023-10-15T17:06:05.137039490Z",
  "ssn": null,
  "z4date": "1697207822",
  "criminalnotes": null,
  "reason": null,
  "notes": null,
  "lname": "LASTNAME",
  "fullnotes": "",
  "icn": null,
  "zid": 391695,
  "fname": "FIRSTNAME",
  "mname": null,
  "status": "new",
  "workingdate": null,
  "criminal": "",
  "email": null,
  "nname": null,
  "fbname": null,
  "@version": "1",
  "city": "CITY",
  "skill": ["Engineer","HelpDesk"]
}

Timestamp aggregation can be set to 1d.
I tried creating this transform but its not showing me the output that I want to see:

POST _transform/_preview
{
  "source": {
    "index": [
      "z4_db*"
    ]
  },
  "pivot": {
    "group_by": {
      "zid": {
        "terms": {
          "field": "zid"
        }
      },
      "@timestamp": {
        "date_histogram": {
          "field": "@timestamp",
          "calendar_interval": "1d"
        }
      },
      "fname": {
        "terms": {
          "field": "fname"
        }
      },
      "lname": {
        "terms": {
          "field": "lname"
        }
      }
    },
    "aggregations": {
      "skill.terms": {
        "filter": {
          "exists": {
            "field": "skill"
          }
        },
        "aggs": {
          "skill.terms": {
            "terms": {
              "field": "skill",
              "size": 10
            }
          }
        }
      }
    }
  }
}

In the preview output I'm seeing column headers @timestamp, fname, lname, and zid. I'm not seeing a skill column header.

2

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