Disable OU Calculation

[utkarshING]

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.

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:-

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

Thanks,

Utkarsh

[rushikeshvartak]

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

[utkarshING]

Thanks for your reply.

But it did not work.

Could you please share the logic of your code.

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

Thanks,

Utkarsh 

[rushikeshvartak]

Query worked or not ?

[utkarshING]

Query doesn't work

[rushikeshvartak]

Please share logs and screenshot

[utkarshING]

Please find the attached logs.

Thanks,

Utkarsh Rathore

[rushikeshvartak]

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

[utkarshING]

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

Thanks,

Utkarsh

[rushikeshvartak]

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