Click HERE to see how Saviynt Intelligence is transforming the industry. |
03/20/2023 12:04 PM
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:
Example account:
Solved! Go to Solution.
03/20/2023 12:54 PM
Can you run the job for 1 user and see if it completes successfully.
03/20/2023 01:51 PM
Hi,
I ran the job for 1 user and got the same issue
thanks
Joshua
03/20/2023 07:57 PM
Try changing mapper data type to number
03/21/2023 09:36 AM - edited 03/23/2023 10:33 AM
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.
03/31/2023 11:40 AM
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
04/03/2023 09:58 AM
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.