1. To
get the user list/details provisioned with particular entitlement
Select
usr_login,USR_DISPLAY_NAME,USR_UDF_EMPLOYEEID,ENT_CODE,ENT_Value,ENT_ASSIGN_UPDATE
from usr inner join ent_assign
on ent_assign.usr_key=usr.usr_key
inner join ent_list
on ent_list.ent_list_key=ent_assign.ent_list_key
where lower(ent_code) like '%=<ent code>%'
and usr_status = 'Active'
and obj_key=<Resource Object key>
order by usr_login;
2. To
get the list of entitlements provisioned to the user associated with
Enabled/Provisioned accounts:
select * from ent_assign, usr ,catalog, oiu,
ost, orc where ent_assign.ent_list_key=catalog.entity_key and
Ent_assign.usr_key=usr.usr_key and ent_assign.oiu_key=oiu.oiu_key and
ost.ost_key=oiu.ost_key and oiu.orc_key=orc.orc_key and usr.usr_login=<User
Login>;
3. To
get the request details on the basis of request status
select IDENTIFICATIONKEY "Request
ID",usr_login "Requestor ID",usr_display_name "Requestor’s
Name",
REQUEST_CREATION_DATE "Requested
Date",rbe_entity_type, rbe_entity_name,
ASSIGNEES "Approver
ID",ASSIGNEESDISPLAYNAME "Approver’s Name",ASSIGNEDDATE
"Assigned Date",REQUEST_STATUS "Request
Status",EXPIRATIONDATE "Expiry Date"
from WFTASK
inner join request on
request_id=IDENTIFICATIONKEY
inner join usr on REQUESTER_KEY=usr_key
inner join request_beneficiary_entities on
request_key=rbe_request_key
where assigneddate between
to_date('01-10-2019','DD-MM-YYYY') and to_date('31-12-2019','DD-MM-YYYY')
and request_status in ('Request
Rejected','Request Withdrawn')
and ASSIGNEES not in ('xelsysadm,user','SYSTEM
ADMINISTRATORS,group')
order by ASSIGNEDDATE DESC;
4. To
get the rejected tasks count for the resource object in particular time period
select count(*),MIL.MIL_NAME,obj_name
FROM OSI
inner join SCH on SCH.SCH_KEY=OSI.SCH_KEY
inner join STA on STA.STA_STATUS=SCH.SCH_STATUS
inner join MIL on OSI.MIL_KEY=MIL.MIL_KEY
inner join TOS on MIL.TOS_KEY=TOS.TOS_KEY
inner join PKG on TOS.PKG_KEY=PKG.PKG_KEY
inner join OIU on OSI.ORC_KEY=OIU.ORC_KEY
inner join USR on OIU.USR_KEY=USR.USR_KEY
inner join OST on oiu.ost_key = ost.ost_key
inner join OBJ on OST.OBJ_KEY=OBJ.OBJ_KEY
inner join ORC on orc.orc_key = oiu.orc_key
WHERE to_date(to_char(sch_actual_start,'DD-MM-YYYY'),'DD-MM-YYYY')
between to_date('01-01-2018','DD-MM-YYYY') and
to_date('01-01-2019','DD-MM-YYYY')
and usr_status='Active'
and obj_name= '<Resource Object
Name>'
and ost_status in
('Provisioned','Enabled','Disabled')
and sta_bucket ='Rejected'
group by MIL.MIL_NAME,obj_name;
5. To
get list of all the rejected tasks
Select * from osi, sch, mil , orc,usr, oiu where
orc.orc_key=osi.orc_key and sch.sch_key=osi.sch_key and oiu.orc_key=orc.orc_key
and oiu.usr_key=usr.usr_key
and osi.mil_key=mil.mil_key and
sch.sch_status='R';
6. To get all
entitlements attached to policies linked to a role
select * from pol, ugp,pog, poc where
pol.pol_key=pog.pol_key
and poc.pol_key=pol.pol_key and
pog.ugp_key=ugp.ugp_key and
ugp.ugp_name like '<Role Name>';
7. To get List of role names against
associated application instances evaluated through access policies
select ugp.ugp_name,
app_instance.app_instance_display_name from pol,ugp, pog ,
pof,obj,svr,app_instance
where pol.pol_key=pog.pol_key and
pog.ugp_key=ugp.ugp_key and
pof.pol_key=pol.pol_key and obj.obj_key=pof.obj_key
and app_instance.itresource_key=svr.svr_key and
to_char(svr.svr_key)=pof.POF_FIELD_VALUE ;
8. To get list of users associated with role and
evaluated with access policy attached to it.
select usr.usr_login,
ugp.ugp_name,ent_list.ent_display_name,pol.pol_name from
poc,ent_list,ent_assign,pol,pog,ugp,usr where
poc.poc_field_value=ent_list.ent_code and poc.pol_key=pol.pol_key
and ent_assign.ent_list_key=ent_list.ent_list_key
and pog.ugp_key=ugp.ugp_key and pog.pol_key=pol.pol_key
and ent_assign.usr_key=usr.usr_key
and ugp.ugp_key in (select ugp.ugp_key
from pol,ugp, pog , pof,obj,svr,app_instance
where pol.pol_key=pog.pol_key and
pog.ugp_key=ugp.ugp_key and pof.pol_key=pol.pol_key
and obj.obj_key=pof.obj_key
and app_instance.itresource_key=svr.svr_key and
to_char(svr.svr_key)=pof.POF_FIELD_VALUE and
app_instance_display_name=<Application instance display name>)
and usr.usr_status='Active';
9.
To get all the Users having specific account provisioned.
SELECT USR.USR_LOGIN,
USR.USR_FIRST_NAME, USR.USR_LAST_NAME
FROM OBJ, OBI, OIU, OST, USR
WHERE OBJ.OBJ_KEY = OBI.OBJ_KEY
AND OBI.OBI_KEY = OIU.OBI_KEY
AND OIU.USR_KEY = USR.USR_KEY
AND OIU.OST_KEY = OST.OST_KEY
AND OST.OBJ_KEY = OBJ.OBJ_KEY
AND OST.OST_STATUS IN ('Enabled','Provisioned')
AND OBJ.OBJ_NAME = '<Resource Object
Name>';
10 To get all the User's provisioned/enabled accounts.
SELECT OBJ.OBJ_NAME, OST_STATUS
FROM OBJ, OBI, OIU, OST, USR
WHERE OBJ.OBJ_KEY = OBI.OBJ_KEY
AND OBI.OBI_KEY = OIU.OBI_KEY
AND OIU.USR_KEY = USR.USR_KEY
AND OIU.OST_KEY = OST.OST_KEY
AND OST.OBJ_KEY = OBJ.OBJ_KEY
AND OST.OST_STATUS IN ('Enabled','Provisioned')
AND USR.USR_LOGIN = '<User_Login>';
To get all the list of users provisioned to application between the dates
select distinct
usr_login, usr_email, usr_status, ost_status, sch_actual_start, mil_name
FROM OSI
inner join SCH on SCH.SCH_KEY=OSI.SCH_KEY
inner join STA on STA.STA_STATUS=SCH.SCH_STATUS
inner join MIL on OSI.MIL_KEY=MIL.MIL_KEY
inner join TOS on MIL.TOS_KEY=TOS.TOS_KEY
inner join PKG on TOS.PKG_KEY=PKG.PKG_KEY
inner join OIU on OSI.ORC_KEY=OIU.ORC_KEY
inner join USR on OIU.USR_KEY=USR.USR_KEY
inner join OST on oiu.ost_key = ost.ost_key
inner join OBJ on OST.OBJ_KEY=OBJ.OBJ_KEY
inner join ORC on orc.orc_key = oiu.orc_key
WHERE to_date(to_char(sch_actual_start,'DD-MM-YYYY'),'DD-MM-YYYY')
between to_date('01-01-2018','DD-MM-YYYY') and
to_date('01-07-2018','DD-MM-YYYY')
and
ost_status in ('Provisioned','Enabled','Disabled')
and sta_bucket ='Completed'
and MIl_name ='Create User'
and upper(obj_name) like '%AD%';
