How to Generate User Statistics Queries using Keycloak

Sometimes we want to see how many users has registered to our Keycloak, how many login per-hours, how many failed logins, and other statistical data for multiple purposes.

We can a use sample queries below for generating those reports. But first we need to enable events for that corresponding realm,

Once we turn Keycloak events on, we can run below queries to populate the required results

## get total number of successful login
select count(1) from EVENT_ENTITY where TYPE='LOGIN';

## get total number of failed login
select count(1) from EVENT_ENTITY where TYPE='LOGIN_ERROR';

## get user's all activity
select USER_ENTITY.USERNAME, EVENT_ENTITY.* 
from USER_ENTITY, EVENT_ENTITY where EVENT_ENTITY.USER_ID = USER_ENTITY.ID
order by USER_ENTITY.USERNAME, EVENT_TIME;

Pretty simple right 🙂