sql query to get the role assigned/revoked in last 7 days in OIM?

2656016

hi all,

does anyone have sql query to get the role assigned/revoked in last x days?

the query output should be

userlogin,rolename,status(revoked/assigned)

TIA

Kevin Pinsky

It's all in the table upa_grp_membership.

-Kevin

2656016

thanks.

here are the query i used:

select usr.usr_login,LISTAGG(ugp.ugp_name, ';') WITHIN GROUP (ORDER BY ugp.ugp_name) from identmgr.upa_grp_membership,identmgr.usr,identmgr.ugp,identmgr.upa_usr

where usr.usr_key=upa_usr.usr_key  and upa_grp_membership.status='Active' AND ugp.ugp_key=upa_grp_membership.ugp_key and upa_grp_membership.upa_usr_key=upa_usr.upa_usr_key and

TRUNC(upa_grp_membership.CREATE_DATE) > TO_DATE('2014-06-01', 'YYYY-MM-DD') group by usr.usr_login;

select usr.usr_login,LISTAGG(ugp.ugp_name, ';') WITHIN GROUP (ORDER BY ugp.ugp_name) from identmgr.upa_grp_membership,identmgr.usr,identmgr.ugp,identmgr.upa_usr

where usr.usr_key=upa_usr.usr_key  and upa_grp_membership.status='Revoked' AND ugp.ugp_key=upa_grp_membership.ugp_key and upa_grp_membership.upa_usr_key=upa_usr.upa_usr_key and

TRUNC(upa_grp_membership.CREATE_DATE) > TO_DATE('2014-06-01', 'YYYY-MM-DD') group by usr.usr_login;