Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Disable OU Calculation

utkarshING
Regular Contributor
Regular Contributor

Hi Team,

We have a requirement to move Active Directory (AD) admin accounts to the appropriate Disabled Organizational Unit (OU) upon termination.

Scenario: A user might have multiple AD admin accounts across different regions, such as APAC, SA, EMEA, and NA. For instance, a user could have the following AD admin accounts:

  • AdminUR_NA
  • AdminUR1_NA
  • AdminUR_APAC
  • AdminUR1_EMEA

We have configured dynamic provisioning to determine the OU based on the region requested by the user, for Active OU calculation.

 

utkarshING_0-1723420718432.png

and accounts are being provisioned as expected.

But for Disabled OU:-

We need a strategy to automatically calculate the Disabled OU for account termination.

We are storing account OU in Accounts.customproperty40

I attempted to use the following dynamic attribute query in the Update account JSON to determine the Disabled OU:

SELECT IF('${user.statuskey}' = 1, null, attribute4) AS ID
FROM dataset_values
WHERE datasetname = 'AdAdminOU'
AND '${user.customproperty41}' = 'RegionCodeNotChanged'
AND '${user.employeeType}' IN ('Employee', 'Contractor')
AND '${user.statuskey}' = '0'
AND attribute3 = (
SELECT DISTINCT SUBSTRING_INDEX(accounts.customproperty40, ",", -7) AS id
FROM accounts
WHERE endpointkey = 1
AND accounts.customproperty25 = '${user.employeeid}'
)
LIMIT 1

But this gives me an excepted error that the substring returns multiple results.

Dataset screenshot example:-

utkarshING_1-1723420975371.png

 

Please suggest how we can calculate Disable OU for account termination.

 

Thanks,

Utkarsh

 

 

 

 

 

 

 

 

 

9 REPLIES 9

rushikeshvartak
All-Star
All-Star

SELECT IF('${user.statuskey}' = 1, null, attribute4) AS ID
FROM dataset_values
WHERE datasetname = 'AdAdminOU'
AND '${user.customproperty41}' = 'RegionCodeNotChanged'
AND '${user.employeeType}' IN ('Employee', 'Contractor')
AND '${user.statuskey}' = '0'
AND attribute3 = (
SELECT SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT accounts.customproperty40 ORDER BY accounts.customproperty40 DESC), ',', 1) AS id
FROM accounts
WHERE endpointkey = 1
AND accounts.customproperty25 = '${user.employeeid}'
)
LIMIT 1


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Thanks for your reply.

But it did not work.

utkarshING_0-1723431166436.png

Could you please share the logic of your code.

My logic:-  when attibute3 is matched with attribute4 then select attibute4

utkarshING_1-1723431999414.png

 

Thanks,

Utkarsh 

 

 

 

Query worked or not ?


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Query doesn't work

Please share logs and screenshot


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

Please find the attached logs.

 

Thanks,

Utkarsh Rathore

  • Does SQL query from dynamic attribute working as expected ?
  • Did you defined moveToOU in JSON ?

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

  • Does SQL query from dynamic attribute working as expected ? No
  • Did you defined moveToOU in JSON ? Yes

 

utkarshING_0-1723571630919.png

 

Thanks,

Utkarsh

  • Share SQL Query as Text
  • Also, share logs when request form is loaded to check for errors in logs 

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.