Query another tables on user import preprocessor

[fuko]

Hi forum,

We have a complex username generation logic that we have to use custom JAR as in https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter03-User-Management/User-Impo.... It runs well until we have another request to check the uniqueness of generated username against another database. Instead of querying that database directly in the JAR, we go with the approach of onboarding that database as a target application and then query for the accounts of that endpoint to check uniqueness.

    private List<String> checkDuplication(Statement statement, List<String> listOfCandidates) throws SQLException {
        List<String> newListOfCandidates = new ArrayList<>();
        ResultSet rs = statement.executeQuery("select a.name from accounts a join endpoints e on e.endpointkey = a.endpointkey and e.endpointname like '%ADAdminDB%'");
        while (rs.next()) {
            for (String logonName : listOfCandidates) {
                if (!logonName.equals(rs.getString("name"))) {
                    newListOfCandidates.add(logonName);
                }
            }
        }
        return newListOfCandidates;
    }

I have tested that query on Data Analyzer but when running as preprocess, it always fails with the message of "Table 'ssminlp.accounts' doesn't exist".  Following this topic, https://forums.saviynt.com/t5/application-access-governance/error-in-users-import-error-while-proces... to change the table name to 'currentaccounts' but still encountered the same thing kind of error of "Table 'ssminlp.currentaccounts' doesn't exist".

2024-04-09T13:09:21+07:00-ecm--null-dw9vx--09-Apr-2024 06:09:20.653 INFO [http-nio-8080-exec-14] com.abc.sav.preprocessor.UsernameGeneratingPreprocessor.doProcess Logon Name Generation Logic initiated!

2024-04-09T13:09:21+07:00-ecm--null-dw9vx--09-Apr-2024 06:09:20.690 INFO [http-nio-8080-exec-14] com.abc.sav.preprocessor.UsernameGeneratingPreprocessor.doProcess Exception Occurred: java.sql.SQLSyntaxErrorException: Table 'ssminlp.accounts' doesn't exist

2024-04-09T13:09:20+07:00-ecm-services.ImportSAvDataUserService-http-nio-8080-exec-14-dw9vx-ERROR-Error while processing data:

2024-04-09T13:09:20+07:00-ecm--null-mxgnq--java.sql.SQLSyntaxErrorException: Table 'ssminlp.accounts' doesn't exist at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) at com.abc.sav.preprocessor.UsernameGeneratingPreprocessor.checkDuplication(UsernameGeneratingPreprocessor.java:143)  

What could be the right name to query in here? Please help me shed a light on.

[PremMahadikar]

Hi @fuko ,

I think this forum article answers your use case: Solved: Dataset usage in User Import Preprocessor - Saviynt Forums - 55992

Just to highlight:

In this article, they have used upper case while using datasets table and they got a similar error: Table 'ssminlp.currentdataset_values' doesn't exist"

Try using upper case while using accounts.

"ACCOUNTS": "SELECT name,customproperty1,customproperty2 from accounts"

Please refer the article.

If this answers your question, please consider selecting Accept As Solution and hit Kudos

[fuko · Accepted solution]

Thanks @PremMahadikar  for the hints.

It turns out like in order to run the below query:

ResultSet rs = statement.executeQuery("select a.name from accounts a join endpoints e on e.endpointkey = a.endpointkey and e.endpointname like '%ADAdminDB%'");

I have declared it under the MODIFYUSERDATAJSON as

{
  "ADDITIONALTABLES": {
	"DATASET_VALUES":"SELECT a.name from accounts a join endpoints e on e.endpointkey = a.endpointkey and e.endpointname like '%ADAdminDB%'"
  },

Then under the custom Java preprocessor class, I need to retrieve the name of this temporary tables through a map and then build the query.

// Look up the real table name
String currentDataSetValueTableName= (String) tempTableNamesMap.get("CURRENTDATASET_VALUES");
// Then build the query
String adDbPortalQueryString = "select name from " + currentDataSetValueTableName;

[rushikeshvartak]

Table name in upper case ACCOUNTS