Saturday, 12 December 2015

How to clean up resource history for an account of a user in OIM

Ever came across a trouble some account in the list of accounts provisioned to a user ? Ever felt to mark all the tasks triggered on that particular instance as cancel?

If the answer is yes, then you have the solution below.

            UPDATE SCH SET SCH_STATUS='X' where SCH_KEY IN (
            SELECT SCH.SCH_KEY FROM OIU, APP_INSTANCE, ORC, OSI, SCH, MIL, USR, OST
            WHERE OIU.USR_KEY = USR.USR_KEY
            AND OIU.APP_INSTANCE_KEY = APP_INSTANCE.APP_INSTANCE_KEY
            AND OIU.ORC_KEY = ORC.ORC_KEY
            AND ORC.ORC_KEY = OSI.ORC_KEY
            AND OSI.SCH_KEY = SCH.SCH_KEY
            AND MIL.MIL_KEY = OSI.MIL_KEY
            and oiu.ost_key = OST.OST_KEY
            and MIL.MIL_NAME ! ='Delete User'

            AND OIU.ORC_KEY =GIVE ORC KEY HERE);

Query to find the list of all acccounts associated/provisioned to a user in OIM

    SELECT USR.USR_LOGIN,APP_INSTANCE.APP_INSTANCE_NAME,OST.OST_STATUS,oiu.orc_key FROM OIU
    JOIN USR ON OIU.USR_KEY = USR.USR_KEY
    JOIN APP_INSTANCE ON OIU.APP_INSTANCE_KEY = APP_INSTANCE.APP_INSTANCE_KEY
    JOIN OST ON OIU.OST_KEY = OST.OST_KEY
    WHERE USR.USR_LOGIN IN ('GIVE LOGIN ID HERE') ORDER BY USR.USR_LOGIN;

Query to find the list of all entitlements of a user for a given application instance in OIM

select usr.usr_login,app_instance.app_instance_name,ent_list.ent_display_name,ent_assign.ent_status from ent_assign
join usr on usr.usr_key = ent_assign.usr_key and usr.usr_login = upper('Given user login here')
join ent_list on ent_list.ent_list_key = ent_assign.ent_list_key
join app_instance on ent_list.obj_key = app_instance.object_key and ent_list.svr_key = app_instance.itresource_key
where app_instance.app_instance_name = 'Give App Instance Name here'

Query to find out the list of all 'Create User' tasks that did not succeed on a given application instance in OIM

Say, you are asked as below... Hey.... can you give me the list of all 'Create User' tasks that got rejected on a given application instance say... 'ActiveDirectory'...

How to find that.... no issues, use below query.... :)

SELECT APP_INSTANCE.APP_INSTANCE_name,mil.mil_name, sch.SCH_STATUS FROM OIU, APP_INSTANCE, ORC, OSI, SCH, MIL, USR, OST
WHERE OIU.USR_KEY = USR.USR_KEY
AND OIU.APP_INSTANCE_KEY = APP_INSTANCE.APP_INSTANCE_KEY
AND OIU.ORC_KEY = ORC.ORC_KEY
AND ORC.ORC_KEY = OSI.ORC_KEY
AND OSI.SCH_KEY = SCH.SCH_KEY
AND MIL.MIL_KEY = OSI.MIL_KEY
and oiu.ost_key = OST.OST_KEY
and MIL.MIL_NAME ='Create User'
and APP_INSTANCE.APP_INSTANCE_NAME = 'ActiveDirectory'
and SCH_STATUS!='C'

Query to find the list of all process tasks associated with a resource object.

Query to find the list of all process tasks associated with a resource object in OIM.

select * from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on pkg.obj_key = obj.obj_key
where obj.obj_name = [Give the resource object name here in single quotes]

Well, lets go a step further...

I want to know the adapter that is mapped to each task listed with above query too...

hmm okay... here you go...

select OBJ.OBJ_NAME, MIL.MIL_NAME,ADP.ADP_NAME from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on pkg.obj_key = obj.obj_key
LEFT OUTER JOIN ADP ON ADP.EVT_KEY = MIL.EVT_KEY
where obj.obj_name = [Give the resource object name here in single quotes]

So sweet .... right?

Lets go one step ahead, How nice would it be, if I have a query that will tell me, the process task name, the adapter that the task is mapped to, and all the task adapter mappings, for a given resource object....

In such a case, we can save some time, instead of opening design console... right?

Alright... here is your query...

select OBJ.OBJ_NAME, Mil.mil_key,MIL.MIL_NAME,adp.adp_key,ADP.ADP_NAME,ADV.ADV_NAME, MAV.MAV_MAP_TO,MAV.MAV_MAP_QUALIFIER,MAV.MAV_MAP_VALUE from mil
join tos on tos.tos_key = mil.tos_key
join pkg on tos.pkg_key = pkg.pkg_key
join obj on pkg.obj_key = obj.obj_key
LEFT OUTER JOIN ADP ON ADP.EVT_KEY = MIL.EVT_KEY
left outer join mav on mav.mil_key = mil.mil_key
left outer JOIN ADV ON MAV.ADV_KEY = ADV.ADV_KEY
where obj.obj_name = [Give the resource object name here in single quotes] order by 3 asc

loved the journey? :)

Monday, 7 December 2015

How to improve catalog searches

How to improve Catalog searches?

Catalog searches from OIM identity console may get slower over time. The catalog searches can be improved by rebuilding Oracle indexes on the CATALOG table.

Run this query to find all the index on the table select * from user_indexes where table_name='CATALOG';
The query will produce result like this
CATALOG_PK
UK_ENTITY_KEY_TYPE
IDX_CATALOG_IS_DEL
IDX_CATALOG_IS_REQ
IDX_CATALOG_ENTITY_KEY
CAT_TAGS
IDX_CATALOG_ENTITY_TYPE
Run this command to build each indexes ALTER INDEX <index_name> rebuild;

How does Refresh Role Memberships work

Refresh Role Memberships

As per oracle documentation, This job evaluates the role memberships and assigns users to roles based on rules. This job evaluates all the roles whose membership rules have changed since the last job run and their immediate evaluation have not been opted by the administrator. Any guess as of from which table, does this job consider the records that it needs to process?

The answer is DYN_EVAL_CHANGELOG

Sunday, 26 April 2015

Query to find the list of all roles associated with a given application instance.

Query to find the list of all roles associated with a given application instance

SELECT UGP_DISPLAY_NAME
FROM POG , UGP , POL , pop , app_instance
WHERE POG.UGP_KEY=UGP.UGP_KEY
AND POL.POL_KEY= POG.POL_KEY
and pop.POL_KEY= POL.POL_KEY
and pop.obj_key= app_instance.object_key
and pop.pop_denial =0
and app_instance_display_name = :AppInstDispName

Dynamic date range in BI Publisher

Dynamic date range in BI Publisher

While you are scheduling any BI report, you might need to give the date range.
If you wanna achieve dynamism to your report, below is the syntax.

Below snippet would fetch the results for the last seven days.

Date Range From: {$SYSDATE()-7$}
Date Range To : {$SYSDATE()$}

To know the current status of any entry[You need to know the orc key of the entry you are interested in ] in UD table

To know the current status of any entry[You need to know the orc key of the entry you are interested in ] in UD table

select OST_STATUS,ost_key from OST where ost_key in (select ost_key from oiu where orc_key =1022507)

Time taken to run a java program

Time taken to run a program*

Can be used in scheduled tasks in OIM for performance considerations.

long startTimer = System.nanoTime();
long endTimer = System.nanoTime();
System.out.print("Time to run this "+(endTimer - startTimer)+"ns");

Tips for viewing oim log file

Generally the oim log files are very huge. Most of the engineers try to download the huge files using WIN SCP and use conventional editors like notepad++ or Textpad to search for the error(s).

And some senior folks who are well versed with VI try to use VI commands.

Here is an alternative to both the approaches.

Use the below command to find out the line numbers which will contain the error string you are searching for.

grep -n "Record is inserted into table!" oim_server1.out

Now use below command to display log snippet below two specified line numbers.

sed -n 114993700,114993750p oim_server1.out

To get list of all AI forms that are in use at the moment

To get list of all AI forms that are in use at the moment

select app_instance_dataset from app_instance where app_instance_dataset is not null;

Query to find out the series of orchestration events for a given request.

Query to find out the series of orchestration events for a given request

select * from orchevents where processid = (select orchestration_process_id from request where request_key = 19957) order by orchorder

How to fetch the lookup values using db query

select LKV_ENCODEd, LKV_DECODED from LKV, LKU WHERE lku.lku_type_string_key='Lookup.ABC.SecondaryGroups' and lkv.lku_key = lku.lku_key