Error while Importing LDAP Account

[tuhink]

I am getting error while importing ldap accounts. (Provisioning/Update/Remove are working fine.) From the logs it seems it is able to search the accounts. But the job is failing with the below error.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE A.ENDPOINTKEY = 3 AND (A.STATUS is null or A.STATUS <> 'SUSPENDED FROM IMP' at line 2

ACCOUNT_ATTRIBUTE  is set as:

[NAME::tech#String,ACCOUNTID::distinguishedName#String,DISPLAYNAME::cn#String,RECONCILATION_FIELD::ACCOUNTID#String,CREATED_ON::createtimestamp#date,UPDATEDATE::modifytimestamp#date,CREATOR::creatorsname#String,customproperty40::distinguishedName#String,CUSTOMPROPERTY21::manager#String,CUSTOMPROPERTY26::ibm-entryuuid#String]

Details logs are as follows:

"2024-02-07T12:12:30.173+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","DEBUG","Calling the LDAP search"
"2024-02-07T12:12:30.230+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","DEBUG","Recieved the results from LDAP search"
"2024-02-07T12:12:30.305+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","DEBUG","LDAP page results=569"
"2024-02-07T12:12:30.306+00:00","ecm-worker","services.HttpClientUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","called api..."
"2024-02-07T12:12:30.306+00:00","ecm-worker","services.HttpClientUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","timeout validated for api..."
"2024-02-07T12:12:30.306+00:00","ecm-worker","services.HttpClientUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","got response for api..."
"2024-02-07T12:12:30.306+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","DEBUG","Start processing Accounts LDAP data"
"2024-02-07T12:12:30.307+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","DEBUG","New Query is....... "
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7",""," SELECT A.ACCOUNTKEY AS ID, A.NAME AS NAME, A.ACCOUNTID#String AS UNIQUE_ID FROM ACCOUNTS A"
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7",""," WHERE A.ENDPOINTKEY = 3 AND (A.STATUS is null or A.STATUS <> 'SUSPENDED FROM IMPORT SERVICE')"
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7","",""
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7","","07-Feb-2024 12:12:30.307 WARNING [quartzScheduler_Worker-7] groovy.sql.Sql$AbstractQueryCommand.execute Failed to execute: "
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7",""," SELECT A.ACCOUNTKEY AS ID, A.NAME AS NAME, A.ACCOUNTID#String AS UNIQUE_ID FROM ACCOUNTS A"
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7",""," WHERE A.ENDPOINTKEY = 3 AND (A.STATUS is null or A.STATUS <> 'SUSPENDED FROM IMPORT SERVICE')"
"2024-02-07T12:12:30.887+00:00","ecm-worker","","null-czmr7",""," because: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE A.ENDPOINTKEY = 3 AND (A.STATUS is null or A.STATUS <> 'SUSPENDED FROM IMP' at line 2"
"2024-02-07T12:12:30.307+00:00","ecm-worker","services.AdImportService","quartzScheduler_Worker-7-czmr7","ERROR","You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE A.ENDPOINTKEY = 3 AND (A.STATUS is null or A.STATUS <> 'SUSPENDED FROM IMP' at line 2"
"2024-02-07T12:12:30.308+00:00","ecm-worker","services.ImportUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","isImportSuccess: false"
"2024-02-07T12:12:30.308+00:00","ecm-worker","services.ImportUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","isImportSuccess: false"
"2024-02-07T12:12:30.317+00:00","ecm-worker","services.ImportUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","Writing job history to import logs."
"2024-02-07T12:12:30.317+00:00","ecm-worker","services.ImportUtilityService","quartzScheduler_Worker-7-czmr7","DEBUG","Number of log entries to be written : 4"

[adarshk]

Please confirm if your access import is working fine.

Also, share the statusthresholdconfig. 

[CR · Accepted solution]

@tuhink 

Can you try like below RECONCILATION_FIELD

RECONCILATION_FIELD::CUSTOMPROPERTY17,
CUSTOMPROPERTY17::ACCOUNTID#String]

Thanks,
Raghu
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem

[pmahalle]

Hi @tuhink ,

What is the value of STATUS_THRESHOLD_CONFIG in connection? Make sure to add mapping userAccountControl in ACCOUNT_ATTRIBUTE  with one of the account;s customproperty like below

CUSTOMPROPERTY29::userAccountControl#number

And then add STATUS_THRESHOLD_CONFIG like below which will help Saviynt to understand the status of the account reconciled.

{
"statusAndThresholdConfig": {
"statusColumn": "customproperty29",
"activeStatus": [
"512",
"active",
"544",
"66048"
],
"deleteLinks": true,
"accountThresholdValue": 1000,
"correlateInactiveAccounts": true,
"inactivateAccountsNotInFile": false
}
}