# EQL sequence performance issue

**URL:** https://discuss.elastic.co/t/eql-sequence-performance-issue/300601
**Category:** Elasticsearch
**Tags:** eql-elastic-query-language
**Created:** [March 24, 2022, 3:49pm UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601 "2022-03-24T15:49:45Z")
**Posts on this page:** 5
**Page:** 1

<div class="post-metadata">

### Author: ![eufe](https://avatars.discourse-cdn.com/v4/letter/e/919ad9/32.png) [@eufe](https://discuss.elastic.co/u/eufe)
#### Post date: [March 24, 2022, 3:49pm UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601/1 "2022-03-24T15:49:45Z")

</div>

Environnement :  
V7.16.2  
JVM : 31g (one node)  
RAM : 64G

Hello,

I'm trying to use EQL to detect some changes on a specific field.  
A concrete usecase would be to detect a regression on the administrative protocol on a network equipement.

I'm facing performance issues (around 21h to execute eql search)

In one index I'm stocking the state of the equipment yesterday and today.  
I have around 600k equipement so 1,2M of documents  
The documents look like this for one equipement (feuillet is the id of the equipement) :

```auto
      {
        "_index" : "test_kibana_alert4",
        "_type" : "_doc",
        "_id" : "RQKNvH8B3UBu97xvB-I2",
        "_score" : 12.169369,
        "_source" : {
          "type" : "test_eql2_bad_eqpts",
          "proto_cli" : "telnet",
          "feuillet" : "CDXX0",
          "@timestamp" : "2022-03-24T08:00:00.000Z",
          "event.category" : "host"
        }
      },
      {
        "_index" : "test_kibana_alert4",
        "_type" : "_doc",
        "_id" : "TwKNvH8B3UBu97xvB-I2",
        "_score" : 12.169369,
        "_source" : {
          "type" : "test_eql_bad_eqpts",
          "proto_cli" : "ssh",
          "feuillet" : "CDXX0",
          "@timestamp" : "2022-03-23T08:00:00.000Z",
          "event.category" : "host"
        }
      }

```

I am trying to use this kind of eql query :

```auto
sequence by feuillet
     [host where proto_cli == "ssh"] 
     [host where proto_cli == "telnet"]

```

And it take around 21h to execute without customisation and if we increase the fetch\_size to 50k it take around 30 min that is clearly better but not useable with more usecases in //

Here the steps to reproduce :

Set the mapping :

```auto
PUT test_kibana_alert4
{
  "mappings": {
    "properties": {
      "event.category": {
        "type": "keyword"
      },
      "@timestamp": {
        "type": "date"
      },
      "proto_cli": {
        "type": "keyword"
      },
      "feuillet": {
        "type": "keyword"
      }
    }
  }
}

```

Set this logsatsh configuration :

```auto
input
{
    generator {
        count => 600000
        type => "test_eql_good_eqts"
    }
    generator {
        count => 10
        type => "test_eql_bad_eqpts"
    }

}
filter
{   
    if [type] =~ "test_eql_good_eqts"
    {
        if [sequence] < 10 
        {
            drop {}
        }
        clone
        {
            clones => ["test_eql2_good_eqts"]
        }
        mutate 
        {
            replace => { "event.category" => "host"
             "timestamp_bis" => "2022-03-23T08:00:00.000Z"
             "proto_cli" => "telnet"}
             replace => { "feuillet" => "CDXX%{sequence}" }
        }
        if [type] == "test_eql2_good_eqts" {
            mutate 
            {
                replace => { "timestamp_bis" => "2022-03-24T08:00:00.000Z"
                 "proto_cli" => "ssh" }
            }
        }        
    }
    if [type] =~ "test_eql_bad_eqpts"
    {
        
        clone
        {
            clones => ["test_eql2_bad_eqpts"]
        }
        mutate 
        {
            replace => { "event.category" => "host"
             "timestamp_bis" => "2022-03-23T08:00:00.000Z"
             "proto_cli" => "ssh"}
             replace => { "feuillet" => "CDXX%{sequence}" }
        }
        if [type] == "test_eql2_bad_eqpts" {
            mutate 
            {
                replace => { "timestamp_bis" => "2022-03-24T08:00:00.000Z"
                 "proto_cli" => "telnet" }
            }
        }        
    }
    if [type] =~ "test_eql" {
        date {
            match => ["timestamp_bis", "ISO8601"]
        }
        mutate 
        {
            remove_field => ["timestamp_bis","sequence" , "host", "message", "@version"]
        }
    }
}
output
{
    if [type] =~ "test_eql" {

        elasticsearch
        {
            hosts => "<host>"
            user => "<user>"
            password => "<pwd>"
            action => "index"
            index => "test_kibana_alert4"
            ssl => true
            ssl_certificate_verification => true
            cacert => "<ca_path>"
        }
    }
}

```

Execute this query :

```auto
GET /test_kibana_alert4/_eql/search
{
  "wait_for_completion_timeout": "1s",
  "query": """
    sequence by feuillet
     [host where proto_cli == "ssh"] 
     [host where proto_cli == "telnet"]
  """
}

```

Have I missed something ?  
Are there any features that I need to consider ?  
Any sugegstion are welcome 🙂

---

<div class="post-metadata">

### Author: ![Vincent\_Maury](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincent_maury/32/59973_2.png) [@Vincent\_Maury](https://discuss.elastic.co/u/Vincent_Maury)
#### Post date: [March 29, 2022, 2:19pm UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601/2 "2022-03-29T14:19:11Z")

</div>

Interested as well...

---

<div class="post-metadata">

### Author: ![Vincent\_Maury](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vincent_maury/32/59973_2.png) [@Vincent\_Maury](https://discuss.elastic.co/u/Vincent_Maury)
#### Post date: [March 29, 2022, 2:21pm UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601/3 "2022-03-29T14:21:22Z")

</div>

Might be related to [EQL: Sequence performance improvements · Issue #60833 · elastic/elasticsearch (github.com)](https://github.com/elastic/elasticsearch/issues/60833) ?

---

<div class="post-metadata">

### Author: ![eufe](https://avatars.discourse-cdn.com/v4/letter/e/919ad9/32.png) [@eufe](https://discuss.elastic.co/u/eufe)
#### Post date: [April 22, 2022, 9:16am UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601/4 "2022-04-22T09:16:35Z")

</div>

The support advice me to add a sort on the field with the higher cardinality :  
Here the new mapping :

```auto
PUT test_kibana_alert4
{
  "mappings": {
    "properties": {
      "event.category": {
        "type": "keyword"
      },
      "@timestamp": {
        "type": "date"
      },
      "proto_cli": {
        "type": "keyword"
      },
      "feuillet": {
        "type": "keyword"
      }
    }
  },
  "settings": {
    "number_of_replicas": 0,
    "index": {
      "sort.field": "feuillet",
      "sort.order": "asc"
    }
  }
}

```

And I have forcemerge the indice.

The time taken decrease from 21hours to 7 minutes !  
With that, if I increase fetch\_size param from eql query, it take around 1 min to execute.

For me this issue is resolved. I will close the case

---

<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: [May 20, 2022, 9:16am UTC](https://discuss.elastic.co/t/eql-sequence-performance-issue/300601/5 "2022-05-20T09:16:46Z")

</div>

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