grandlyon: ajout script pour calcul des statistiques GLC

This commit is contained in:
Benjamin Dauvergne 2021-01-12 22:58:39 +01:00
parent e7d4f39108
commit fad153dc21
1 changed files with 258 additions and 0 deletions

View File

@ -0,0 +1,258 @@
-- Fichier de calcul des statistiques de base pour GLC
-- Sont calculés :
-- le nombre d'utilisateurs créés chaque jour par partenaire, service et mode de création
-- le nombre d'utilisateurs validés chaque jour par partenaire, service et mode de validation
-- une aggrégation par mois est aussi fournie.
SET search_path = moncompte_grandlyon_com;
CREATE OR REPLACE TEMPORARY VIEW creation_mode AS SELECT atv.id, atv.object_id AS user_id, (CASE WHEN atv.content = 'backoffice' THEN 'BO' ELSE atv.content END) AS value FROM authentic2_attributevalue AS atv JOIN authentic2_attribute AS at ON atv.attribute_id = at.id AND at.name = 'creation_mode' WHERE atv.content IS NOT NULL AND atv.content <> '';
CREATE OR REPLACE TEMPORARY VIEW creation_partner AS SELECT atv.id, atv.object_id AS user_id, atv.content AS value FROM authentic2_attributevalue AS atv JOIN authentic2_attribute AS at ON atv.attribute_id = at.id AND at.name = 'creation_partner' WHERE atv.content IS NOT NULL AND atv.content <> '';
CREATE OR REPLACE TEMPORARY VIEW validation_partner AS SELECT atv.id, atv.object_id AS user_id, atv.content AS value FROM authentic2_attributevalue AS atv JOIN authentic2_attribute AS at ON atv.attribute_id = at.id AND at.name = 'validation_partner' WHERE atv.content IS NOT NULL AND atv.content <> '';
CREATE OR REPLACE TEMPORARY VIEW validation_context AS SELECT atv.id, atv.object_id AS user_id, atv.content AS value FROM authentic2_attributevalue AS atv JOIN authentic2_attribute AS at ON atv.attribute_id = at.id AND at.name = 'validation_context' WHERE atv.content IS NOT NULL AND atv.content <> '';
CREATE OR REPLACE TEMPORARY VIEW validation_date AS SELECT atv.id, atv.object_id AS user_id, atv.content::date AS value FROM authentic2_attributevalue AS atv JOIN authentic2_attribute AS at ON atv.attribute_id = at.id AND at.name = 'validation_date' WHERE atv.content IS NOT NULL AND atv.content <> '';
CREATE OR REPLACE TEMPORARY VIEW creation_stats AS
SELECT
dates.day AS date,
ou.name AS partner,
service.name AS service,
creation_mode.value as creation_mode,
count(u.id) AS count
FROM
generate_series((select date_trunc('day', min(date_joined)) FROM custom_user_user), current_date, '1 day') AS dates(day)
LEFT JOIN
custom_user_user AS u
ON
date_trunc('day', u.date_joined) = dates.day
LEFT JOIN
a2_rbac_organizationalunit AS user_ou
ON
u.ou_id = user_ou.id AND user_ou.default IS TRUE
LEFT JOIN
creation_mode
ON
creation_mode.user_id = u.id
LEFT JOIN
creation_partner
ON
creation_partner.user_id = u.id
LEFT JOIN
authentic2_service AS service
ON
service.slug = creation_partner.value
LEFT JOIN
a2_rbac_organizationalunit AS ou
ON
ou.slug = creation_partner.value OR ou.id = service.ou_id
GROUP BY date, partner, service, creation_mode
ORDER BY date, partner, service, creation_mode;
CREATE OR REPLACE TEMPORARY VIEW creation_stats_by_months AS
SELECT
EXTRACT(year FROM date) as year,
EXTRACT(month FROM date) as month,
COALESCE(partner, 'aucun') as partenaire,
COALESCE(service, 'aucun') as service,
COALESCE(creation_mode, 'inconnu') as mode_de_creation,
SUM(count) as count
FROM creation_stats
GROUP BY
year,
month,
partenaire,
service,
mode_de_creation
ORDER BY
year,
month,
partenaire,
service,
mode_de_creation;
\COPY (SELECT * FROM creation_stats) TO 'creation-stats-by-day.csv' WITH DELIMITER ';' CSV HEADER;
--
\COPY (SELECT * FROM creation_stats_by_months) TO 'creation-stats-by-months.csv' WITH DELIMITER ';' CSV HEADER;
CREATE OR REPLACE TEMPORARY VIEW validation_stats AS
SELECT
dates.day AS date,
ou.name AS partner,
service.name AS service,
vmode.value as validation_mode,
COUNT(u.id) AS count
FROM
generate_series((select date_trunc('day', min(date_joined)) FROM custom_user_user), current_date, '1 day') AS dates(day)
LEFT JOIN
validation_date AS vdate
ON
vdate.value = (dates.day::date)
LEFT JOIN
custom_user_user AS u
ON
vdate.user_id = u.id
LEFT JOIN
validation_context AS vmode
ON
vmode.user_id = u.id
LEFT JOIN
validation_partner
ON
validation_partner.user_id = u.id
LEFT JOIN
authentic2_service AS service
ON
service.slug = validation_partner.value
LEFT JOIN
a2_rbac_organizationalunit AS ou
ON
ou.slug = validation_partner.value OR ou.id = service.ou_id
GROUP BY date, partner, service, validation_mode
ORDER BY date, partner, service, validation_mode;
CREATE OR REPLACE TEMPORARY VIEW validation_stats_by_months AS
SELECT
EXTRACT(year FROM date) as year,
EXTRACT(month FROM date) as month,
COALESCE(partner, 'aucun') as partenaire,
COALESCE(service, 'aucun') as service,
validation_mode as mode_de_validation,
SUM(count) as count
FROM validation_stats
WHERE validation_mode IS NOT NULL
GROUP BY
year,
month,
partenaire,
service,
mode_de_validation
ORDER BY
year,
month,
partenaire,
service,
mode_de_validation;
\COPY (SELECT * FROM validation_stats) TO 'validation-stats-by-day.csv' WITH DELIMITER ';' CSV HEADER;
--
\COPY (SELECT * FROM validation_stats_by_months) TO 'validation-stats-by-months.csv' WITH DELIMITER ';' CSV HEADER;
CREATE OR REPLACE TEMPORARY VIEW users_connected_recently AS
SELECT
inter AS since,
count(u.id) AS count
FROM (SELECT (INTERVAL '1 month' * GENERATE_SERIES(1, 36))) AS d(inter)
JOIN custom_user_user AS u ON u.last_login > (current_date - inter)
GROUP BY since
ORDER BY since;
\COPY (SELECT * FROM validation_stats_by_months) TO 'users-connected-recently-by-months.csv' WITH DELIMITER ';' CSV HEADER;
CREATE OR REPLACE TEMPORARY VIEW sso_stats_by_days AS
SELECT
date_trunc('day', timestamp)::date as date,
coalesce(ou.name, 'aucun') AS partenaire,
coalesce(service.name, 'aucun') AS service,
COUNT(ev.user_id) AS count
FROM journal_event AS ev
JOIN journal_eventtype AS evt ON ev.type_id = evt.id
LEFT JOIN
authentic2_service AS service
ON
service.id = (reference_ids[1] % (2^32)::bigint)
LEFT JOIN
a2_rbac_organizationalunit AS ou
ON
ou.id = service.ou_id
WHERE evt.name = 'user.service.sso'
GROUP BY date, partenaire, service
ORDER BY date, partenaire, service;
\COPY (SELECT * FROM sso_stats_by_days WHERE date > (current_date - interval '3 months')) TO 'sso-by-days.csv' WITH DELIMITER ';' CSV HEADER;
CREATE OR REPLACE TEMPORARY VIEW oidc_authz_by_service AS
SELECT
auth.id,
auth.created,
auth.user_id,
auth.client_id AS service_id
FROM
authentic2_idp_oidc_oidcauthorization AS auth
JOIN
django_content_type AS ct
ON
ct.model = 'oidcclient' AND ct.id = auth.client_ct_id
WHERE
auth.expired > current_date;
CREATE OR REPLACE TEMPORARY VIEW oidc_authz_by_ou AS
SELECT
auth.id,
auth.created,
auth.user_id,
auth.client_id AS ou_id
FROM
authentic2_idp_oidc_oidcauthorization AS auth
JOIN
django_content_type AS ct
ON
ct.model = 'organizationalunit' AND ct.id = auth.client_ct_id
WHERE
auth.expired > current_date;
CREATE OR REPLACE TEMPORARY VIEW authorization_stats_by_service AS
SELECT
service_ou.name AS partenaire,
service.name AS service,
COUNT(u.id) AS count
FROM
custom_user_user AS u
JOIN
oidc_authz_by_service AS auths
ON
auths.user_id = u.id
LEFT JOIN
authentic2_service AS service
ON
service.id = auths.service_id
LEFT JOIN
a2_rbac_organizationalunit AS service_ou
ON
service_ou.id = service.ou_id
GROUP BY
ROLLUP(partenaire, service)
ORDER BY
partenaire, service;
CREATE OR REPLACE TEMPORARY VIEW authorization_stats_by_ou AS
SELECT
ou.name AS partenaire,
COUNT(u.id) AS count
FROM
custom_user_user AS u
JOIN
oidc_authz_by_ou AS authou
ON
authou.user_id = u.id
LEFT JOIN
a2_rbac_organizationalunit AS ou
ON
ou.id = authou.ou_id
GROUP BY
partenaire
ORDER BY
partenaire;
\COPY (SELECT * FROM authorization_stats_by_service) TO 'authz-by-services.csv' WITH DELIMITER ';' CSV HEADER;
\COPY (SELECT * FROM authorization_stats_by_ou) TO 'authz-by-ou.csv' WITH DELIMITER ';' CSV HEADER;