IDM World: November 2016

Wednesday, November 23, 2016

SQL Query to get Process Instance Key of particular Resource for user in OIM

Query to get Process Instance Key:

SELECT ORC_KEY

FROM ORC,PKG,USR

WHERE ORC.PKG_KEY=PKG.PKG_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND PKG_NAME=’[Insert Resource Name here]’

AND USR_LOGIN=’[Insert User Login here]’;

Example: To get Process Instance Key of AD User resource for TESTUSER user:

SELECT ORC_KEY

FROM ORC,PKG,USR

WHERE ORC.PKG_KEY=PKG.PKG_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND PKG_NAME='AD User'

AND USR_LOGIN=’TESTUSER’;

Sunday, November 20, 2016

SQL query to get all the accounts of user in OIM

Query to get active/enabled accounts:

SELECT OBJ.OBJ_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 USR.USR_LOGIN='[Insert User Login here]'

AND OST.OST_STATUS IN ('Enabled','Provisioned');

Example: To get all the active/enabled accounts of user TESTUSER

SELECT OBJ.OBJ_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 USR.USR_LOGIN='TESTUSER'

AND OST.OST_STATUS IN ('Enabled','Provisioned');

Query to get disabled accounts:

SELECT OBJ.OBJ_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 USR.USR_LOGIN='[Insert User Login here]'

AND OST.OST_STATUS IN ('Disabled');

Example: To get all the active/enabled accounts of user TESTUSER

SELECT OBJ.OBJ_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 USR.USR_LOGIN='TESTUSER'

AND OST.OST_STATUS IN ('Disabled');

Query to get revoked accounts:

SELECT OBJ.OBJ_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 USR.USR_LOGIN='[Insert User Login here]'

AND OST.OST_STATUS IN ('Revoked');

Example: To get all the revoked accounts of user TESTUSER

SELECT OBJ.OBJ_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 USR.USR_LOGIN='TESTUSER'

AND OST.OST_STATUS IN ('Revoked');

SQL query to get all the users having account on particular resource in OIM

Query to get users list having active/enabled account:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME=' [Insert Resource Name here]'

AND OST.OST_STATUS IN ('Enabled','Provisioned');

Example: To get the users having active/enabled account on AD User resource:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME='AD User'

AND OST.OST_STATUS IN ('Enabled','Provisioned');

Query to get users list having disabled account:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME=' [Insert Resource Name here]'

AND OST.OST_STATUS IN ('Disabled');

Example: To get the users having disabled account on AD User resource:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME='AD User'

AND OST.OST_STATUS IN ('Disabled');

Query to get users list having revoked account:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME=' [Insert Resource Name here]'

AND OST.OST_STATUS IN ('Revoked');

Example: To get the users having revoked account on AD User resource:

SELECT USR.USR_LOGIN

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 OBJ.OBJ_NAME='AD User'

AND OST.OST_STATUS IN ('Revoked');

Friday, November 18, 2016

SQL query to get all the Completed/Rejected tasks of particular resource for user in OIM

SQL Query to get Completed tasks of particular resource for User:

SELECT MIL_NAME as TASKS

FROM USR, ORC, MIL, PKG, TOS, SCH, OSI

WHERE OSI.MIL_KEY=MIL.MIL_KEY

AND OSI.ORC_KEY=ORC.ORC_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND OSI.TOS_KEY=TOS.TOS_KEY

AND OSI.PKG_KEY=PKG.PKG_KEY

AND OSI.SCH_KEY=SCH.SCH_KEY

AND PKG.PKG_NAME='[Insert Resource Name here]'

AND SCH.SCH_STATUS ='C'

AND USR.USR_LOGIN='[Insert User Login here]'

ORDER BY OSI.OSI_UPDATE;

Example: To get Completed tasks of AD User resource for TESTUSER user:

SELECT MIL_NAME as TASKS

FROM USR, ORC, MIL, PKG, TOS, SCH, OSI

WHERE OSI.MIL_KEY=MIL.MIL_KEY

AND OSI.ORC_KEY=ORC.ORC_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND OSI.TOS_KEY=TOS.TOS_KEY

AND OSI.PKG_KEY=PKG.PKG_KEY

AND OSI.SCH_KEY=SCH.SCH_KEY

AND PKG.PKG_NAME='AD User'

AND SCH.SCH_STATUS ='C'

AND USR.USR_LOGIN='TESTUSER'

ORDER BY OSI.OSI_UPDATE;

SQL Query to get Rejected tasks of particular resource for User:

SELECT MIL_NAME as TASKS

FROM USR, ORC,MIL, PKG, TOS, SCH, OSI

WHERE OSI.MIL_KEY=MIL.MIL_KEY

AND OSI.ORC_KEY=ORC.ORC_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND OSI.TOS_KEY=TOS.TOS_KEY

AND OSI.PKG_KEY=PKG.PKG_KEY

AND OSI.SCH_KEY=SCH.SCH_KEY

AND PKG.PKG_NAME='[Insert Resource Name here]'

AND SCH.SCH_STATUS ='R'

AND USR.USR_LOGIN='[Insert User Login here]'

ORDER BY OSI.OSI_UPDATE;

Example: To get Rejected tasks of AD User resource for TESTUSER user:

SELECT MIL_NAME as TASKS

FROM USR, ORC,MIL, PKG, TOS, SCH, OSI

WHERE OSI.MIL_KEY=MIL.MIL_KEY

AND OSI.ORC_KEY=ORC.ORC_KEY

AND ORC.USR_KEY=USR.USR_KEY

AND OSI.TOS_KEY=TOS.TOS_KEY

AND OSI.PKG_KEY=PKG.PKG_KEY

AND OSI.SCH_KEY=SCH.SCH_KEY

AND PKG.PKG_NAME='AD User'

AND SCH.SCH_STATUS ='R'

AND USR.USR_LOGIN='TESTUSER'

ORDER BY OSI.OSI_UPDATE;