Grouper Users - Open Discussion List

[Mathieu HETRU <>]

Hello,

I search how get the max created_on date for a custom attribute from a grouper uuid on the grouper_change_log_entry_v view.

The custom attributes are :

etc:legacy:attribute:legacyAttribute_date-fin

etc:legacy:attribute:legacyAttribute_cn-ldap

etc:legacy:attribute:legacyAttribute_nom-liste

etc:legacy:attribute:legacyAttribute_type-liste

etc:legacy:attribute:legacyAttribute_groupe-sender

The query is :

SELECT gv.group_name AS code_groupe,
g.description,
gv.group_id AS id_ref_si,
(
SELECT string_agg(lm.subject_id, ';')
FROM grouper_memberships_lw_v AS lm
WHERE lm.list_name='members'
AND lm.group_name=gv.group_name
GROUP BY lm.group_name
) AS liste_membres,
(
SELECT string_agg(lr.subject_id, ';')
FROM grouper_memberships_lw_v AS lr
WHERE lr.list_name='readers'
AND lr.group_name=gv.group_name
GROUP BY lr.group_name
) AS liste_responsables,
(
SELECT cl.value_string
FROM grouper_aval_asn_asn_group_v AS cl
WHERE cl.attribute_def_name_name2='etc:legacy:attribute:legacyAttribute_date-fin'
AND cl.group_name=gv.group_name
) AS date_fin,
(
SELECT mt.create_time
FROM grouper_groups AS mt
WHERE mt.id=gv.group_id
) AS date_creation,
(
SELECT MAX(t1.created_on)
FROM
(
SELECT MAX(created_on) as created_on
FROM grouper_change_log_entry_v
WHERE action_name IN ('addGroup', 'updateGroup', 'deleteGroup')
AND change_log_category='group'
AND string01=gv.group_id
UNION
SELECT MAX(created_on) as created_on
FROM grouper_change_log_entry_v
WHERE action_name IN ('addMembership', 'deleteMembership')
AND change_log_category='membership'
AND label_string06='groupId'
AND string06=gv.group_id
UNION
SELECT MAX(created_on) as created_on
FROM grouper_change_log_entry_v
WHERE action_name IN ('addPrivilege', 'deletePrivilege')
AND change_log_category='privilege'
AND label_string06='ownerType'
AND string06='group'
AND label_string07='ownerId'
AND string07=gv.group_id
UNION
SELECT modify_time AS created_on
FROM grouper_groups AS mt
WHERE mt.id=gv.group_id
) as t1
) AS date_modification,
(
SELECT cl.value_string
FROM grouper_aval_asn_asn_group_v AS cl
WHERE cl.attribute_def_name_name2='etc:legacy:attribute:legacyAttribute_cn-ldap'
AND cl.group_name=gv.group_name
) AS cn,
(
SELECT nl.value_string
FROM grouper_aval_asn_asn_group_v AS nl
WHERE nl.attribute_def_name_name2='etc:legacy:attribute:legacyAttribute_nom-liste'
AND nl.group_name=gv.group_name
) AS nom_liste_diffusion,
(
SELECT tl.value_string
FROM grouper_aval_asn_asn_group_v AS tl
WHERE tl.attribute_def_name_name2='etc:legacy:attribute:legacyAttribute_type-liste'
AND tl.group_name=gv.group_name
) AS type_liste_diffusion,
(
SELECT gs.value_string
FROM grouper_aval_asn_asn_group_v AS gs
WHERE gs.attribute_def_name_name2='etc:legacy:attribute:legacyAttribute_groupe-sender'
AND gs.group_name=gv.group_name
) AS liste_groupe_sender,
(
SELECT string_agg(ll.subject_id, ';')
FROM grouper_memberships_lw_v AS ll
WHERE ll.list_name='readers'
AND ll.group_name=gv.group_name
GROUP BY ll.group_name
) AS liste_lecteurs
FROM grouper_memberships_lw_v AS gv, grouper_groups AS g
WHERE gv.group_id = g.id
AND g.type_of_group = 'group'
AND g.name LIKE 'UDL:%'
GROUP BY gv.group_id, gv.group_name, g.description;

Thanks for your help!

Mathieu

--



Mathieu HETRU
Responsable du bureau Web/ENT

Université de Lille - Campus Cité Scientifique
Direction des Systèmes d'Information
Service Intégration / Exploitation
Cellule Web/ENT

Bureau 47 - Bâtiment A3
Domaine universitaire de la Cité Scientifique
Avenue Carl Von Limé
BP 90179 59653 Villeneuve d'Ascq

Tél. :+33 (0)3 62 26 83 81 (numéro interne : 68381)

| www.univ-lille.fr