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');