# Failed execution of ESQL query and high cpu load **URL:** \ **Category:** Elastic Security\ **Created:** [November 13, 2023, 10:17am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992 "2023-11-13T10:17:52Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 10:17am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/1 "2023-11-13T10:17:52Z")
Hi There, I have a one node cluster here for testing. Hardware Spec: 16 Core // 148GB // 6 SAS 15K Raid0 Everything is running smoothly except for Elastic Security. As soon as i perform an action here, i get Kibana timeouts (50sec) and also several error messages regarding ESQL query and a load1 with 7 - 9. ```auto [2023-11-13T10:41:51,722][INFO][o.e.x.e.a.EsqlResponseListener] [elastic01] Failed execution of ESQL query. Query string: [from .alerts-security.alerts-default,apm-*-transaction*,auditbeat-*,endgame-*,filebeat-*,logs-*,packetbeat-*,traces-apm*,winlogbeat-*,-*elastic -cloud-logs-* | limit 10] Execution time: [55969]ms ``` There are only 150 elastic original rules active. Currently only 7 Elastic Agents are rolled out, i think that the hardware used should be sufficient. Why is there such a strong loss of performance with Elastic Security? Anyone have any ideas? ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 10:28am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/2 "2023-11-13T10:28:06Z")
Hello, Would like to know if the performance get better if remove **logs-** \* or query only one index pattern at a time? ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 10:33am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/3 "2023-11-13T10:33:38Z")
Hi Angela, thanks for your quick reply. No, i hadn't tested that yet. Is it possible to see from the log file why the query fails or takes so long? ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 11:14am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/4 "2023-11-13T11:14:43Z")
We might not always able to find the cause from the log file. By querying the index pattern one by one could allow us to identify the pattern that is slower that expected. We've experienced some cases that **logs-** \* slows down the performance overall, therefore I suggested started from removing logs-\* from the query and see if it improves. ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 11:23am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/5 "2023-11-13T11:23:26Z")
OK, I'll give it a try. Can you please show / explain me, where i can remove the logs-\* ? ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 12:49pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/6 "2023-11-13T12:49:02Z")
1. In /app/management/kibana/dataViews, find the one with tag `Security Data View`, click on it. Select `Edit`, remove `logs-*,` from both `Name` field and `Index pattern` field 2. Visit /app/security/timelines , create a new timeline or update an existing timeline, click ES|QL tab, remove `logs-*` from the query and click `Update`. 3. Whenever you use ESQL query, try not to include `logs-*` in the query and observe if the performance improved. * * * If you'd like to know more about Data view: [Create a Data view | Kibana Guide [8.11] | Elastic](https://www.elastic.co/guide/en/kibana/current/data-views.html) ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 12:59pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/7 "2023-11-13T12:59:24Z")
Angela wow, 😀 that had been the problem, from 50sec and more of execution time we are now at 100ms on average. That is a significant step forward. You should open a ticket if you haven't already done so. Thank you for your help. Stefan ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 2:00pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/8 "2023-11-13T14:00:59Z")
Rejoiced a little too soon. For some reason i don't understand, `logs-*` was added again in Data View. Is there an automatism that does this (new index etc.) ? ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 3:45pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/9 "2023-11-13T15:45:29Z")
There's another place you can remove logs-\* from data view. In Security Solution app, there should be a `data view` dropdown on the top-right corner, we can de-select `logs-*` from there as well. > **[Data views in Elastic Security | Elastic Security Solution \[8.11\] | Elastic](https://www.elastic.co/guide/en/security/current/data-views-in-sec.html)** ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 3:59pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/10 "2023-11-13T15:59:09Z")
Angela, Yes OK, but the main problem is that for some reason `log-*` is automatically added to the data view again. I have opened a ticket here so that this bug can be investigated, in the hope that this problem will be solved soon. It's no fun to use the security app because everything that has to do with the security app is so slow. [[Security Solution] Failed execution of ESQL query and high cpu load, Security Solution not usable · Issue #171108 · elastic/kibana (github.com)](https://github.com/elastic/kibana/issues/171108) ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 4:15pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/11 "2023-11-13T16:15:10Z")
Thanks so much for doing this, I've notified the team that's in charge of this feature. At the mean time, if you deselect `logs-*` from the Data view dropdown in security app. The deselected item wouldn't come back again until you re-select it. ![Screenshot 2023-11-13 at 16.10.16](https://us1.discourse-cdn.com/elastic/original/3X/e/c/ec689684ed8a525ab0942c36545ac97d9c42420f.png) ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 13, 2023, 4:15pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/12 "2023-11-13T16:15:42Z")
Angela, if I remove logs-\* from hosts, for example, i no longer have any data to display...... ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 4:23pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/13 "2023-11-13T16:23:57Z")
As logs-\* includes all the indices match this pattern. I'd recommend using GET /logs-\*/\_stats in dev tools to identify which indices you'd like to query from. Create a new data view that includes only those indices, so it'd be easier to tell which are the problematic ones. ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 13, 2023, 4:56pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/14 "2023-11-13T16:56:56Z")
I've looked into the reason why logs-\* added back to SecuritySolution data view. That's because we didn't change the default it uses in here: Visit /app/management/kibana/settings?query=category:(securitySolution) Remove logs-\* from `Elasticsearch indices`. Then we should be able to visit the /app/management/kibana/dataViews and removing the logs-\* from there. When landing on Security app again, the logs-\* shouldn't be added back automatically. ![Screenshot 2023-11-13 at 16.50.45](https://us1.discourse-cdn.com/elastic/original/3X/5/4/54a4ae0799007408b79d25687660abf9d8ddb04b.png) ---
**Author:** ![Stefan\_Sabolowitsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stefan_sabolowitsch/32/48680_2.png) [@Stefan\_Sabolowitsch](https://discuss.elastic.co/u/Stefan_Sabolowitsch)\ **Post date:** [November 14, 2023, 10:04am UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/15 "2023-11-14T10:04:51Z")
Angela, thank you for your time and effort, but this is not really a solution. So that i can see everything of relevance in the security app, i have added the indexes `logs-endpoint*,logs-sophos.*` instead of `logs-*`. Yes, it has become a little faster, especially with smaller times frames, but 24h takes several seconds until a result is there and i have a load1 of 9 and that with this hardware. In addition, the map is no longer displayed in the Security app under Network, supposedly an index is missing here. All in all, this is an unsatisfactory solution and your team should invest more time here so that the performance of the security app improves significantly. ---
**Author:** ![AngelaChuang](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/angelachuang/32/49719_2.png) [@AngelaChuang](https://discuss.elastic.co/u/AngelaChuang)\ **Post date:** [November 14, 2023, 4:04pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/16 "2023-11-14T16:04:54Z")
Thanks for the feedback, I've taken this issue back to the team and we're working on this. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 12, 2023, 4:05pm UTC](https://discuss.elastic.co/t/failed-execution-of-esql-query-and-high-cpu-load/346992/17 "2023-12-12T16:05:40Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.