Group comparison

hcodpleh

Hello,

I have more than 1000 groups. I would like to know which groups contains the same users?

For example

group1 (user1, user20,user29)

group2 (user1, user3,user20,user29)

group3 (user1, user2, user20,user29)

group4 (user1, user20,user29)

The result of the query should return group1 and group4

Any idea?

Find more posts tagged with

Comments

hcodpleh

Help! Help!

Still any idea?

alain_molimard

Hi,

Here is a query you can run on the database directly (if you use SQL Server 2005):

SELECT

p3.Users,

(

SELECT

p4.r_object_id + ','

FROM (SELECT

p1.r_object_id,

(

SELECT

users_names + ','

FROM dm_group_r p2

WHERE p2.r_object_id = p1.r_object_id

ORDER BY users_names FOR XML PATH('')

)AS Users

FROM dm_group_r p1

where p1.users_names is not null

GROUP BY p1.r_object_id)

WHERE p4.Users = p3.Users

ORDER BY r_object_id FOR XML PATH('')

)

AS Groups

FROM (SELECT

p5.r_object_id,

(

SELECT

users_names + ','

FROM dm_group_r p6

WHERE p5.r_object_id = p6.r_object_id

ORDER BY users_names FOR XML PATH('')

)

AS Users

FROM dm_group_r p5

where p5.users_names is not null

GROUP BY p5.r_object_id)

GROUP BY p3.Users

This will return something like this

users combination	r_object_id of the groups containing that combination
cgidevd6,	1201e24080000505,
cgidevd6,dm_autorender_mac,dm_autorender_win31,dm_mediaserver,dmadmin,	1201e24080000100,
cgidevd6,dmadmin,	1201e24080000125,1201e24080000d00,
dmadmin,	1201e24080000130,1201e24080000503,1201e24080000504,1201e24080000507,1201e24080000900,

As you can see, two groups contain cgidevd6 and dmadmin as a user.

NOTE:

This does not take into account if the groups contains groups

I will try to post something for Oracle soon!

Enjoy...

Anyone is welcome to improve...

alain_molimard

Ok,

Here is the query of an Oracle instance:

SELECT str, SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH( group_name , '/'),'/',',')),2) groupIDFROM(select A.*, row_number() OVER (Partition by str order by group_name ) ROW# from (SELECT group_name, SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH( users_names , '/'),'/',',')),2) str FROM (select E.group_name, A.users_names, row_number() OVER (Partition by E.group_name order by A.users_names ) ROW# from ged_caisse.dm_group_r A, ged_caisse.dm_group_s E Where A.r_object_id=E.r_object_id and A.r_object_id in ( Select r_object_id from ( select r_object_id, count(users_names) from ged_caisse.dm_group_r group by r_object_id having count(users_names) < 200 ) ) ) CONNECT BY PRIOR group_name = group_name AND PRIOR row# = row# -1 GROUP BY group_name ) A) START WITH ROW#=1 CONNECT BY PRIOR str = str AND PRIOR row# = row# -1 GROUP BY strThis version also join with the dm_group_s table to display the group_name instead of the r_object_id; same logic could be apply the the SQL Server query.RegardsAl

alain_molimard

Hi,

I have updated my oracle query:

select all_grps, all_users, "occurences" + 1 as "number_of_identical_group" from (
select
   all_users,
   all_grps,
   ',' as "character",
   length(all_grps) - length(translate(all_grps,chr(0)||',',chr(0))) as "occurences"
from(

    SELECT
        all_users,
        SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH( group_name , '/'),'/',',')),2) all_grps
FROM
        (
        select
        A.*,
        row_number() OVER (Partition by all_users order by group_name ) ROW#
        from
            (
            SELECT
                group_name,
                SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH( users_names , '/'),'/',',')),2) all_users
            FROM
            (
                select
                A.group_name, A.users_names,
                row_number() OVER (Partition by group_name order by users_names ) ROW#
                from
                (
                ( select B.group_name, A.users_names as users_names from dm_group_r A, dm_group_s B
                    where A.r_object_id = B.r_object_id and A.users_names is not null
                 union
                 select B.group_name, A.groups_names from dm_group_r A, dm_group_s B
                  where A.r_object_id = B.r_object_id and A.groups_names is not null
                )order by 1)
                A

            )
                START WITH ROW#=1
                CONNECT BY PRIOR group_name = group_name
                AND PRIOR row# = row# -1
                GROUP BY group_name
            ) A
         )
START WITH ROW#=1
CONNECT BY PRIOR all_users = all_users
AND PRIOR row# = row# -1
GROUP BY all_users
)
)where "occurences" > 0
order by all_grps

ALL_GRPS	ALL_USERS	number_of_identical_group
dm_datefield_override_role,dm_internal_attrib_override_role,dm_user_identity_override_role	dm_world	3
dm_fulltext_admin,room_0b00bb8480002523_owners,room_0b00bb8480002523_visitors	dmadmin	3
dm_workflow_task_supervisor,queue_processor	queue_advance_processor,queue_manager	2
group a,group b	Joe Smith,dmadmin	2
process_report_admin,queue_manager	queue_admin	2

This query only list groups that are duplicates and the last column chose the number of groups that are duplicatese;

You can create a view in your database, register that view in your docbase and then create a smartlist so your power user can see the results in webtop

Cheers