Get all docs with the occurrence of field values only once

[kaushik.vankayala]

Hi There,

I have the documents in my index in the following format;

{
  "_index": "mt_uat-app",
  "_type": "doc",
  "_source": {
    "Environment": "UAT",
    "loglevel": "INFO",
    "APITransactionId": "4d95e9b12cf0488084a22a8760c92c20",
    "audittype": "Audit-IN",
    "@timestamp": "2018-08-31T01:03:10.917Z",
    "Request": "POST /api/experience/member/login",
  },
  "fields": {
    "@timestamp": [
      "2018-08-31T01:03:10.917Z"
    ]
  }
}

another one say like below;

{
  "_index": "mt_uat-app",
  "_type": "doc",
  "_source": {
    "Environment": "UAT",
    "APITransactionId": "4d95e9b12cf0488084a22a8760c92c20",
    "audittype": "Audit-OUT",
    "@timestamp": "2018-08-31T01:03:12.917Z",
    "ResponseTime": "2 sec",
  },
  "fields": {
    "@timestamp": [
      "2018-08-31T01:03:10.917Z"
    ]
  }
}

Could anybody suggest how its possible to filter out the docs which have the count of APITransactionId exactly 1.

So in brief for every hit of an API, an Audit-IN and and an Audit-OUT will be generated which will have same APITransactionId. But if the API does not respond then we will NOT get an Audit-OUT.

I am looking for a DSL query probably with aggregation or an alternate approach to get all the docs which have the count of APITransactionId only 1, that way i get to filter out all docs which have only Audit-IN and NO Audit-OUT for a specific APITransactionId

Can anybody help at the earliest!

Regards

Kaushik

[kaushik.vankayala]

Hello Team, Any help or update here?

[cbuescher (Christoph)]

I'd say you can try to use a terms aggregation on the APITransactionId field, the only problem with that is probably that the size would have to be high in order to catch all ids with count 1. A possible alternative could be the Composite Aggregation introduced in I believe Elasticsearch 6.1. There you could use the APITransactionId as a terms value source and paginate over the result in ascending order, stopping when you reach the buckets with count 2. I haven't given it a try yet but I think it could work.

Cheers