OIM SQL Query 1

To check for users created today whose resource status is provisioning

select oiu.usr_key,obj.obj_key, obj.OBJ_NAME, usr.USR_LOGIN , usr.usr_first_name , usr.USR_LAST_NAME , ost.ost_status from oiu , ost, obj , USR where oiu.ost_key =ost.ost_key and oiu.usr_key = usr.usr_key and ost.obj_key = obj.obj_key and trunc(usr.usr_create) = trunc(sysdate) and (obj.obj_name= 'OID' or obj.obj_name = 'FLAT_GTC')and ost.ost_status = 'Provisioning';

To check for users whose resource status is provisioning

select oiu.usr_key,obj.obj_key, obj.OBJ_NAME, usr.USR_LOGIN , usr.usr_first_name , usr.USR_LAST_NAME , ost.ost_status from oiu , ost, obj , USR where oiu.ost_key =ost.ost_key and oiu.usr_key = usr.usr_key and ost.obj_key = obj.obj_key and (obj.obj_name= 'OID' or obj.obj_name = 'FLAT_GTC')and ost.ost_status = 'Provisioning';

To check the resource assigned to a user

select obj.obj_name from OBJ,OST,OIU,USR where OST.OBJ_KEY = obj.OBJ_KEY and oiu.ost_key = ost.ost_key and oiu.usr_key = usr.usr_key and USR.USR_LAST_NAME='asdf';

Getting accesspolicyDetails

select pol.pol_key , pol.pol_name , pol.pol_description from pol;