User and Operator log in/out history

This SQL report retrieves the login and logout history of users, providing a detailed view of user activity. It selects records for login actions and associates them with the most recent corresponding logout actions. The report includes user details such as personal number, username, and lastname, as well as timestamps for both login and logout events. It also distinguishes between different types of logout actions, marking them as either system-logoutAction or endpoint-logoutAction. The results include additional information such as user IDs, token IDs, and token hashes, ensuring a comprehensive overview of user authentication events.

User log-in/out history

SQL

SELECT
	hf.id as id_login,
	ht.id as id_logout,
	CASE 
		WHEN hf.user_personal_number is not null THEN hf.user_personal_number
		ELSE ht.user_personal_number
	END AS user_personal_number,
	CASE 
		WHEN hf.user_name is not null THEN hf.user_name
		ELSE ht.user_name
	END AS user_name,
	CASE 
		WHEN hf.user_lastname is not null THEN hf.user_lastname
		ELSE ht.user_lastname
	END AS user_lastname,

	hf.time_created as time_login,
	ht.time_created as time_logout,
	CASE 
		WHEN ht.action LIKE 'endpoint-logoutAction' THEN 0
		WHEN ht.action LIKE 'system-logoutAction' THEN 1
		ELSE null
	END AS system_logout,

	CASE 
		WHEN hf.id_user is not null THEN hf.id_user
		ELSE ht.id_user
	END AS id_user,
	CASE 
		WHEN hf.id_token is not null THEN hf.id_token
		ELSE ht.id_token
	END AS id_token,
	CASE 
		WHEN hf.token_hash is not null THEN hf.token_hash
		ELSE ht.token_hash
	END AS token_hash
FROM user_auth_history hf
LEFT JOIN (
	SELECT *
	FROM user_auth_history
	WHERE id IN (
		SELECT min(id)
		FROM user_auth_history
		WHERE action LIKE '%-logoutAction'
		GROUP BY token_hash
	) r
) ht ON ht.token_hash = hf.token_hash AND ht.action LIKE '%-logoutAction'
WHERE hf.action LIKE '%-loginAction'

Operator log-in/out history

SQL

SELECT
	hf.id as id_login,
	ht.id as id_logout,
	CASE 
		WHEN hf.operator_personal_number is not null THEN hf.operator_personal_number
		ELSE ht.operator_personal_number
	END AS operator_personal_number,
	CASE 
		WHEN hf.operator_name is not null THEN hf.operator_name
		ELSE ht.operator_name
	END AS operator_name,
	CASE 
		WHEN hf.operator_lastname is not null THEN hf.operator_lastname
		ELSE ht.operator_lastname
	END AS operator_lastname,

	hf.time_created as time_login,
	ht.time_created as time_logout,
	CASE 
		WHEN ht.action LIKE 'endpoint-logoutAction' THEN 0
		WHEN ht.action LIKE 'system-logoutAction' THEN 1
		ELSE null
	END AS system_logout,

	CASE 
		WHEN hf.id_operator is not null THEN hf.id_operator
		ELSE ht.id_operator
	END AS id_operator,
	CASE 
		WHEN hf.id_token is not null THEN hf.id_token
		ELSE ht.id_token
	END AS id_token,
	CASE 
		WHEN hf.token_hash is not null THEN hf.token_hash
		ELSE ht.token_hash
	END AS token_hash
FROM operator_auth_history hf
LEFT JOIN (
	SELECT *
	FROM operator_auth_history
	WHERE id IN (
		SELECT min(id)
		FROM operator_auth_history
		WHERE action LIKE '%-logoutAction'
		GROUP BY token_hash
	) r
) ht ON ht.token_hash = hf.token_hash AND ht.action LIKE '%-logoutAction'
WHERE hf.action LIKE '%-loginAction'