OIM: Helpful SQLs

Hi this Thiago Leoncio again: Goal of this article is provide you some helpful OIM Sqls. So, you will not waste your time with such complex Data Model as OIM is. Some of them you can use to OIM 10G and OIM11G.

Here you go:

====================================================================
OIM 10G (&& ||) OIM  11G SQLS:
====================================================================

The queries we are going to run to generate the high priority report for the analysts are:

1) All the pending open tasks assigned to GROUP
SELECT SCH_KEY,OSI_ASSIGNED_TO_UGP_KEY, UGP_NAME, OSI_ASSIGNED_DATE, REQ_KEY,OBJ.OBJ_NAME AS OBJ_NAME FROM OTI
INNER JOIN UGP ON UGP.UGP_KEY = OTI.OSI_ASSIGNED_TO_UGP_KEY
INNER JOIN OBJ ON OBJ.OBJ_KEY = OTI.OBJ_KEY
AND OSI_ASSIGN_TYPE = ‘Group’
AND PKG_TYPE=’Provisioning’
AND REQ_KEY IS NOT NULL
ORDER BY OSI_ASSIGNED_DATE

2) All the pending open tasks assigned to USERS

SELECT SCH_KEY,OSI_ASSIGNED_TO_USR_KEY, OSI_ASSIGNED_DATE,REQ_KEY,USR_FIRST_NAME,USR_LAST_NAME,USR_MANAGER_KEY,USR_EMAIL,OBJ.OBJ_NAME AS OBJ_NAME FROM OTI
INNER JOIN USR ON USR.USR_KEY = OTI.OSI_ASSIGNED_TO_USR_KEY
INNER JOIN OBJ ON OBJ.OBJ_KEY = OTI.OBJ_KEY
AND PKG_TYPE=’Provisioning’
AND REQ_KEY IS NOT NULL
ORDER BY OSI_ASSIGNED_DATE

3) All the pending approvals assigned to GROUP

SELECT SCH_KEY,OSI_ASSIGNED_TO_UGP_KEY, UGP_NAME,OSI_ASSIGNED_DATE, REQ_KEY,OBJ.OBJ_NAME AS OBJ_NAME FROM OTI
INNER JOIN UGP ON UGP.UGP_KEY = OTI.OSI_ASSIGNED_TO_UGP_KEY
INNER JOIN OBJ ON OBJ.OBJ_KEY = OTI.OBJ_KEY
AND OSI_ASSIGN_TYPE = ‘Group’
AND PKG_TYPE=’Approval’
AND REQ_KEY IS NOT NULL
ORDER BY OSI_ASSIGNED_DATE

4) All the pending approvals assigned to USERS

SELECT SCH_KEY,OSI_ASSIGNED_TO_USR_KEY, OSI_ASSIGNED_DATE, REQ_KEY,USR_FIRST_NAME,USR_LAST_NAME,USR_MANAGER_KEY,USR_EMAIL,OBJ.OBJ_NAME AS OBJ_NAME FROM OTI INNER JOIN USR ON USR.USR_KEY = OTI.OSI_ASSIGNED_TO_USR_KEY INNER JOIN OBJ ON OBJ.OBJ_KEY = OTI.OBJ_KEY AND PKG_TYPE=’Approval’ AND REQ_KEY IS NOT NULL ORDER BY OSI_ASSIGNED_DATE

5)Below Query will provide you the list of all the users and their resource Status.

SELECT usr.usr_login, usr.usr_first_name, usr.usr_last_name,

obj.obj_key, obj.obj_name, oiu.oiu_create, ost.ost_status, orc.orc_tos_instance_key
FROM orc, usr, obj, oiu, ost, obi WHERE orc.orc_key = oiu.orc_key AND oiu.usr_key = usr.usr_key AND
oiu.ost_key = ost.ost_key AND oiu.obi_key = obi.obi_key AND obi.obj_key = obj.obj_key
6)Below Query gives me all the records of users that are disabled/terminated prior to 15 days (from today) but their AD directory resource is not revoked yet.

SELECT usr.usr_login,usr.usr_first_name,usr.usr_last_name,usr.usr_end_Date,usr.usr_status “User Status”,ost.ost_status “AD Status”,sysdate – 15 “date comparison” FROM orc, usr, obj, oiu, ost, obi WHERE orc.orc_key = oiu.orc_key AND oiu.usr_key = usr.usr_key AND oiu.ost_key = ost.ost_key AND oiu.obi_key = obi.obi_key AND obi.obj_key = obj.obj_key AND obj.obj_name = ‘AD User’ AND ost.ost_status != ‘Revoked’ AND usr.usr_disabled = 1 AND usr.usr_end_date < sysdate – 15

7)SCHEDULED TASK

select tsk_classname,tsk_name,to_char(tsk_last_start_time,’DD/MM/YYYY
HH24:MI:SS’) STARTTIME,to_char(tsk_last_stop_time,’DD/MM/YYYY
HH24:MI:SS’) STOPTIME,to_char(tsk_next_start_time,’DD/MM/YYYY
HH24:MI:SS’) NEXTSTART,tsk_freqtype,tsk_disable from tsk;

====================================================================
These next SQLS are Oracle Database SQLS===>Database Metadata:
====================================================================

8)This SQL below will show you wait sessions(waiter) and who they are waiting(Holder)

SELECT
DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

=

9)This is a easy way to find an object(example schedule) using Database Metadata to search some columns:

select * from user_tab_comments where comments is not null and upper(comments) like upper(‘%schedule%’) order by table_name

I hope this helps,

Thiago Leoncio