Joining data from multiple types in visualization

Elastic Stack Kibana
1

Hi,
Currently I'm using JDBC and logstash to import my SQL database into Elasticsearch. I've got three tables, which I'm putting under the same index and using a different type for each table. For ease of explanation, the types and fields look like this:

  • User

    UserID
    UserName

  • Account

    AccountID
    AccountName

  • AccountUsers

    AccountID
    UserID

Basically, an account can have multiple users, and users can belong to multiple accounts. What I'm trying to do in Kibana is visualize how many users belong to each account using a bar chart and the type 'AccountUsers' (x-axis:AccountID, y-axis:Unique count of UserID). This is working fine, but I'd prefer the x-axis labels to be 'AccountName' instead of 'AccountID'. I've done some research, and it doesn't seem like any functionality exists to use these 'AccountID's to look up the actual account name in a different document type (In my case, 'Account') and display that instead. Can anybody suggest ways I will be able to achieve this?

2

One way you could do this is to get rid of User and Account types, and simply put the UserName and AccountName records directly into the AccountUsers records. If you've got a lot of other data in the User and Account records, this wouldn't be as practical, however. Is that the case?

3

Yeah there's quite a lot of other data in the records. I've just excluded it for the sake of the question. Modifying the records themselves isn't really an option in my case.

4

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