Postgresql View Definitions for Keycloak Analytics Dashboard

keycloak_dashboard.sql

-- 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;