# Canvas SQL: Unexpected result of an SQL query **URL:** \ **Category:** Elasticsearch\ **Tags:** elastic-stack-sql\ **Created:** [December 22, 2020, 1:26pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386 "2020-12-22T13:26:29Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [December 22, 2020, 1:26pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/1 "2020-12-22T13:26:30Z")
Hello everybody, I am trying to display in canvas the users that have more than 10 authentication failed, so I am using this SQL query: ```auto SELECT COUNT(*) as result_count FROM ( SELECT user.name, COUNT(*) as result FROM "winlogbeat-*" WHERE event.category = 'authentication' AND event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ) ``` And I am getting this result: ```auto |result_count| |------------| | 29 | |------------| | 78 | |------------| | 13 | |------------| ``` and the expected result is: `3` Could you tell me please why I am getting this unexpected result ? Thanks for your help ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [December 23, 2020, 12:27am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/2 "2020-12-23T00:27:52Z")
Can you elaborate? What do you mean by --\> > [@Abdelhalim](#): > > and the expected result is: `3` ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [December 23, 2020, 8:01am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/3 "2020-12-23T08:01:08Z")
Thanks for your answer @AClerk, So I meant that as I have used `SELECT COUNT(*) as result_count` in the beginning of the query, I want to calculate the number of the rows which is `3`, and not display the rows ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [December 23, 2020, 8:12am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/4 "2020-12-23T08:12:44Z")
try ```auto select count(distinct user.name) from ... ``` ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [December 23, 2020, 8:31am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/5 "2020-12-23T08:31:11Z")
When I try ```auto SELECT COUNT(distinct user.name) as result_count FROM ( SELECT user.name, COUNT(*) as result FROM "winlogbeat-*" WHERE event.category = 'authentication' AND event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ) ``` I am getting this output: ```auto |result_count| |------------| | 1 | |------------| | 1 | |------------| | 1 | |------------| ``` ---
**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\ **Post date:** [December 23, 2020, 10:46pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/6 "2020-12-23T22:46:11Z")
Try adding a count?! It is hard without having the data... ```auto SELECT SUM(COUNT(distinct user.name)) as result_count FROM ( ``` ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [December 24, 2020, 8:07am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/7 "2020-12-24T08:07:58Z")
I tried it, and it doesn't work ```auto Error: function.aggregate.count cannot be cast to class ``` It's weird as normally the first query `SELECT COUNT(*) as result_count` should give one row in SQL ! ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [January 11, 2021, 2:11pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/8 "2021-01-11T14:11:11Z")
@Abdelhalim, Why the outer select? If you want the total count of those users, not grouping by `user.name` should give you what you need, right? > the expected result is: `3` Could it be that `event` contains arrays that contain both 'authentication' and 'logon-failed', but not for the same array element? That would result in a higher count than you expect, since the query effectively behaves like an `OR` instead of an `AND`. ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [January 12, 2021, 8:25am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/9 "2021-01-12T08:25:02Z")
Hi @bogdan.pintea, For me I want to count the users which have more than 10 failed authentications, so if I just use group by, it will just give me the number of failed authentication per user. (If I remember well the courses of SQL, as I didn't use SQL since a long time 😅) > [@bogdan.pintea](#): > > Could it be that `event` contains arrays that contain both 'authentication' and 'logon-failed', but not for the same array element? That would result in a higher count than you expect, since the query effectively behaves like an `OR` instead of an `AND` . I tried this one too : ```auto SELECT user.name, COUNT(*) as result FROM "winlogbeat-*" WHERE event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ``` and the result is like this: ```auto |--------------------------|---------------------------| | user.name | result | |--------------------------|---------------------------| | user_1 | 11 | | user_2 | 56 | | user_1 | 34 | ``` and when I try: ```auto SELECT COUNT(user.name) FROM ( SELECT user.name, COUNT(*) as result FROM "winlogbeat-*" WHERE event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ) ``` I get : ```auto |-------------------------| | COUNT_user.name | |-------------------------| | 11 | | 56 | | 34 | ``` ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [January 12, 2021, 8:12pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/10 "2021-01-12T20:12:18Z")
Ah, I see, I didn't read through initially, sorry. > [@Abdelhalim](#): > > I am trying to display in canvas the users that have more than 10 authentication failed You're after the count of users that each have more than 10 failed authentications, not the list of those users. That would require a count aggregation over the grouping aggregation, indeed. > [@Abdelhalim](#): > > Could you tell me please why I am getting this unexpected result ? Subselects have limited support in ES/SQL currently and as you could see in the [limitations](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select), the outer count is "flattened" and that's why you get again a list of counts and not the cardinality of the list. ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [January 13, 2021, 8:04am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/11 "2021-01-13T08:04:30Z")
Thanks for your answer @bogdan.pintea, So I will wait for the next releases to do that 😊 ---
**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\ **Post date:** [January 13, 2021, 1:33pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/12 "2021-01-13T13:33:06Z")
> [@Abdelhalim](#): > > So I will wait for the next releases to do that 😊 Opening an [issue on Github](https://github.com/elastic/elasticsearch/issues) would certainly help. Other users looking for this or a similar feature could weigh in. Thanks. 🙂 ---
**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\ **Post date:** [January 21, 2021, 6:29pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/13 "2021-01-21T18:29:41Z")
Hi @Abdelhalim If you are only trying to display a number you can use the below technique. Use the below SQL in a metric: ```auto SELECT user.name FROM "winlogbeat-*" WHERE event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ``` Then in the 'Display' tab either select **Count** or **Unique** from the dropdown. ---
**Author:** ![Abdelhalim](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@Abdelhalim](https://discuss.elastic.co/u/Abdelhalim)\ **Post date:** [January 22, 2021, 8:24am UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/14 "2021-01-22T08:24:40Z")
Thanks for your answer @preetish_P, by dong that: ```auto SELECT user.name, COUNT(*) as result FROM "winlogbeat-*" WHERE event.action = 'logon-failed' GROUP BY user.name HAVING result > 10 ``` I get the number of authentication failure for each user, and for me I would like to diplay the number of users, NB: I already created an issue on github, but they closed it by saying that they are not working to add this feature in the near futur > > > Elasticsearch version: 7.10.1 > Kibana version: 7.10.1 > OS version : Debian 10 > Description of the problem including expected versus actual behavior: > I would like to display... ---
**Author:** ![preetish\_P](https://avatars.discourse-cdn.com/v4/letter/p/77aa72/32.png) [@preetish\_P](https://discuss.elastic.co/u/preetish_P)\ **Post date:** [January 24, 2021, 2:49pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/15 "2021-01-24T14:49:31Z")
Hi @Abdelhalim The key is to select **Count** or **Unique** from 'Display' tab. Which will ensure that it counts the number of rows in the result set. If you are familiar with canvas expression language, you can use math function within the element body too. So output of my SQL is: ```auto |--------------------------| | user.name | |--------------------------| | user_1 | | user_2 | | user_1 | ``` And metric will show **3** Hope this is clear. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 21, 2021, 2:50pm UTC](https://discuss.elastic.co/t/canvas-sql-unexpected-result-of-an-sql-query/259386/16 "2021-02-21T14:50:00Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.