and more in a single search tool across platforms. Read the announcement here. |
07/22/2023 09:50 PM
Hello,
We are trying to reconcile the accounts and entitlements from the database the job is getting successful, but the accounts and entitlements are not getting reconciled. We are getting the below warning in the Logs after running the jobs.
Accounts:
{log":"2023-07-23 04:14:00 | 777 [http-nio-8080-exec-8] DEBUG domain.JobcontrolController - checking AccountsImportFullJob status\n" |
{log":"23-Jul-2023 04:14:00.777 WARNING [http-nio-8080-exec-8] groovy.sql.Sql.asSql In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: SELECT count(*) as count FROM qrtz_fired_triggers where JOB_NAME='?'\n" | stream:"stderr" |
{log":"2023-07-23 04:14:00 | 777 [http-nio-8080-exec-8] DEBUG domain.JobcontrolController - AccountsImportFullJob is not running\n" |
{log":"2023-07-23 04:14:00 |
|
Entitlements:
{log":"2023-07-23 04:13:21 | 580 [http-nio-8080-exec-13] DEBUG domain.JobcontrolController - checking EntitlementValueImportJob status\n" |
{log":"23-Jul-2023 04:13:21.580 WARNING [http-nio-8080-exec-13] groovy.sql.Sql.asSql In Groovy SQL please do not use quotes around dynamic expressions (which start with $) as this means we cannot use a JDBC PreparedStatement and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: SELECT count(*) as count FROM qrtz_fired_triggers where JOB_NAME='?'\n" | stream:"stderr" |
{log":"2023-07-23 04:13:21 | 580 [http-nio-8080-exec-13] DEBUG domain.JobcontrolController - EntitlementValueImportJob is not running\n" |
{log":"2023-07-23 04:13:21 | 581 [http-nio-8080-exec-13] DEBUG domain.JobcontrolController - showTrailButtons: false\n" |
What Should we do to resolve this issue?
Best Regards,
Aditya Chadde
Solved! Go to Solution.
07/22/2023 10:28 PM
Hi @adityachadde ,
Can you share account and entitlement import xml and type of jobs you are using
07/23/2023 06:02 PM
Can you share more details about the same?
07/23/2023 06:35 PM - edited 07/23/2023 06:41 PM
Hi,
We are using below Accounts Import XML file content:
<dataMapping><sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="accountid" ><![CDATA[select ? from tablename u, tablename g where u.R_OBJECT_ID=g.R_OBJECT_ID]]></sql-query>
<mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="suspend" deleteaccountentitlement="true" ifusernotexists="noaction">
<mapfield saviyntproperty="accounts.name" sourceproperty="USERNAME" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty1" sourceproperty="USERADDRESS" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="EntitlementType" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.entitlementvalue" sourceproperty="entitlementvalue" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty2" sourceproperty="CLIENTCAPABILITY" type="number"></mapfield>
<mapfield saviyntproperty="accounts.customproperty3" sourceproperty="USERSOURCE" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty4" sourceproperty="USERLDAPDN" type="character"></mapfield>
<mapfield saviyntproperty="accounts.displayname" sourceproperty="USERLOGINNAME" type="character"></mapfield>
<mapfield saviyntproperty="accounts.customproperty5" sourceproperty="USERLOGINDOMAIN" type="character"></mapfield>
<mapfield saviyntproperty="accounts.accountid" sourceproperty="USERINITIALS" type="number"></mapfield>
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="SecuritySystem" type="character"></mapfield>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="Endpoint" type="character"></mapfield>
<mapfield saviyntproperty="accounts.status" sourceproperty="status" type="number"></mapfield>
</mapper></dataMapping>
Entitlement Value Import XML file content:
<dataMapping> <before-import> </before-import> <sql-query description="This is the Source DB Query">
<![CDATA[SELECT ? from tablename]]>
</sql-query>
<mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="true">
<mapfield saviyntproperty="securitysystems.systemname" sourceproperty="SecuritySystem" type="character"></mapfield>
<mapfield saviyntproperty="entitlementvalues.description" sourceproperty="Description" type="character"/>
<mapfield saviyntproperty="endpoints.endpointname" sourceproperty="Endpoint" type="character"></mapfield>
<mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="EntitlementType" type="character"/>
<mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/>
</mapper>
</dataMapping>
Below Jobs:
Best Regards,
Aditya Chadde
07/23/2023 08:08 PM
I see you're using the below queries for account and access import respectively. Can you please confirm? Also in the select statement can you please explicitly mention the column names and retry the import jobs again?
account import: select ? from tablename u, tablename g where u.R_OBJECT_ID=g.R_OBJECT_ID
entitlement import: SELECT ? from tablename
change it to "Select <column_name1>,<column_name2> from tablename.
07/23/2023 09:17 PM
Specify actual column names in query
07/23/2023 11:12 PM
Hi @adityachadde ,
Make sure to select proper column from target table instead of ?. Also, use the same selected column name as sourceproperty under mapper.
You can refer attached account and entitlement import xml for reference.
07/24/2023 06:10 AM
This seems to be mapping issue.
Please use below sample:
Account Import:
<dataMapping> <sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" > <![CDATA[select accountname,securitysystem,endpoint,entitlementtype,entitlementvalue,status from accounts]]> </sql-query> <mapper description="This is the mapping field for Saviynt Field name" accountnotinfileaction="Suspend" deleteaccountentitlement="true" ifusernotexists="noaction"> <mapfield saviyntproperty="accounts.name" sourceproperty="accountName" type="character"/> <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"/> <mapfield saviyntproperty="accounts.status" sourceproperty="status" type="character"/> </mapper> </dataMapping>
Entitlement Import:
<dataMapping> <sql-query description="This is the Source Database Query"> <![CDATA[select applicationname,endpointname,entitlementtype,entitlementvalue,entID,glossary,dataowner1,dataowner2,dataowner3,soxcritical,syscritical,status,description from db_import_ent]]> </sql-query> <mapper description="This is the mapping field for Saviynt Field name" deleteentitlementowner="true" entnotpresentaction="noaction" createentitlementtype="true" systems="SecSystemName"> <mapfield saviyntproperty="securitysystems.systemname" sourceproperty="applicationname" type="character"/> <mapfield saviyntproperty="endpoints.endpointname" sourceproperty="endpointname" type="character"/> <mapfield saviyntproperty="entitlementtypes.entitlementname" sourceproperty="entitlementtype" type="character"/> <mapfield saviyntproperty="entitlementvalues.entitlement_value" sourceproperty="entitlementvalue" type="character"/> <mapfield saviyntproperty="entitlementvalues.entitlementID" sourceproperty="entID" type="character"/> <mapfield saviyntproperty="entitlementvalues.entitlement_glossary" sourceproperty="glossary" type="character"/> <mapfield saviyntproperty="entitlementvalues.description" sourceproperty="description" type="character"/> <mapfield saviyntproperty="entitlementvalues.entowner1" sourceproperty="dataowner1" type="character"/> <mapfield saviyntproperty="entitlementvalues.entowner2" sourceproperty="dataowner2" type="character"/> <mapfield saviyntproperty="entitlementvalues.entowner3" sourceproperty="dataowner3" type="character"/> <mapfield saviyntproperty="entitlementvalues.sox_critical" sourceproperty="soxcritical" type="character"/> <mapfield saviyntproperty="entitlementvalues.sys_critical" sourceproperty="syscritical" type="character"/> <mapfield saviyntproperty="entitlementvalues.status" sourceproperty="status" type="number"/> </mapper> </dataMapping>
07/23/2023 10:36 PM - edited 07/23/2023 10:55 PM
07/26/2023 07:09 AM
Thank you @rushikeshvartak for the Suggestions!
EntitlementType was wrong in the entitlement Import JSON and in account import the query is not returning any records and both the issues are resolved now.
Best Regards,
Aditya Chadde