OIM Tables

RJ_KEY — auto generated key (the Job ID)

RJ_NAME — name of the scheduled job

RJ_JOB_STATUS — current status of the job

RJ_EVENT_CNT — number of recon events which were associated with this job

The batch info is captured in the RECON_BATCHES table. After RECON_JOBS table is populated, an entry in the RECON_BATCHES table is created. 

It contains the key of the job id from the RECON_JOBS table as a foreign key

Info contained within RECON_BATCHES table

RB_KEY — unique batch number

RJ_KEY — foreign key corresponding to job id from the RECON_JOBS table

RB_BATCH_STATUS — current status of batch execution

RB_EVENT_CNT — count of the number of events in the batch 

Info contained within RECON_EVENTS table

RE_KEY — unique event key

RJ_KEY — the job id corresponding to this event (from RECON_JOBS)

RB_KEY — the batch id (from RECON_BATCHES)

OBJ_KEY — name of the Resource Object for which recon is executing …. and other columns as well

At last, there is an entry into the recon staging table corresponding to the Resource Object. 

This is nothing but the RA_* table. This table consists of the data in ‘horizontal table‘ style i.e. 

all the attributes of a recon event are stored in various columns and each row/entry in the table is corresponding to a particular event for that resource

Info about RA_* table

RECON_TABLE_KEY — unique key

RE_KEY — the recon event key to which the entry belongs 

usr - Identities

UD_ADUSER  - Process Form

SELECT * FROM user_provisioning_attrs ORDER BY 1 desc;

SELECT * FROM USER_PROVISIONING_ATTRS where USER_PROVISIONING_ATTRS.usr_key in (SELECT usr_key FROM usr where usr_key in (SELECT usr_key FROM recon_events where recon_events.rj_key = '2397326'));

 

SELECT * FROM RECON_JOBS ORDER BY 1 desc;
SELECT * FROM recon_batches ORDER BY 1desc;
SELECT * FROM recon_events ORDER BY 1 desc;

NOTE : 

# If you see user has POLICY_EVAL_NEEDED =1, it means user is marked for evaluation and will be processed in next run of schedule task.

# If you see user with POLICY_EVAL_IN_PROGRESS =1, it means policy evaluation is in progress for such user. However, this case is RED flag and need to be looked into closely.

svr — IT Resource information. (svr_key is primary key)

spd — IT resource parameter Definition information

svd – IT Resource Type Definition.

svp -  IT Resource parameter values

app_instance — Application Instance Information (app_instance_key is the primary key)

It will have itresoucre_key to link with svr_key in svr table and object_key to link with obj_key in obj table.

obj— Resource Object information (obj_key)

obi — Object Instance information  (obi_key)

catalog — Request Catalog Information (applications instances, roles, entitlements) (Catalog_id)

orc — It stores any users account instance information (orc_key)

ost — It stores the object status information (ost_key)

Note : never link ost and obj table directly, we have to link ost and oiu table with ost_key and then obi and oiu with obi_key and then link obi, obj using obj_key

ent_list — Stores the entitlements information (ent_list_key)

lkv,lku — Lookup tables lkv stores Lookup Values information, lku stores Lookup Definition information (lkv_key, lku_key)

ent_assign — Stores the information of entitlements assigned to users. (ent_assign_key)

Note : This table will be having a column to say the provisioning mechanism as well, how the entitlement was added (via Recon, Direct Prov, access policy, Manual Provisioning etc..), This table will be linked with different tables, using ent_list_key we can link with ent_list table as well as entity_key of catalog table , ud_child_key we can link with child table of any recourse.

ent_assign_hist – Stores the information of history of entitlements assigned to user.

oiu — It is the key table which will be linked to all other tables like ost, orc, obi, usr, app_instance etc. to get the users account information (oiu_key, It will have ost_key to link with ost table, orc_key to link with orc table, obi_key to link with obi table, app_instance_key to link with app_instance table, usr_key to link with usr table )

ugp — Stores the roles information

pol— Stores access Policies information

poc --Stores the child table/Entitlements attached to a access policy

pof -- Stores Resource tied in policy (pol_field_value will have IT resource key but as char so when we link with svr table, should be like to_char(svr.svr_key))

mil – Table stores task information, process task that triggered

osi – key table that can link with orc_key of orc table, sch_key of sch table, mil_key of mil table

sch – status and other information of the task triggered

sdk – This table stores the Form information.

sdp – Stores the properties of the form.

update catalog set MANAGERAPPROVALNEEDED='TRUE' where catalog_id=5624;

select * from catalog where ENTITY_DISPLAY_NAME like '%CN=BPRWeb-citqa-q.web.abbott.com-(RX)%';

commit;