# Finding distinct values from a field

**URL:** <https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965>\
**Category:** Kibana\
**Created:** [May 23, 2018, 9:33am UTC](https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965 "2018-05-23T09:33:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Suhas\_K](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/suhas_k/32/31525_2.png) [@Suhas\_K](https://discuss.elastic.co/u/Suhas_K)\
**Post date:** [May 23, 2018, 9:33am UTC](https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965/1 "2018-05-23T09:33:22Z")

</div>

Hi,

I have made a filter in Logstash which divides log lines into different fields.

```
    {
      "_index": "XX_DATE",
      "_type": "doc",
      "_id": "vBhbcghstjdJgpojNbs",
      "_version": 1,
      "_score": null,
      "_source": {
        "m": "Data: ###InData###: |SId[12]|CId[6652888]|X[BC]-->SId[12]|TC=9565|RId=XXXX|CStatus=P|NoOfRec=1|TRec=1|Er=0|x=BC|CustomerID=xxxxxx|DID=yyyyyy|S=Fat|xCode=SSS|",
        "s": "XXXXXXXX",
        "@version": "1",
        "@timestamp": "2018-05-22T01:52:06.671Z"
       }
     }

```

In the above JSON, the **CustomerID** in \_source.m is unique for every customer. However, since the CustomerID is not a separate field, it is not possible to get the number of distinct customers using the **unique count metric** in Visualize.

Is there any way to get the number of unique customers without changing the Logstash filters? (as we have already processed a huge amount of data using the same Logstash grok filters)

Thanks in advance. 🐅

---

<div class="post-metadata">

**Author:** ![Stacey\_Gammon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stacey_gammon/32/14025_2.png) [@Stacey\_Gammon](https://discuss.elastic.co/u/Stacey_Gammon)\
**Post date:** [May 23, 2018, 1:36pm UTC](https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965/2 "2018-05-23T13:36:15Z")

</div>

You can try parsing the string with a painless scripted field to extract out the customer id and aggregate on that.

[https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-examples.html](https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-examples.html)

---

<div class="post-metadata">

**Author:** ![Suhas\_K](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/suhas_k/32/31525_2.png) [@Suhas\_K](https://discuss.elastic.co/u/Suhas_K)\
**Post date:** [May 24, 2018, 12:35pm UTC](https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965/3 "2018-05-24T12:35:32Z")

</div>

Thanks for the reply, @Stacey_Gammon

I used the following query to parse the string, extract the CustomerID and insert it back as a **new field** into the document.

```auto
POST xxxx-DATE/doc/_update_by_query
{
  "query": {
    "match_phrase": {
      "m": "CustomerID"
    }
  },
  "script": {
    "lang": "painless",
    "source": "ctx._source.CustomerID = /.*CustomerID=(\\w{0,50})\\|.*/.matcher(ctx._source.m).replaceAll('$1')"
  }
}

```

Using this approach, I am now able to get the unique number of customers. However, in order to get this result, I had to make the following change in elasticsearch.yml in all my Elasticsearch nodes. This also involved **restarting** all the **elastic nodes**. As the restart didn't affect my local test environment, is it recommended to add the below change and restart the nodes in production? Also, will it affect the existing Elasticsearch data in any way?

```auto
script.painless.regex.enabled: true

```

My logs are indexed based on the date, with a new index being created each day. Hence, I would have to execute this query manually everyday in order to achieve the same result.

Is there a better way to approach the issue?

I have also faced a problem when I tried executing the POST request without the "query" field, where it went through all the documents (~1,300,000). This resulted in a timeout error 503. Whereas, when I added the query field, it only had to parse through and update around 200 documents. My concern is that there might be a possibility of a timeout even with the query field due to a large number of documents in production. Is there any way to overcome this issue?

Thanks 🐅

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 21, 2018, 12:45pm UTC](https://discuss.elastic.co/t/finding-distinct-values-from-a-field/132965/4 "2018-06-21T12:45:16Z")

</div>

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