Even after attaching jdbc provision rule still cant write into csv file through SQL LOADER connector

Identity Security Cloud (ISC) ISC Discussion and Questions
1

Hi Guys,

Even after attaching jdbc rule i am not able to provision into csv file through sql loader. below rule i used . But i am only trying to provision few given in rule but still no luck may i know what should i do to do provisoning

these are the headers of csv

racerId (1st part) emploeeNumber racerId (2nd part) employeeNrOld sn givenName foreName surnamePrefix surnameAppendix title birthName birthDate birthPlace gender preferredLanguage costCenter companyCode l subDepartment subDepartmentLong department departmentLong homePostalCode homeCity homeStreet homeAddressAdd homeCountry homeCountryCode snReal givenNameReal lReal homeCityReal homeStreetReal homeAddressAdd titleNational retireDate retireReason hireDate employeeType employeeState accountingArea personnelArea personnelSubArea personnelStructKey telephoneNumber facsimileTelephoneNumber roomNumber EmployeeTypeInternal Member Flag HireReason formerContractEmployeeNumber formerContractCompanyCode formerContractEmail ReactivationDate partnerDUNSnumber LastValidationDate mgmtLevel VPNInternal mobile CompanyVehicleGroup GID 
import sailpoint.object.*;
import java.sql.*;
import java.util.*;

Map result = new HashMap();

try {
    if (plan != null && connection != null) {
        List accountRequests = plan.getAccountRequests();

        if (accountRequests != null && accountRequests.size() > 0) {
            for (int i = 0; i < accountRequests.size(); i++) {
                Object acctReqObj = accountRequests.get(i);

                String operation = acctReqObj.getOperation().toString();

                if ("Create".equals(operation)) {

                    String emploeeNumber = (String) acctReqObj.getAttributeValue("emploeeNumber");
                    String givenName = (String) acctReqObj.getAttributeValue("givenName");
                    String sn = (String) acctReqObj.getAttributeValue("sn");
                    String mgmtLevel = (String) acctReqObj.getAttributeValue("mgmtLevel");
                    String department = (String) acctReqObj.getAttributeValue("department");

                    log.debug("Creating account: " + emploeeNumber);

                    String sql = "INSERT INTO vcd (emploeeNumber, givenName, sn, mgmtLevel, department) VALUES (?, ?, ?, ?, ?)";

                    PreparedStatement stmt = connection.prepareStatement(sql);
                    stmt.setString(1, emploeeNumber);
                    stmt.setString(2, givenName);
                    stmt.setString(3, sn);
                    stmt.setString(4, mgmtLevel);
                    stmt.setString(5, department);

                    int rowsAffected = stmt.executeUpdate();
                    log.debug("Rows affected: " + rowsAffected);

                    String packSQL = "pack table vcd";
                    Statement packStmt = connection.createStatement();
                    packStmt.execute(packSQL);
                    packStmt.close();

                    stmt.close();

                    log.info("Account created successfully: " + emploeeNumber);

                    result.put("result", "success");
                    result.put("accountId", emploeeNumber);

                } else if ("Delete".equals(operation)) {

                    log.info("Delete operation received");
                    result.put("result", "Delete not supported");

                } else {

                    log.debug("Unsupported operation: " + operation);
                    result.put("result", "Operation not supported");
                }
            }
        }
    }

} catch (Exception e) {

    log.error("Failed to create account: " + e.getMessage(), e);
    result.put("error", "Failed to create account: " + e.getMessage());
    result.put("result", "failed");
}

return result;
2

Hi @Rakesh_Singh_1234

As mentionned by Sailpoint documentation Supported Features

image
image1812×867 81.8 KB

For some features : Provision accounts, Password Management, … you should contact first Sailpoint to activate those feaures in your tenant.

3

@Rakesh_Singh_1234 Your rule need some changes. Please refer the below article and correct your script.

https://community.sailpoint.com/t5/Identity-Security-Cloud-Wiki/IdentityNow-Rule-Guide-JDBC-Provision-Rule/ta-p/77339

4

may i know what is wrong with my rule?

5

@Rakesh_Singh_1234 try with this code.

import sailpoint.object.*;
import java.sql.*;
import java.util.*;

// Initialize ProvisioningResult object instead of a Map
ProvisioningResult result = new ProvisioningResult();


public String getAttributeStringValue(AccountRequest acctReq, String attrName) {
    AttributeRequest attrReq = acctReq.getAttributeRequest(attrName);
    if (attrReq != null && attrReq.getValue() != null) {
        return attrReq.getValue().toString();
    }
    return null;
}

try {
    if (plan != null && connection != null) {
        List<AccountRequest> accountRequests = plan.getAccountRequests();

        if (accountRequests != null && !accountRequests.isEmpty()) {
            for (AccountRequest acctReq : accountRequests) {
                AccountRequest.Operation operation = acctReq.getOperation();

                if (AccountRequest.Operation.Create.equals(operation)) {
                    // FIX: AccountRequest does not have getAttributeValue(). 
                    // You must get the AttributeRequest first, check for null, and then get its value.
                    String emploeeNumber = getAttributeStringValue(acctReq, "emploeeNumber");
                    String givenName     = getAttributeStringValue(acctReq, "givenName");
                    String sn            = getAttributeStringValue(acctReq, "sn");
                    String mgmtLevel     = getAttributeStringValue(acctReq, "mgmtLevel");
                    String department    = getAttributeStringValue(acctReq, "department");

                    log.debug("Creating account: " + emploeeNumber);

                    String sql = "INSERT INTO vcd (emploeeNumber, givenName, sn, mgmtLevel, department) VALUES (?, ?, ?, ?, ?)";

                    PreparedStatement stmt = null;
                    Statement packStmt = null;

                    try {
                        stmt = connection.prepareStatement(sql);
                        stmt.setString(1, emploeeNumber);
                        stmt.setString(2, givenName);
                        stmt.setString(3, sn);
                        stmt.setString(4, mgmtLevel);
                        stmt.setString(5, department);

                        int rowsAffected = stmt.executeUpdate();
                        log.debug("Rows affected: " + rowsAffected);

                        String packSQL = "pack table vcd";
                        packStmt = connection.createStatement();
                        packStmt.execute(packSQL);

                        log.info("Account created successfully: " + emploeeNumber);

                        // Set result to COMMITTED
                        result.setStatus(ProvisioningResult.STATUS_COMMITTED);

                    } finally {
                        if (packStmt != null) packStmt.close();
                        if (stmt != null) extern stmt.close();
                    }

                } else if (AccountRequest.Operation.Delete.equals(operation)) {
                    log.info("Delete operation received but not supported");
                    
                    result.setStatus(ProvisioningResult.STATUS_FAILED);
                    result.addWarning("Delete operation is currently not supported.");
                    
                } else {
                    log.debug("Unsupported operation: " + operation);
                    
                    result.setStatus(ProvisioningResult.STATUS_FAILED);
                    result.addWarning("Unsupported operation: " + operation);
                }
            }
        }
    }
} catch (Exception e) {
    log.error("Failed to process account provisioning: " + e.getMessage(), e);
    
    // Set the result status to FAILED and attach the error details
    result.setStatus(ProvisioningResult.STATUS_FAILED);
    result.addError(e.getMessage());
}

return result;
6

Check the ccg.log or connector logs and add a few log.info() statements at the beginning of the rule to confirm execution.