Dashboard / Canvas based on two indices

[Itay_Bittan]

I have two indices: users & activities.
Users index data looks like:

{
    "email": "alice@gmail.com",
    "Name": "Alice A"
},
{
    "email": "bob@gmail.com",
    "name": "Bob B"
},
...

activity index data looks like this:

{
    "@timestamp": "...",
    "email": "bob@gmail.com",
    "activity": "bob's first activity"
},
{
    "@timestamp": "...",
    "email": "bob@gmail.com",
    "activity": "bob's second activity"
}

I want to create a graph that counts the activities per user.
I did something like this:

But I want to change the email with name field (from users index). I need to present real names in the legend.
Is there a way to do that? JOIN?
I don't mind to use visualization in dashboard or canvas / any other solution (rather than SQL) that supports that.

[flash1293 (Joe Reuter)]

This is currently not possible to do at query time (as it would require to join documents). You need to move this kind of enriching your event index into the ingest phase.

This sounds like a good use case for the enrich processor as part of an ingest pipeline: https://www.elastic.co/guide/en/elasticsearch/reference/current/enrich-setup.html

[aaron-nimocks]

If your index isn't HUGE you can do this in Vega.

{
  "$schema": "https://vega.github.io/schema/vega/v5.json",
  "data": [
    {
      "name": "users",
      "url": {"index": "users", "body": {"size": 10000}},
      "format": {"property": "hits.hits"},
    },
    {
      "name": "data",
      "url": {"index": "activities", "body": {"size": 10000}},
      "format": {"property": "hits.hits"},
      "transform": [
        {
          "type": "lookup",
          "from": "users",
          "key": "email",
          "fields": ["name"],
          "as": ["obj"]
        },
        {
          "type": "project",
          "fields": [
            "obj._source.email",
            "obj._source.name",
            "_source.activity"
          ],
          "as": ["email", "name", "activity"]
        }
      ]
    }
  ]
}

Given your example documents the above produces 2 results which I believe is what you need. Would need to do an aggregation or 2 still to get counts but the start is there.

{
 "activity:" "bob's first activity",
 "email:" "bob@gmail.com",
 "name": "Bob B"
},
{
 "activity" "bob's second activity",
 "email": "bob@gmail.com",
 "name": "Bob B"
}

Also I'd still recommend doing this during ingest. This is just an option if required.

[Itay_Bittan]

Thanks,
I wasn't familiar with Vega - look nice.
Tried your snippet with no lack - no errors in Kibana but I get blank visualisation.

[aaron-nimocks]

I didn't do the visualization part. Just the data portion.

There is a lot left to do in Vega still for this and would require learning it first unfortunately.

The way I learned is going through the examples at https://vega.github.io/vega/examples/ and then loading those in the editor and seeing what everything does.

[Itay_Bittan]

Thanks! I'll try & update!
BTW, back to the HUGE index, with Vega, is it (the lookup) done in the client-side or server-side?

[aaron-nimocks]

Client side.

Say you do a query in Vega that returns 9,000 results all that data goes to the client and then processed. The max return by default is 10,000 so if you need to query above that you need update settings.

Also if you put a Vega visualization in a dashboard the UI loads for the dashboard and then the data processes for Vega. Meaning if it's a lot of data that visualization could appear a few seconds after the rest of the dashboard does.