# Finding the first occurrence of all Unique IDs within a term **URL:** \ **Category:** Kibana\ **Created:** [February 10, 2022, 10:06pm UTC](https://discuss.elastic.co/t/finding-the-first-occurrence-of-all-unique-ids-within-a-term/296908 "2022-02-10T22:06:17Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![SkipJ](https://avatars.discourse-cdn.com/v4/letter/s/898d66/32.png) [@SkipJ](https://discuss.elastic.co/u/SkipJ)\ **Post date:** [February 10, 2022, 10:06pm UTC](https://discuss.elastic.co/t/finding-the-first-occurrence-of-all-unique-ids-within-a-term/296908/1 "2022-02-10T22:06:17Z")
I'm trying to create a visualization that captures all new users a from the last month (now/M-1 to now/M), then capture the amount of sessions they've ran from counting their unique session IDs. The challenge I'm facing is that I want to automate this so we pull all new user IDs and their session IDs without doing what we currently do, which is to manually create and add a filter that contains the new user IDs, then running a Unique Session count metric against it. I'm admittedly unfamiliar with Kibana and trying to find resources, but I'm not sure what the best approach is. Thanks! ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [February 11, 2022, 1:13am UTC](https://discuss.elastic.co/t/finding-the-first-occurrence-of-all-unique-ids-within-a-term/296908/2 "2022-02-11T01:13:34Z")
If you have more than some tens of thousands of users (above the max bucket limit), it's difficult to achieve the results from a single query. If it's below the limit, bucket select aggreation is a possible option. Otherwise, The easiest way could be flag their first session or add the the information about user creation date on the client side. Is it difficult? ---
**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\ **Post date:** [February 11, 2022, 10:35am UTC](https://discuss.elastic.co/t/finding-the-first-occurrence-of-all-unique-ids-within-a-term/296908/3 "2022-02-11T10:35:12Z")
My multistage solution is as below. I used `kibana_sample_data_logs` sample dataset and consider `clientip` as fake 'user ID'. Selected new user for last day (not month). # Outline 1. create 1st transform groupby `clientip` to get their first timestamp 2. create 2nd transform to aggregate the first timestamp to day buckets with scripted metric aggregation to retrieve unique `clientip`s as array. 3. query terms lookup to filter `clientip`s whose first timestamp is on a specific day. # Detail ## 1. create 1st transform groupby `clientip` to get their first timestamp ```auto PUT /_transform/test_kibana_sample_first_timestamp { "dest":{ "index": "test_kibana_sample_first_timestamp" }, "pivot": { "group_by": { "clientip": { "terms": { "field": "clientip" } } }, "aggregations": { "first_timestamp": { "min": { "field": "timestamp" } } } }, "source": { "index": "kibana_sample_data_logs" } } POST _transform/test_kibana_sample_first_timestamp/_start ``` Then you get: ```auto GET /test_kibana_sample_first_timestamp/_search?size=3&filter_path=hits.hits._source { "hits" : { "hits" : [ { "_source" : { "first_timestamp" : "2022-01-24T07:51:57.333Z", "clientip" : "0.72.176.46" } }, { "_source" : { "first_timestamp" : "2022-01-26T11:02:32.392Z", "clientip" : "0.207.229.147" } }, { "_source" : { "first_timestamp" : "2022-01-27T10:55:29.114Z", "clientip" : "0.209.144.101" } } ] } } ``` ## 2. create 2nd transform to aggregate the first timestamp to day buckets with scripted metric aggregation to retrieve unique `clientip`s as array. First, create target index of the 2nd transform: ```auto PUT /test_kibana_sample_first_timestamp_bucket/ { "mappings": { "properties": { "day": {"type":"date"}, "new_ip": {"type": "ip"} } } } ``` Then make ingest pipeline to set \_id field: ```auto PUT /_ingest/pipeline/test_set_id { "processors": [ { "set": { "field": "_id", "value": "{{day}}" } } ] } ``` Create 2nd transform and start. I used scripted metric "[unique values aggregation](https://discuss.elastic.co/t/how-to-add-field-that-is-only-present-in-some-documents/295283/6)" . ```auto PUT /_transform/test_kibana_sample_first_timestamp_bucket { "dest":{ "index": "test_kibana_sample_first_timestamp_bucket", "pipeline": "test_set_id" }, "pivot": { "group_by": { "day": { "date_histogram": { "field": "first_timestamp", "calendar_interval": "day" } } }, "aggregations": { "new_ip": { "scripted_metric": { "init_script": "state.set = new HashSet()", "map_script": "if (params['_source'].containsKey(params.field)) {state.set.add(params['_source'][params.field])}", "combine_script": "return state.set", "reduce_script": "def ret = new HashSet(); for (s in states) {for (k in s) {ret.add(k);}} return ret", "params":{ "field": "clientip" } } } } }, "source": { "index": "test_kibana_sample_first_timestamp" } } POST /_transform/test_kibana_sample_first_timestamp_bucket/_start ``` Then you get: ```auto GET /test_kibana_sample_first_timestamp_bucket/_search?size=1&filter_path=hits.hits._id,hits.hits._source { "hits" : { "hits" : [ { "_id" : "2022-01-23T00:00:00.000Z", "_source" : { "day" : "2022-01-23T00:00:00.000Z", "new_ip" : [ "19.253.238.55", "42.17.43.107", "81.32.253.92", "91.183.212.113", "240.58.187.246", "1.5.239.89",... ] } } ] } } ``` ## 3. query terms lookup to filter `clientip`s whose first timestamp is on a specific day. You can filter clientips whose first timestamp is on a specific day. ```auto GET /kibana_sample_data_logs/_search { "query":{ "terms": { "clientip": { "index": "test_kibana_sample_first_timestamp_bucket", "id": "2022-01-23T00:00:00.000Z", "path": "new_ip" } } } } ``` This is a sample visualization with filtering by clientips whose first time stamp is on 2022-01-27. ![image](https://us1.discourse-cdn.com/elastic/original/3X/d/e/de646fb67a2bf7023e5dc73f2b1652ebc6eae109.png) You have to use Query DSL to use terms lookup query for filtering. ![image](https://us1.discourse-cdn.com/elastic/original/3X/4/c/4c0c68094046126df11d20a8b6943118b9672de8.png) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 11, 2022, 10:35am UTC](https://discuss.elastic.co/t/finding-the-first-occurrence-of-all-unique-ids-within-a-term/296908/4 "2022-03-11T10:35:13Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.