Complex SQL query calculated field generation in kibana

Elastic Stack Kibana
1

Hello,
We want to created a visualization based on regular user in every month.
Here is the requirement:
"The visualization would display 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)."

we have generated a sql query in MSSQL by below query:

Select loginyear,loginmonth,CustomerId, UserId, count(1) cutomerwiseregularuser
from
(
select distinct ru.CustomerId, ru.UserId,convert(date, ru.time) as logindate, MONTH(ru.time) loginmonth, year(ru.time) loginyear
from Regularuser as ru ) a
group by loginyear,loginmonth,CustomerId, UserId having count(1) > 5

How can i achieve in kibana visualization to display into dashboard.
Please help

2

Create a date field (i.e. timestamp without the time).
Then count distinct the usernames.
Filter out where count < 5

Hope it will give you an idea

3

can you please tell me where to create the date field and how to applie the query in kibana?

4

Create it as a custom field under index patterns. Or better if you can do that before loading the data into your index, and create another field.
Then create a visualisation of your choice.

5

We cannot create in index as that is from client side, so we have the option in custom field.

are you talking about scripted field in index pattern?
Also which function i can use to get distinct result for username?

Thanks

6

yes scripted field.
you can use unique values when creating the visualisation.

7

unique count gives the distinct count in each month userwise, then i am unable to check which user are my regular user according to above explain in MSSQL query.

Please we need those user who have login more then 5times in different days for the month.

how we can achieve in scripted field explain deep.

Thanks

8

@TimV could you help me with the above case?

Regards

9

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