Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgresql View Definitions for Keycloak Analytics Dashboard
-- View: analytics_event_count
-- DROP VIEW analytics_event_count;
CREATE OR REPLACE VIEW analytics_event_count AS
WITH event_data AS (
SELECT ee.realm_id,
timezone('UTC'::text, '1970-01-01 00:00:00'::timestamp without time zone + (ee.event_time / 1000)::double precision * '00:00:01'::interval)::date AS event_date,
ee.type AS event_type,
ee.user_id
FROM event_entity ee
), aggregated_event_data AS (
SELECT ed.realm_id,
ed.event_date,
ed.event_type,
count(ed.user_id) AS event_count
FROM event_data ed
GROUP BY ed.realm_id, ed.event_date, ed.event_type
)
SELECT aed.realm_id,
aed.event_date,
aed.event_type,
aed.event_count
FROM aggregated_event_data aed;
ALTER TABLE analytics_event_count
OWNER TO postgres;
GRANT ALL ON TABLE analytics_event_count TO postgres;
GRANT SELECT ON TABLE analytics_event_count TO public;
-- View: analytics_latest_events
-- DROP VIEW analytics_latest_events;
CREATE OR REPLACE VIEW analytics_latest_events AS
WITH events AS (
SELECT ee.realm_id,
ee.user_id,
'1970-01-01 00:00:00'::timestamp without time zone + (ee.event_time / 1000)::double precision * '00:00:01'::interval AS event_timestamp,
ee.type AS event_type
FROM event_entity ee
), annotated_events AS (
SELECT e.realm_id,
e.user_id,
ue.username,
e.event_timestamp,
e.event_type,
row_number() OVER (PARTITION BY e.realm_id, e.event_type ORDER BY e.event_timestamp DESC) AS event_order,
lead(e.event_timestamp) OVER (PARTITION BY e.realm_id, e.event_type, e.user_id ORDER BY e.event_timestamp DESC) AS prev_event_timestamp
FROM events e
JOIN user_entity ue ON e.user_id::text = ue.id::text
)
SELECT ae.realm_id,
ae.user_id,
ae.username,
ae.event_timestamp,
ae.event_type,
ae.event_order,
ae.prev_event_timestamp
FROM annotated_events ae;
ALTER TABLE analytics_latest_events
OWNER TO postgres;
GRANT ALL ON TABLE analytics_latest_events TO postgres;
GRANT SELECT ON TABLE analytics_latest_events TO public;
-- View: dashboard_summary
-- DROP VIEW dashboard_summary;
CREATE OR REPLACE VIEW dashboard_summary AS
WITH realmevents AS (
SELECT event_entity.id,
event_entity.client_id,
event_entity.details_json,
event_entity.error,
event_entity.ip_address,
event_entity.realm_id,
event_entity.session_id,
event_entity.event_time,
event_entity.type,
event_entity.user_id
FROM event_entity
), eventstatslast7days AS (
SELECT realmevents.realm_id,
sum(
CASE
WHEN realmevents.type::text = 'LOGIN'::text THEN 1
ELSE 0
END) AS logincount,
sum(
CASE
WHEN realmevents.type::text = 'REGISTER'::text THEN 1
ELSE 0
END) AS registercount
FROM realmevents
WHERE (realmevents.type::text = ANY (ARRAY['LOGIN'::character varying, 'REGISTER'::character varying]::text[])) AND realmevents.event_time > date_part('epoch'::text, now() - '7 days'::interval)::bigint
GROUP BY realmevents.realm_id
), eventstatslast30days AS (
SELECT realmevents.realm_id,
count(DISTINCT realmevents.user_id) AS activeuserscount
FROM realmevents
WHERE realmevents.type::text = 'LOGIN'::text AND realmevents.event_time > date_part('epoch'::text, now() - '30 days'::interval)::bigint
GROUP BY realmevents.realm_id
), userstats AS (
SELECT user_entity.realm_id,
count(1) AS totalusercount
FROM user_entity
GROUP BY user_entity.realm_id
)
SELECT us.realm_id,
esl7d.logincount,
esl7d.registercount,
esl30d.activeuserscount,
us.totalusercount
FROM eventstatslast7days esl7d
JOIN userstats us ON esl7d.realm_id::text = us.realm_id::text
JOIN eventstatslast30days esl30d ON esl30d.realm_id::text = us.realm_id::text;
ALTER TABLE dashboard_summary
OWNER TO postgres;
GRANT ALL ON TABLE dashboard_summary TO postgres;
GRANT SELECT ON TABLE dashboard_summary TO public;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment