Canvas SQL: Unexpected result of an SQL query

[Abdelhalim]

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:

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:

    |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

[AClerk]

Can you elaborate?
What do you mean by -->

@Abdelhalim

and the expected result is: 3

[Abdelhalim]

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

[AClerk]

try

select count(distinct user.name) from
...

[Abdelhalim]

When I try

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:

    |result_count|
    |------------|
    |     1      |
    |------------|
    |     1      |
    |------------|
    |     1      |
    |------------|

[AClerk]

Try adding a count?! It is hard without having the data...

SELECT SUM(COUNT(distinct user.name)) as result_count 
FROM (

[Abdelhalim]

I tried it, and it doesn't work

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 !

[bogdan.pintea]

@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.

[Abdelhalim]

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 :

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:

|--------------------------|---------------------------|
|        user.name         |          result           |
|--------------------------|---------------------------|
|          user_1          |             11            |  
|          user_2          |             56            |  
|          user_1          |             34            |  

and when I try:

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 :

|-------------------------|
|     COUNT_user.name     |
|-------------------------|
|            11           |  
|            56           |  
|            34           |  

[bogdan.pintea]

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, the outer count is "flattened" and that's why you get again a list of counts and not the cardinality of the list.

[Abdelhalim]

Thanks for your answer @bogdan.pintea,
So I will wait for the next releases to do that

[bogdan.pintea]

@Abdelhalim

So I will wait for the next releases to do that

Opening an issue on Github would certainly help. Other users looking for this or a similar feature could weigh in.
Thanks.

[preetish_P (Preetish Pai)]

Hi @Abdelhalim

If you are only trying to display a number you can use the below technique.
Use the below SQL in a metric:

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.

[Abdelhalim]

Thanks for your answer @preetish_P,

by dong that:

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

Canvas SQL: Unexpected result of an SQL query

[preetish_P (Preetish Pai)]

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:

|--------------------------|
|        user.name         |        
|--------------------------|
|          user_1          |               
|          user_2          |               
|          user_1          |          

And metric will show 3

Hope this is clear.