keycloak debugging user sessions - query offline session generated for a user sorted by creation time
The purpose of this query is to figure out the offline session created for the user by time interval sorted
In keycloak, previous offline session gets invalidated when a new token gets generated. So if the previou session matches then we know user or app follows a proper sequential sequence to get a new tokem. However, if a app misbehave then this sequential pattern will not come out in the table results.
SELECT
r.name AS realm_name,
u.username,
c.client_id,
ous.id AS offline_user_session_id,
ocs.id AS offline_client_session_id,
ocs.offline_flag,
ocs.offline_token,
to_timestamp(ocs.created_on) AS created_at,
LAG(ocs.offline_token) OVER (PARTITION BY u.id ORDER BY ocs.created_on) AS previous_token,
LAG(to_timestamp(ocs.created_on)) OVER (PARTITION BY u.id ORDER BY ocs.created_on) AS previous_created_at,
(ocs.created_on - LAG(ocs.created_on) OVER (PARTITION BY u.id ORDER BY ocs.created_on)) / 60 AS minutes_since_previous
FROM
user_entity u
JOIN realm r
ON r.id = u.realm_id
JOIN offline_user_session ous
ON ous.user_id = u.id
JOIN offline_client_session ocs
ON ocs.offline_user_session_id = ous.id
JOIN client c
ON c.id = ocs.client_id
WHERE
u.id = '<USER_ID_HERE>'
AND ocs.offline_flag = TRUE
ORDER BY
ocs.created_on ASC;
Comments