elasticsearch transform data to array

Question

i want to use ES to calculate user-retention:

1, event logs to default index
2, transform to middle index: entity-centric data, group by acc
3, use aggs filters (or adjacency_matrix) to calculate intersecting result of each day.

the problem is at step-2: how to generate a nice transform

input event logs:

POST _bulk
{"index": {"_index": "test.u1"}}
{"acc":1001, "event":"create", "timestamp":"2020-08-01 09:00"}
{"index": {"_index": "test.u1"}}
{"acc":1001, "event":"login", "timestamp":"2020-08-01 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1001, "event":"login", "timestamp":"2020-08-02 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1001, "event":"login", "timestamp":"2020-08-03 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1002, "event":"create", "timestamp":"2020-08-01 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1002, "event":"login", "timestamp":"2020-08-02 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1002, "event":"login", "timestamp":"2020-08-02 11:00"}
{"index": {"_index": "test.u1"}}
{"acc":1003, "event":"create", "timestamp":"2020-08-01 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1004, "event":"create", "timestamp":"2020-08-02 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1004, "event":"login", "timestamp":"2020-08-02 10:00"}
{"index": {"_index": "test.u1"}}
{"acc":1004, "event":"login", "timestamp":"2020-08-03 10:00"}

expect middle index:

{"acc":1001, "create":"08-01", "login":[08-01, 08-02, 08-03]}
{"acc":1002, "create":"08-01", "login":[08-02]}
{"acc":1003, "create":"08-01", "login":[]}
{"acc":1004, "create":"08-02", "login":[08-02, 08-03]}

how to generate the "login" array? or any better design is welcome.

user_887205 · Answer

make it done by aggs.scripted_metric

PUT _transform/tr-acc2-ar2
{
  "source": {
    "index": [
      "mhlog2-*"
    ]
  },
  "pivot": {
    "group_by": {
      "msg.#account_id": {
        "histogram": {
          "field": "msg.#account_id",
          "interval": "1"
        }
      }
    },
    "aggregations": {
      "create": {
        "filter": {
          "term": {
            "msg.#event_name.keyword": "createRole"
          }
        },
        "aggs": {
          "time": {
            "min": {
              "field": "@timestamp"
            }
          }
        }
      },
      "login": {
        "filter": {
          "term": {
            "msg.#event_name.keyword": "login"
          }
        },
        "aggs": {
          "days": {
            "scripted_metric": {
              "init_script": "state.days=[:];",
              "map_script": "state.days[doc['@timestamp'].value.toString('yyyy-MM-dd')]=1; ",
              "combine_script": "return state",
              "reduce_script": "def days = [:]; def array =[]; for (s in states) { for (d in s.days.keySet()) { days[d]=1; } }  for (d in days.keySet()) { array.add(d);} return array; "
            }
          }
        }
      }
    }
  },
  "dest": {
    "index": "idx.tr.acc2.ar2"
  },
  "sync": {
    "time": {
      "field": "@timestamp",
      "delay": "60s"
    }
  }
}

gen middle index:

_id : AAAAAAAA
_index : acc.array  
_score : 0
_type : _doc    
create.time : Aug 18, 2020 @ 11:17:43.000   
login.days : 2020-08-18T00:00:00.000Z, 2020-08-19T00:00:00.000Z, 2020-08-20T00:00:00.000Z   
msg.#account_id : 12333212323

finally, 2020-08-18 user-retention for 2020-08-19 is easy by KQL filter :

create.time: 2020-08-18 AND login.days: 2020-08-19

elasticsearch transform data to array

1 Answers