Best mapping/architecture for querying latest members from join/leave documents?

There are a number of groups, where people can join and leave any groups. I have logs on all join and leave action, i.e. {"user": "Alice", "action": "JOIN", "group": "group-0", "@timestamp": "2017-12-12T20:00:00.000Z"} and {"user": "Alice", "action": "LEAVE", "group": "group-0", "@timestamp": "2017-12-13T20:00:00.000Z"}. People are allowed to join again after leave.

I want to query who is in group-0 from these logs. I cannot make a visualization that "add person when join & delete when leave", or make a suitable query.

Any ways to get the list of current people in group-0? If no, what's more should I log or restructure to fulfil my needs?


===== EDIT =====
I come up with a solution, to have a "member list" document for each group, using script update to update the list. This is one POC logstash conf file.

input {
    stdin {

filter {
    grok {
        match => { "message" => "%{NUMBER:updateTimestamp} %{NOTSPACE:action} %{NOTSPACE:group} %{NOTSPACE:user}" }

    ruby {
        code => "
            e = {};
            e['user'] = event.get('user');
            event.set('memberList', event.get('memberList') << e);


output {
    elasticsearch {
        hosts => ["..."]
        index => "group"
        document_type => "members"
        document_id => "%{group}"
        manage_template => false
        action => "update"
        doc_as_upsert => true
        script_lang => "painless"
        script_type => "inline"
        script => '
            if (ctx._source.updateTimestamp != params.event.get("updateTimestamp")) {
                ctx._source.updateTimestamp = params.event.get("updateTimestamp");
                if (params.event.get("action") == "JOIN") {
                } else if (params.event.get("action") == "LEAVE") {
                    def list = ctx._source.memberList;

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