Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Accounts Import from Database getting stuck

JoshuaLawrence
New Contributor III
New Contributor III

Hi,

We have an issue where the account import from database job is getting stuck. It is able to import all of the accounts but it seems to get stuck after trying to correlate users to accounts. If I remove the User Account Correlation rule, the job will complete successfully. However if I add it back, the job will get stuck. 

Here is our account import xml: 

<dataMapping>
<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >
<![CDATA[select Cardholder.Guid,Cardholder.FirstName,Cardholder.LastName,Concat(Cardholder.LastName,', ',CardHolder.FirstName) as Name,Cardholder.Email,Cardholder.MobilePhoneNumber,Cardholder.Status,Cardholder.ExpirationDate,CustomFieldValue.CF5b720d20fa254a8cb80f81971a07eb24 as employeeID,CustomFieldValue.CF4ebb6297d514449d8b981d6ce15000be as companyName,CustomFieldValue.CFa872350a88de483092ff6a446fcfa0e4 as employmentType,'Genetec_test1' as SECURITYSYSTEM,'Genetec_test1' as ENDPOINT, null as entitlementtype, null as entitlementvalue from Cardholder INNER JOIN CustomFieldValue
ON (Cardholder.Guid = CustomFieldValue.Guid)]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="Guid" type="character"/>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="Name" type="character"/>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="FirstName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="LastName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="Email" type="character"/>
<mapfield saviyntproperty="accounts.customproperty4" sourceproperty="MobilePhoneNumber" type="character"/>
<mapfield saviyntproperty="accounts.customproperty5" sourceproperty="ExpirationDate" type="date"/>
<mapfield saviyntproperty="accounts.customproperty6" sourceproperty="employeeID" type="character"/>
<mapfield saviyntproperty="accounts.customproperty7" sourceproperty="companyName" type="character"/>
<mapfield saviyntproperty="accounts.customproperty8" sourceproperty="employmentType" type="character"/>
<mapfield saviyntproperty="accounts.status" sourceproperty="Status" type="number"/>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="SECURITYSYSTEM" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="ENDPOINT" type="character"/>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"/>
</mapper>
</dataMapping>

 

And here are the logs where is seems to get stuck:

2023-03-20/17:11:44.244 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.AccountsImportService - deleting account entitlement
2023-03-20/17:11:44.244 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.AccountsImportService - statusAndThresholdConfig is null
2023-03-20/17:11:44.244 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.AccountsImportService - account_not_in_file_action - Suspend, incrementalColumnExternal - null
2023-03-20/17:11:44.245 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.AccountsImportService - Suspending accounts using the Qry -UPDATE ACCOUNTS SET STATUS='SUSPENDED FROM IMPORT SERVICE',NAME=CONCAT(name,'-Deleted on-','2023-03-20 17:11:44') , JOBID=760748 WHERE JOBID not in (760748) and SYSTEMID in (70) AND STATUS <> 'SUSPENDED FROM IMPORT SERVICE'
2023-03-20/17:11:45.718 [{}] [https-jsse-nio-443-exec-4] INFO domain.JobcontrolController - Auditing for list of JobControl
2023-03-20/17:11:45.721 [{}] [https-jsse-nio-443-exec-4] INFO domain.JobcontrolController - Auditing process completed for JobControl
2023-03-20/17:11:51.102 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.AccountsImportService - correlateInactiveAccounts: null
2023-03-20/17:11:51.368 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.ImportExternalDbService - Associating Users and Accounts
2023-03-20/17:11:53.217 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.ImportExternalDbService - Orphan Accounts- 50560
2023-03-20/17:11:53.218 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.ImportExternalDbService - Rule: (users.employeeid) = accounts.customproperty6
2023-03-20/17:11:53.218 [{}] [quartzScheduler_Worker-3] DEBUG saviynt.ImportExternalDbService - sql =
SELECT USERS.USERKEY AS USERKEY, ACCOUNTS.ACCOUNTKEY AS ACCOUNTKEY FROM ACCOUNTS ACCOUNTS
LEFT JOIN USER_ACCOUNTS UA ON ACCOUNTS.ACCOUNTKEY = UA.ACCOUNTKEY
LEFT JOIN USERS USERS ON
(users.employeeid) = accounts.customproperty6
WHERE ACCOUNTS.ENDPOINTKEY = 74 AND ACCOUNTS.STATUS IN ('Active','1','Manually Provisioned')
AND ACCOUNTS.ACCOUNTKEY IS NOT NULL AND UA.ACCOUNTKEY IS NULL
AND ACCOUNTS.NAME IS NOT NULL AND USERS.USERNAME IS NOT NULL

We have verified that customproperty6 is stored for every account as well.

Here is our correlation rule:

JoshuaLawrence_0-1679338866275.png

Example account:

JoshuaLawrence_1-1679338989016.png

 

6 REPLIES 6

SB
Saviynt Employee
Saviynt Employee

Can you run the job for 1 user and see if it completes successfully. 


Regards,
Sahil

JoshuaLawrence
New Contributor III
New Contributor III

Hi,

I ran the job for 1 user and got the same issue

thanks

Joshua

rushikeshvartak
All-Star
All-Star

Try changing mapper data type to number


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi, just to clarify, do you mean change the data type of customproperty6 to number in the xml? In that case, it did not seem to fix the issue, the job still gets stuck.

I am actually able to correlate with a different user attribute such as user.email with account.customproperty3, so the issue must have to with user.employeeid 

Update: I ended up storing the employeeID as the account name during import, and then set the correlation rule to correlate if user.employeeid = account.accountname, and this seemed to fix the issue.