ElasticSearch : nested query in "IN" operator

Question

I have following data in my Index:

{
   "took": 1,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": 1,
      "hits": [
         {
            "_index": "para-slapdmine-logs-2016.11.11",
            "_score": 1,
            "_source": {
               "message": "<167>Nov 11 16:22:05 red5admin slapd[45740]:
 conn=1046 op=0 RESULT tag=97 err=49 text=",
               "@timestamp": "2016-11-11T10:52:42.921Z",
               "timestamp": "Nov 11 16:22:05",
               "connection": 1046,
               "operation_number": 0,
               "tag": 97,
               "error_code": 49
            }
         },
         {
            "_index": "para-slapdmine-logs-2016.11.11",
            "_score": 1,
            "_source": {
               "message": "<167>Nov 11 16:22:05 red5admin slapd[45740]:
 conn=1046 fd=13 ACCEPT from IP=10.1.2.2:37713 (IP=0.0.0.0:389)",
               "@version": "1",
               "@timestamp": "2016-11-11T10:52:42.920Z",
               "type": "slapdmine",
               "timestamp": "Nov 11 16:22:05",
               "connection": 1046,
               "fd_number": "13",
               "src_ip": "10.1.2.2"
            }
         },
         {
            "_index": "para-slapdmine-logs-2016.11.11",
            "_score": 1,
            "_source": {
               "message": "<167>Nov 11 16:22:05 red5admin slapd[45740]:
 conn=1046 op=0 BIND dn=\"uid=dharmikp,ou=python,dc=red5admin\"
 method=128",
               "@version": "1",
               "@timestamp": "2016-11-11T10:52:42.920Z",
               "timestamp": "Nov 11 16:22:05",
               "connection": 1046,
               "operation_number": 0,
               "operation_name": "BIND",
               "bind_dn": "uid=dharmikp,ou=python,dc=red5admin",
               "bind_method": "128"
            }
         },
         {
            "_index": "para-slapdmine-logs-2016.11.11",
            "_score": 1,
            "_source": {
               "message": "<167>Nov 11 16:22:05 red5admin slapd[45740]:
 conn=1046 op=1 UNBIND",
               "@timestamp": "2016-11-11T10:52:42.953Z",
               "type": "slapdmine",
               "timestamp": "Nov 11 16:22:05",
               "connection": 1046,
               "operation_number": 1,
               "operation_name": "UNBIND"
            }
         }
      ]
   }
}

I want to find the list of src_ip where error_code is 49. These two attribute not present in single document but connection id is same in that documents.

If I had to write SQL query I might have done that following way

select src_ip from ldap where connection in (select connection 
from ldap where error_code = 49)

Any idea how I can achieve this in ElasticSearch ?

Using ElasticSearch(2.3.3).

Mapping of my index

"para-slapdmine-logs-2016.11.11" : {
    "mappings" : {
      "slapdmine" : {
        "properties" : {
          "@timestamp" : {
            "type" : "date",
            "format" : "strict_date_optional_time||epoch_millis"
          },
          "@version" : {
            "type" : "string"
          },
          "bind_dn" : {
            "type" : "string"
          },
          "bind_method" : {
            "type" : "string"
          },
          "connection" : {
            "type" : "long"
          },
          "dst_ip" : {
            "type" : "string"
          },
          "dst_port" : {
            "type" : "string"
          },
          "error_code" : {
            "type" : "long"
          },
          "fd_number" : {
            "type" : "string"
          },
          "host" : {
            "type" : "string"
          },
          "logsource" : {
            "type" : "string"
          },
          "message" : {
            "type" : "string"
          },
          "operation_name" : {
            "type" : "string"
          },
          "operation_number" : {
            "type" : "long"
          },
          "pid" : {
            "type" : "string"
          },
          "program" : {
            "type" : "string"
          },
          "src_ip" : {
            "type" : "string"
          },
          "src_port" : {
            "type" : "string"
          },
          "tag" : {
            "type" : "long"
          },
          "timestamp" : {
            "type" : "string"
          },
          "type" : {
            "type" : "string"
          }
        }
      }
    }
  }

Possible duplicate of [What is the ElasticSearch equivalent for an SQL subquery?](http://stackoverflow.com/questions/28734436/what-is-the-elasticsearch-equivalent-for-an-sql-subquery) — gerosalesc, Nov 11 '16 at 20:51

gerosalesc · Answer 1 · 2016-11-13T12:04:54.100

0

I am afraid we can't do such thing as a SQL subquery at this moment, but you we can still do an Application-Side Join using Terms query like this:

GET /my_index/ldap/_search
{
  "query": {
    "bool": {
      "filter": [{ "term":  { "error_code": 49 }}]
    }
  }
}

GET /my_index/ldap/_search
{
  "query": {
    "bool": {
      "filter": [{ "terms":  { "connection": [RESULTS_FROM_FIRST_QUERY] }}]
    }
  }
}

Hope this helps.

edited Nov 13 '16 at 12:04

answered Nov 11 '16 at 20:50

gerosalesc

2,983
3
27
46

I was planning to use Kibana. So basically you saying I need to write 'Application-Side join' in some other language(as my application is written Java, I will prefer Java). Am I getting you right ? – Bhushan Nov 15 '16 at 05:31
Yes, you got it right, regardless of the client application technology, keep in mind that this are the queries you should be using – gerosalesc Nov 15 '16 at 14:02

ElasticSearch : nested query in "IN" operator

1 Answers1