Elasticsearch query to check last entries


#1

Hi,

I am using ELK GA 6.3.0. I have the below query;

"body": {
  "query": {
	"bool": {
	  "filter": {
		"range": {
		  "@timestamp": {
			"gte": "now-30s/s",
			"lte": "now/s",
			"format": "epoch_millis"
		  }
		}
	  },
	  "must": {
		"match": {
		  "gender": "boy"
		}
	  }
	}
  },
  "size": 0,
  "aggs": {
	"dateAgg": {
	  "date_histogram": {
		"field": "@timestamp",
		"time_zone": "GMT",
		"interval": "30s",
		"min_doc_count": 3
	  }
	}
  }
}

I have an index of students. In my index, there is an attribute named class (1, 2, and 3 are the possible values). I want to check if the last 3 entries in last 30s is boy. The query work well. But the problem is, it returns true if 3 boys are present in class 1 or 2 or 3. What I want is, return true, if the last 3 consecutive entries of one of the class is 3. Means, return true if last 3 entries of class 1 is boy. return true if last 3 entries of class 2 is boy, like that. Return false if last 3 entries is like boy,boy,girl or boy,girl,boy etc. Is this possible?

Thanks.


(Byron Voorbach) #2

Could you show a part of your mapping and some sample data?
Makes it a bit easier to help you out :slight_smile:


#3

Hi @byronvoorbach , below is the mapping;

{
  "students": {
    "mappings": {
      "doc": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "gender": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "class": {
            "type": "integer"
          },
          "name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "age": {
            "type": "integer"
          },
          "type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

Sample data, is, you can imagine a student entry with name, age, class, and gender (boy / girl). Type will have an alphabet "a" in it.

Thanks.


#5

Somebody kindly help


(David Pilato) #6

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

I believe this is what @byronvoorbach was expecting here.