JDBC Provisioning rule writing null values

kwhipple · April 18, 2025, 8:08pm

I have a working JDBC provisioning rule but I have found it is filtering values that already match so writing null values.

We have read-only access to application MSSQL database table so write to an alternate table for provisioning that the application then imports from.
*Some fields don’t allow null values so write operation frequently fails
*We need to send a complete record on any attribute change otherwise the application import job overwrites valid values with null

IdentityNow-Rule-Guide-JDBC-Provision-Rule comments mention turning nofiltering to true but doesn’t provide any documentation.

Google AI returns:

By default, SailPoint IdentityNow may not send attribute changes to the connector if the value being sent is identical to the existing value in the link object.
noFiltering=true: Setting this flag to true in the provisioning plan instructs SailPoint to bypass this filtering mechanism.

We tried using aliases as mentioned by ‘bilal’ in this article but then we can’t turn on attribute sync for those attributes. There are a lot of articles for IIQ and workflow but not ISC JDBC Provisioning Rules.

I found ProvisioningPlan.GenericRequest for setting an argument but all my attempts at setting ‘nofilterting=true’ have failed.

Same behavior was mentioned in this post but he found a solution that worked without needing to turn off filtering.

This post talks about it for IIQ but not ISC>

Any suggestions would be appreciated.

paul_hilchey · April 18, 2025, 8:39pm

Hi @kwhipple can you change your provisioning rule to: read the appropriate row from the application table, apply the updates from the account request, then write a complete record to the alternate table?

kwhipple · April 22, 2025, 7:50pm

Thanks for the suggestion and it seems to be working but now since I also enabled update/modify provisioning plan it forces a refresh of the account after a disable account causing it to write another entry re-enabling the account since that is what is in the main table. I need to find out how to disable the refresh or figure out a way to write the latest value for disable from the alternate table. Any suggestions?

kwhipple · April 29, 2025, 11:17pm

@paul_hilchey Is this code what you were referring to where if the provisioning plan attribute is null it returns the application attribute?

public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
		rtnValue = null;
		if (acctReq != null) {
			AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
			if (attrReq != null) {
				rtnValue = attrReq.getValue();
			} else {
				String attrVal = application.getStringAttributeValue(attribute);
				rtnValue = attrVal.getValue();
			} 
		}
		return rtnValue;
	}

paul_hilchey · April 30, 2025, 3:42am

Hi @kwhipple ,
I had in mind something like this:

if (request.getOp() == AccountRequest.Operation.Modify) {
  // query the account_ro_table for the current account attributes
  PreparedStatement pss = connection.prepareStatement("select id, first_name, last_name from account_ro_table where id=?");
  pss.setString(1, acctRequest.getNativeIdentity())
  ResultSet rs = pss.executeQuery()

  if (rs.next()) {
    // default values to be what is in the account_ro_table
    String firstName rs.getString(2);
    String lastName = rs.getString(3);

    // then update with values from the account request
    for (AttributeRequest attrReq : acctRequest.getAttributeRequests()) {
      String attrName = attrReq.getName();
      if (attrName.equals("firstName"))
        firstName = attrReq.getValue();
      else if (attrName.equals("lastName"))
        lastName = attrReq.getValue();
    }

    // write out an update record to the change table
    PreparedStatement psi = connection.prepareStatement("insert into account_change_table(id,first_name,last_name) values(?,?,?)");
    psi.setString(1, acctRequest.getNativeIdentity());
    psi.setString(2, firstName);
    psi.setString(3, lastName);
    int rows = psi.executeUpdate();
  }
}

kwhipple · April 30, 2025, 7:11pm

Since I’m working with 30+ attributes and need them for all operations I am using that function. Here is my code with no logging and since the application class occasionally writes null values when it shouldn’t I will work on incorporating your db query suggestion:

import ...;

ProvisioningResult result = new ProvisioningResult();
String insertQuery = "INSERT INTO user_middleware_dev( PartitionID, LastName, FirstName, MiddleName, PersonnelTypeID, Disabled, Text1, Text2, Text3, Text4, Text5, Text6, Text7, Text8, Text9, Text10, Text11, Text12, Text13, Text14, Text15, Text16, Text17, Text18, Text19, Text20, Text21, Text22, Text23, Text24, Text25, Logical1, Date1, Date2, Date3, Date4, EmailAddress, ManagerEmail ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";

	public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {
		rtnValue = null;
		if (acctReq != null) {
			AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
			if (attrReq != null) {
				rtnValue = attrReq.getValue();
			} else {
				String attrVal = application.getStringAttributeValue(attribute);
				rtnValue = attrVal.getValue();
			}
		}
		return rtnValue;
	}

	public void writeToDB(AccountRequest account) throws Exception {
		String PartitionID = getAttributeRequestValue(account, "PartitionID");
		String LastName = getAttributeRequestValue(account, "LastName");
		String FirstName = getAttributeRequestValue(account, "FirstName");
		String MiddleName = getAttributeRequestValue(account, "MiddleName");
		String PersonnelTypeID = getAttributeRequestValue(account, "PersonnelTypeID");
		String Disabled = getAttributeRequestValue(account, "Disabled");
		String EmployeeID = getAttributeRequestValue(account, "Text1");
		String jobDescription = getAttributeRequestValue(account, "Text2");
		String departmentName = getAttributeRequestValue(account, "Text3");
		String locationName = getAttributeRequestValue(account, "Text4");
		String departmentId = getAttributeRequestValue(account, "Text5");
		String employeeType = getAttributeRequestValue(account, "Text6");
		String connectionType = getAttributeRequestValue(account, "Text7");
		String workPhone = getAttributeRequestValue(account, "Text8");
		String addressLine1 = getAttributeRequestValue(account, "Text9");
		String addressLine2 = getAttributeRequestValue(account, "Text10");
		String city = getAttributeRequestValue(account, "Text11");
		String postalCode = getAttributeRequestValue(account, "Text12");
		String country = getAttributeRequestValue(account, "Text13");
		String state = getAttributeRequestValue(account, "Text14");
		String regionId = getAttributeRequestValue(account, "Text15");
		String regionName = getAttributeRequestValue(account, "Text16");
		String geoRegion = getAttributeRequestValue(account, "Text17");
		String locationId = getAttributeRequestValue(account, "Text18");
		String hrStatus = getAttributeRequestValue(account, "Text19");
		String companyId = getAttributeRequestValue(account, "Text20");
		String companyName = getAttributeRequestValue(account, "Text21");
		String departmentbelongsto = getAttributeRequestValue(account, "Text22");
		String functionalunit = getAttributeRequestValue(account, "Text23");
		String action = getAttributeRequestValue(account, "Text24");
		String actionreason = getAttributeRequestValue(account, "Text25");
		String ismanager = getAttributeRequestValue(account, "Logical1");
		String startDate = getAttributeRequestValue(account, "Date1");
		String endDate = getAttributeRequestValue(account, "Date2");
		String expectedenddate = getAttributeRequestValue(account, "Date3");
		String assignmeneteffectivedate = getAttributeRequestValue(account, "Date4");
		String email = getAttributeRequestValue(account, "EmailAddress");
		String managerEmail = getAttributeRequestValue(account, "ManagerEmail");

		try {
			String dbURL3 = "jdbc:sqlserver://xxx;databaseName=SailPoint;integratedSecurity=false;trustServerCertificate=true;Trusted_Connection=SSPI"; 
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			Connection connection = DriverManager.getConnection(dbURL3);

			PreparedStatement statement = connection.prepareStatement(insertQuery);
			statement.setString(1,PartitionID);
			statement.setString(2,LastName);
			statement.setString(3,FirstName);
			statement.setString(4,MiddleName);
			statement.setString(5,PersonnelTypeID);
			statement.setString(6,Disabled);
			statement.setString(7,EmployeeID);
			statement.setString(8,jobDescription);
			statement.setString(9,departmentName);
			statement.setString(10,locationName);
			statement.setString(11,departmentId);
			statement.setString(12,employeeType);
			statement.setString(13,connectionType);
			statement.setString(14,workPhone);
			statement.setString(15,addressLine1);
			statement.setString(16,addressLine2);
			statement.setString(17,city);
			statement.setString(18,postalCode);
			statement.setString(19,country);
			statement.setString(20,state);
			statement.setString(21,regionId);
			statement.setString(22,regionName);
			statement.setString(23,geoRegion);
			statement.setString(24,locationId);
			statement.setString(25,hrStatus);
			statement.setString(26,companyId);
			statement.setString(27,companyName);
			statement.setString(28,departmentbelongsto);
			statement.setString(29,functionalunit);
			statement.setString(30,action);
			statement.setString(31,actionreason);
			statement.setString(32,ismanager);
			statement.setString(33,startDate);
			statement.setString(34,endDate);
			statement.setString(35,expectedenddate);
			statement.setString(36,assignmeneteffectivedate);
			statement.setString(37,email);
			statement.setString(38,managerEmail);

			int executeUpdate = statement.executeUpdate();
			result.setStatus(ProvisioningResult.STATUS_COMMITTED);
		} catch (SQLException ex) {
			result.setStatus(ProvisioningResult.STATUS_FAILED);
			result.addError(ex.getMessage());
		}
	}

// Code
if (plan != null) {
	List accounts = plan.getAccountRequests();
	if ((accounts != null) && (accounts.size() > 0)) {
		for (AccountRequest account : accounts) {
			try {
				if (AccountRequest.Operation.Create.equals(account.getOperation())) {
					try {
						writeToDB(account);
					} catch (Exception e) {
						result.setStatus(ProvisioningResult.STATUS_FAILED);
						result.addError(e);
					}
				} else if (AccountRequest.Operation.Modify.equals(account.getOperation())) {
					// Modify Operation is not supported;
				} else if (AccountRequest.Operation.Delete.equals(account.getOperation())) {
					// Delete Operation is not supported;
				} else if (AccountRequest.Operation.Disable.equals(account.getOperation())) {
					try {
						writeToDB(account);
					} catch (Exception e) {
						result.setStatus(ProvisioningResult.STATUS_FAILED);
						result.addError(e);
					}
				} else if (AccountRequest.Operation.Enable.equals(account.getOperation())) {
					try {
						writeToDB(account);
					} catch (Exception e) {
						result.setStatus(ProvisioningResult.STATUS_FAILED);
						result.addError(e);
					}
				}
			} catch (Exception e) {
				result.setStatus(ProvisioningResult.STATUS_FAILED);
				result.addError(e);
			}
		}
	}
}
return result;

system · June 29, 2025, 7:12pm

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.