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');
|
I am having Multiple status for the same user and same account in OIM. Example
ReplyDeleteABC123 is an user ID and this user is having Multiple AD accounts with different status
1. Provisioned
2.Revoked
3.Disable
So I need to find a query for all the users with there status.
I am getting only the current status of the user not the other status.
Add last line as
DeleteAND OST.OST_STATUS IN ('Revoked',Provisioned,Disabled);
Cool and I have a swell supply: What Renovations Can You Claim On Tax small home additions
ReplyDelete