Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
08/11/2024 05:09 PM
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:
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
08/11/2024 06:51 PM
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
08/11/2024 08:07 PM
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
08/11/2024 08:24 PM
Query worked or not ?
08/11/2024 09:33 PM - edited 08/11/2024 09:34 PM
Query doesn't work
08/11/2024 09:38 PM
Please share logs and screenshot
08/13/2024 10:34 AM
08/13/2024 10:49 AM
08/13/2024 10:54 AM
Thanks,
Utkarsh
08/13/2024 10:55 AM