Skip to Content.
Sympa Menu

grouper-users - [grouper-users] how get created_on date for change_log for a custom attribute from grouper_change_log_entry_v view

Subject: Grouper Users - Open Discussion List

List archive

[grouper-users] how get created_on date for change_log for a custom attribute from grouper_change_log_entry_v view


Chronological Thread 
  • From: Mathieu HETRU <>
  • To: "" <>
  • Subject: [grouper-users] how get created_on date for change_log for a custom attribute from grouper_change_log_entry_v view
  • Date: Mon, 7 May 2018 16:19:36 +0200
  • Ironport-phdr: 9a23:Ji0EphB3hrS00h/XcjxuUyQJP3N1i/DPJgcQr6AfoPdwSPT+osbcNUDSrc9gkEXOFd2Cra4c0KyO6+jJYi8p2d65qncMcZhBBVcuqP49uEgeOvODElDxN/XwbiY3T4xoXV5h+GynYwAOQJ6tL1LdrWev4jEMBx7xKRR6JvjvGo7Vks+7y/2+94fcbglUijexe69+IAmrpgjNq8cahpdvJLwswRXTuHtIfOpWxWJsJV2Nmhv3+9m98p1+/SlOovwt78FPX7n0cKQ+VrxYES8pM3sp683xtBnMVhWA630BWWgLiBVIAgzF7BbnXpfttybxq+Rw1DWGMcDwULs5Xymp4aV2Rx/ykCoJKT43/nzUhMJ+gq1Urw6uqgdlzILIeoyYLuZyc63fcN4cWGFPXtxRVytEAo6kaIUAFfYBPeZZr4bjulsFsBmwCwayCu3zxDBDm3j73agg3OQ9EQHG3RIvH88UvHTUstr1KL0SUf2uwanVyzXDbu1W1S756IjJdhAhpe+DUah+ccrL0EQiER7OgFuXqYzgJTyV1+INvnCG7+pmVOKvlXcrqwVwojirwscjlJPFhowPyl3C6C53w541KMWlREN/btOoCoVcuiGVOodsX88uXWVltDw0x7AIoZK3YSYHxZs9yxLBd/CKcpKE7xD5WOqMPzt1gm9udqiliBao60egz/XxVsmq31ZOqSpIitzMt38Q2xDJ98iHTPp9/lm41TaL1gDT7uVELl4umarVMZ4t2r8wlpwNvkTfBiL6hUH7gaCMekk6+uWl5P7rbqj4qpKYLYN5hA/zPrwrmsOlAOQ4NgYOX3Kc+eS5zLDj+Ff2QKlKjv03jKbZrYrWJcQFqa6lGQ9Vz50u6wu5DzenztQUhH0HLFNBeBKDkYflIV/OIOrgAfeln1usiCtrx+zBPrD5DZXNNH/DkKr5fblj8U5Q0RczzctB6JJOEbEMO/bzWk7qtNzEFR81LRa4w+fhCNVhyIweQ2SPDbGFMK/Mq1OH+P8gI/TfLLMS7XzyMf84//P0yGIilEUGVaivwZYNbn2kRLJrL1jTKS7jmNAcCWoQ+xclQfbxoFyETTNJYXuuBeQx6ixtW6y8CoKWY4mxgL2MwW+bM6FsWygSD1mQEXblasOEVusWdQqPJM5/1zgFUbysDYE7g0L9/DTmwqZqe7KHshYTsojugYB4

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


--

Université de Lille

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


  • [grouper-users] how get created_on date for change_log for a custom attribute from grouper_change_log_entry_v view, Mathieu HETRU, 05/07/2018

Archive powered by MHonArc 2.6.19.

Top of Page