Achieve Email Uniqueness Check During Email Generation Against Existing Proxy Addresses

[Chirag_Gupta]

Hello Everyone,

We are seeking guidance on ensuring email uniqueness during email generation by checking against existing proxy addresses. Previously, we achieved this using a custom query job to update the proxy addresses into the user_attribute table, but since custom query jobs are now deprecated, we need an alternative solution.

Below is the query we previously used:

INSERT INTO user_attributes (attributetype, data, userkey)
SELECT newdata.attributetype, newdata.email, newdata.userkey
FROM user_attributes uattr
RIGHT JOIN (
SELECT
'1' AS attributetype,
REPLACE(LOWER(SUBSTRING_INDEX(SUBSTRING_INDEX(acc.customproperty51, ',', numbers.n), ',', -1)),'smtp:','') email,
u.userkey
FROM
(
SELECT 1 n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
) numbers
JOIN accounts acc ON CHAR_LENGTH(acc.customproperty51) - CHAR_LENGTH(REPLACE(acc.customproperty51, ',', '')) >= (numbers.n - 1)
JOIN user_accounts ua ON ua.accountkey = acc.accountkey
JOIN users u ON u.userkey = ua.userkey
WHERE
acc.endpointkey = 5 AND
acc.customproperty51 IS NOT NULL
) newdata
ON uattr.userkey = newdata.userkey AND uattr.data = newdata.email
WHERE
uattr.data IS NULL;

Since we are now pulling proxy addresses into the user's account customproperty, how can we achieve this email uniqueness check with the new method? Any recommendations for replacing the deprecated custom query job with a current supported method would be appreciated.

Looking forward to your suggestions.

Thanks,

chirag gupta

[rushikeshvartak]

Use preprocessor 

[sudeshjaiswal]

Hello @Chirag_Gupta ,

Please refer the below KB for your usecase,
Achieve Email uniqueness check during email genera... - Saviynt Forums - 40223

Thanks.

[Chirag_Gupta]

Hello @sudeshjaiswal @Darshanjain @rushikeshvartak 

I reviewed the above post and have a small doubt. Could you clarify where exactly we are placing the following JSON?

[CUSTOMPROPERTY30::employeeID#String,

LOCATIONDESC::ssoId#String,

CUSTOMPROPERTY42::sAMAccountName#String,

USERATTRIBUTE_EMAIL:proxyAddresses#String,

USERATTRIBUTE_CHECK1:customproperty23#String]

Are we inserting it into the `account_attribute` JSON? If so, we are already importing proxy addresses into `accounts.customproperty51`. How can I push this data to the `user_attribute` table? Could you please share the preprocessor query for this?

Thanks,

chirag gupta

[rushikeshvartak]

This is json for AD Connector. Once you define attribute mapping userattribute_xxx then product automatically insert into user_attribute table

[Chirag_Gupta]

Hello @rushikeshvartak 

Here's my account_attribute JSON for AD. When mapping proxyAddresses to userattribute_email, the connection is failing. Could you please advise what I'm doing wrong? Below is the JSON for your reference.

[ACCOUNTCLASS::objectClass#String,
ACCOUNTID::distinguishedName#String,
CREATED_ON::whenCreated#date,
COMMENTS::distinguishedName#String,
DISPLAYNAME::name#String,
DESCRIPTION::description#String,
LASTLOGONDATE::lastLogon#millisec,
LASTPASSWORDCHANGE::pwdLastSet#millisec,
NAME::sAMAccountName#String,
RECONCILATION_FIELD::CUSTOMPROPERTY26,
UPDATEDATE::whenChanged#date,
VALIDTHROUGH::accountExpires#millisec,
CUSTOMPROPERTY1::cn#String,
CUSTOMPROPERTY2::userPrincipalName#String,
CUSTOMPROPERTY3::sn#String,
CUSTOMPROPERTY4::homeDirectory#String,
CUSTOMPROPERTY5::co#String,
CUSTOMPROPERTY6::employeeNumber#String,
CUSTOMPROPERTY7::givenName#String,
CUSTOMPROPERTY8::title#String,
CUSTOMPROPERTY9::regioncode#String,
CUSTOMPROPERTY10::c#String,
CUSTOMPROPERTY11::uSNCreated#String,
CUSTOMPROPERTY12::logonCount#String,
CUSTOMPROPERTY13::physicalDeliveryOfficeName#String,
CUSTOMPROPERTY14::extensionAttribute1#String,
CUSTOMPROPERTY15::extensionAttribute2#String,
CUSTOMPROPERTY16::streetAddress#String,
CUSTOMPROPERTY17::mailNickname#String,
CUSTOMPROPERTY18::department#String,
CUSTOMPROPERTY19::countryCode#String,
CUSTOMPROPERTY20::employeeID#String,
CUSTOMPROPERTY21::manager#String,
CUSTOMPROPERTY22::homePhone#String,
CUSTOMPROPERTY23::mobile#String,
CUSTOMPROPERTY24::userAccountControl#String,
CUSTOMPROPERTY25::company#String,
CUSTOMPROPERTY26::objectGUID#Binary,
CUSTOMPROPERTY27::objectSid#Binary,
CUSTOMPROPERTY28::primaryGroupID#String,
CUSTOMPROPERTY29::st#String,
CUSTOMPROPERTY30::userAccountControl#String,
CUSTOMPROPERTY31::mail#String,
CUSTOMPROPERTY32::sAMAccountName#String,
CUSTOMPROPERTY33::extensionAttribute4#String,
CUSTOMPROPERTY34::extensionAttribute7#String,
CUSTOMPROPERTY35::extensionAttribute8#String,
CUSTOMPROPERTY36::extensionAttribute15#String,
CUSTOMPROPERTY37::extensionAttribute3#String,
CUSTOMPROPERTY38::extensionAttribute5#String,
CUSTOMPROPERTY39::extensionAttribute6#String,
CUSTOMPROPERTY40::extensionAttribute9#String,
CUSTOMPROPERTY41::extensionAttribute10#String,
CUSTOMPROPERTY42::extensionAttribute11#String,
CUSTOMPROPERTY43::extensionAttribute12#String,
CUSTOMPROPERTY44::extensionAttribute13#String,
CUSTOMPROPERTY45::extensionAttribute14#String,
userattribute_email::proxyAddresses#String,
CUSTOMPROPERTY52::l#String]

[rushikeshvartak]

Please share logs in text format

[Chirag_Gupta]

Hello @rushikeshvartak ,

Please find error below.

Errors : "
"2024-09-17T16:04:01.577+00:00","ecm","","null-f7r2m","","{"
"2024-09-17T16:04:01.577+00:00","ecm","","null-f7r2m",""," "ACCOUNT_ATTRIBUTE" : {"
"2024-09-17T16:04:01.577+00:00","ecm","","null-f7r2m",""," "ERROR" : [ "USERATTRIBUTE_EMAIL is not a domain property" ]"
"2024-09-17T16:04:01.577+00:00","ecm","","null-f7r2m",""," }"
"2024-09-17T16:04:01.577+00:00","ecm","","null-f7r2m","","}"
"2024-09-17T16:04:00.820+00:00","ecm","ldap.SaviyntGroovyLdapService","http-nio-8080-exec-9-f7r2m","ERROR","Error while saving the Connection: ACCOUNT_ATTRIBUTE : USERATTRIBUTE_EMAIL is not a domain property"
"2024-09-17T16:04:00.821+00:00","ecm","integration.ExternalConnectionCallService","http-nio-8080-exec-9-f7r2m","DEBUG","EXIT invokeExternalMethod"
"2024-09-17T16:03:59.750+00:00","ecm","certificatemgmt.CertificateManagementService","http-nio-8080-exec-20-f7r2m","DEBUG","Inside in saveCertificateByAlias..."

Thanks,

chirag

[rushikeshvartak]

Keep it capital

[Chirag_Gupta]

I changed it to uppercase, but the issue still persists.

[Chirag_Gupta]

@rushikeshvartak Have you had a chance to look into this?