combine output of a a first filter as input of a second filter

user_291417 0
Viewed 178

We have an elasticsearch instance with entries with two tagged fields.

  • sessionid
  • message

In a first filter, I find all entries where the message contains a certain substring. Each of those entries contains a sessionid,

In a second filter, I want to find all messages, where the sessionid matches one of the sessionids returned by the first filter. This filter should go through all entries a second time.

Example, in the log below (sessionid;message)

1234;miss 1
2456;miss 2
1234;match

When filtering for the string "match" in the message part, I would get as output of the combined query:

1234;miss 1
1234;match

We are using KQL.

Background: We want an easy way to follow complete flows with an error-string in a message, in a multithreaded environment.

elasticsearch
2 Answers

I understand why you'd want to do that in one go but it's not possible in ElasticSearch. You cannot "revisit" documents which you've already ruled out by a different query -- searching for match would disqualify all misss.

It's unfortunate you have the log message combined with the ID but you can try this:

  1. Find all that match match (pun intended) -- I'm assuming you do have a keyword field available
GET your_index/_search
{
  "query": {
    "regexp": {
      "separated_msg.keyword": ".*\\;match.*"
    }
  }
}

  1. Post-process the hits and extract the session IDs

  2. Run session ID matching:

GET your_index/_search
{
  "query": {
    "regexp": {
      "separated_msg.keyword": "1234;.*"
    }
  }
}

or on multiple IDs using a bool should: 

GET your_index/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "regexp": {
            "separated_msg.keyword": "1234;.*"
          }
        },
        {
          "regexp": {
            "separated_msg.keyword": "4567;.*"
          }
        }
      ]
    }
  }
}
user_81603180

If a unique numeric value can be assigned to each message ex 1 for "match", 2 for "miss 1" then bucket selector and top_hits can be used. 

{
  "size": 0,
  "aggs": {
    "sessionid": {
      "terms": {
        "field": "sessionid",   --> first get all unique sessionids
        "size": 10
      },
      "aggs": {
        "documents":{
          "top_hits": {
            "size": 10
          }
        },
        "messageid": {
          "terms": {
            "field": "messageid",   ---> get unique sessionId
            "size": 10
          },
          "aggs": {
            "matching_messageid": {  ---> select a bucket with key(message Id) as 2
              "bucket_selector": {
                "buckets_path": {
                  "key": "_key"
                },
                "script": "params.key==2"
              }
            }
          }
        },
        "my_bucket": {
          "bucket_selector": {
            "buckets_path": {
              "hits": "messageid._bucket_count"
            },
            "script": "params.hits>0"--> if bucket not empty then consider that sessionid
          }
        }
      }
    }
  }
}

Result

  "aggregations" : {
    "sessionid" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1234,
          "doc_count" : 2,
          "documents" : {
            "hits" : {
              "total" : {
                "value" : 2,
                "relation" : "eq"
              },
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "index31",
                  "_type" : "_doc",
                  "_id" : "MTAYpnABheSAx2q_eNEF",
                  "_score" : 1.0,
                  "_source" : {
                    "sessionid" : 1234,
                    "message" : "miss 1",
                    "messageid" : 1
                  }
                },
                {
                  "_index" : "index31",
                  "_type" : "_doc",
                  "_id" : "MjAYpnABheSAx2q_n9FW",
                  "_score" : 1.0,
                  "_source" : {
                    "sessionid" : 1234,
                    "message" : "match",
                    "messageid" : 2
                  }
                }
              ]
            }
          },
          "messageid" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : 2,
                "doc_count" : 1
              }
            ]
          }
        }
      ]
    }
  }

If a given message has timestamp(max/min) then max_path can be used to select buckets with given messages.

The best approach to above problem will be to use nested documents

{
   "sessionid":1234,
   "messages":[
                 {
                     "message":"match"
                 },
                 {
                     "message":"miss 1"
                 }
    ]
}
````
then the problem can be resolved by nested query. If logstash is used then above structure can generated while indexing.
user_92793540
Related