Group by function is not working in sql query in kibana>dev tools

Elastic Stack Kibana
#1

I am creating a query in kibana>dev tools.
Some how my SQL query is not working due to group by function in console.
The SQL query is working fine in MSSQL management studio.

Query:
POST /_sql?format=txt&pretty
{
"query": """

  Select loginyear,loginmonth,customerId,userId, count(1) customerwiseregularuser
 from
 (
  SELECT   customerId,userId,time,
  DATETIME_FORMAT(CAST(time AS DATE), 'YYYY-MM-dd') AS "logindate",
  DATETIME_FORMAT(CAST(time AS DATE), 'MM') AS "loginmonth" ,
  DATETIME_FORMAT(CAST(time AS DATE), 'YYYY') AS "loginyear"
  FROM "isco-regularusers" )a
  GROUP BY loginyear,loginmonth,customerId,userId having count(1) >5
  ORDER BY customerId,userId, customerwiseregularuser
  
  """
}

Please help for the error.

query
query1920×706 43.1 KB

#2

Hello Anjali,

Kibana by the box doesn't work with SQL.

You will need to use - https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-jdbc.html

Thanks
Bhavya

#3

Elasticsearch SQL does not support all of the things MSSQL supports - I think in this case you are running into this limitation: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-limitations.html#_using_a_sub_select

#4

So how i can achieve this logical query or conditions in kibana??

Thanks

#5

Okay thanks, so how i can achieve this complex conditions in kibana?

What i have to do in elasticsearch?

Thanks

#6

Try something like this and avoid nested SQL queries

POST /_sql?format=txt&pretty
{
  "query": """
  SELECT   customerId,userId,
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY-MM-dd') AS "logindate",
  DATETIME_FORMAT(CAST(login_date AS DATE), 'MM') AS "loginmonth" ,
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY') AS "loginyear",
  count(*) as login_count
  FROM "users-logon-data"
  GROUP BY 
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY-MM-dd'),
  DATETIME_FORMAT(CAST(login_date AS DATE), 'MM'),
  DATETIME_FORMAT(CAST(login_date AS DATE), 'YYYY'),
  customerId,userId having count(*) >= 1
  order by customerId, userId
  """
}
#7

The query i working, but i am not getting the desire the result what need.

I need the count of user who have login more then 5times in different days for the same month.

Requirement to create the query:
The number of regular users every month, regular users are those who have logged in on 5 different days in the same month (A user could log in several times on the same day, still would count as one).

For this we have the fields which was mentioned in the above query given in the use case.

Thanks

#8

Please guide me to function the jdbc driver use in elasticsearch.

  1. how to configure in Elasticsearch
    2.how to excute the query in it.

Thanks

#9

As this is a separate question, please open another topic so people will be able to follow