Visualization time lookup table or "join" possible?


(Rvn Br) #1

Hi Everyone,

I'm looking for a way (best practice) to implement a sort of join or lookup of different IDs in Kibana visualizations.

Basically, using ELK we will send events with certain fields, such as IDs for categories, clients, etc. These are stored in other indices, which could be used for lookup...

The idea is that instead of showing

User ID - count(xxx)

I wanted my users to see another field, such as the User name, or something, instead of the id.

I have researched abt the different join options in ES and below are my comments for each strategy. Please do correct me or point me in the correct direciton if i'm missing something?

Application join - this is pretty much the best solution/only solution? However the "application" in this case is Kibana, so how do I implement? A last resort option would be a ES proxy that finds some "known" fields in the kibana query, and replaces it with a lookup of that field to the desired "label" field (such as UserID->UserFirstName+UserLastName for example.)
Parent/Child - this would work as the entire log entry would need to be a child of a single parent. In our case we need to have parents of different types, for example a User parent, a Category parent, etc.
Denormalization - store the name of the user inside each log event or the category name inside each log event would work until there is an update to a field which is used for exhibition...
Nested Documents - not really useful as it would require storing the entire content of the user inside each log, or every log inside each user, which would pose a problem because we have Categories, and other dimensions which are N-to-N... (even if each even has only 1 user, 1 category, etc).

I'm at a loss for the best option here... I'm pretty much settled that I'll need to go from "Names" to IDs prior to conducting the search, and from IDs to names after the search. This is doable app-side, but I cant see how to implement this in Kibana. Any experience or ideas greatly appreciated!


(Mark Walkom) #2

The lookup table idea is something that is coming in KB 4.1 or 4.2 (not sure which).

However joins are a difficult problem in nosql type systems, as you can see.


(Rvn Br) #3

Hi Mark,

Yes that all makes sense in line with regular NoSQL behavior... Like I said I was looking for some side of app-side-join emulation or something... I'm glad to hear its on the roadmap, but I wont even ask if there is a planned date for it as it seems it will be quite a ways away :smile:

Eitherway, I guess we will start looking at some other ways of doing it with a "proxy" layer or something...

If anyone is aware of a "proxy" project which works well with recent ES versions and Kibana I'd be interested in looking as a starting point... I know there was one or two projects that were abandoned in favor of Shield usage... But in this case the use-case is slightly different...

Thanks!


(Jared Kauppila) #4

Hi Mark,

Just curious if this has been on the road map?

Thanks


#5

I also have the same question, have you found any good solution yet?


(Curu Wong) #6

I also need this feature.
For the sake of performance, when index docs to Elasticsearch, I'd like to store only the IDs , like user id, city id, etc. which would use far less network bandwidth and elasticsearch resource. However, when display the aggregation result in Kibana, I need the key to be mapped to some string, which could be looked up in redis. for example:
if we get a user id of 2015, then we can get its name via a key lookup of "user_id.2015" to get its name from redis.


(Rvn Br) #7

Has anyone found a suitable way to tackle this issue?


(system) #8