grandlyon: ajout script pour calcul des statistiques GLC
This commit is contained in:
parent
e7d4f39108
commit
fad153dc21
|
@ -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;
|
Loading…
Reference in New Issue