AD Provisioning Error - ERROR http-nio-8080-exec-1 sailpoint.connector.ADLDAPConnector:3871 - 1158736412 Exception occurred in handling Object Request.

 More often then not, this is because IIQ machine is not able to connect to IQService. 

• Check the status of IQService
• Check network
• Check firewalls

Full Stack Trace:

2021-09-28 15:10:11,539 ERROR http-nio-8080-exec-1 sailpoint.connector.ADLDAPConnector:3871 - 1158736412 Exception occurred in handling Object Request.

sailpoint.tools.GeneralException: Network is unreachable (connect failed)

        at sailpoint.connector.RPCService.execute(RPCService.java:429)

        at sailpoint.connector.ADLDAPConnector.handleObjectRequest(ADLDAPConnector.java:4203)

        at sailpoint.connector.ADLDAPConnector.provision(ADLDAPConnector.java:3862)

        at sailpoint.connector.ConnectorProxy.provision(ConnectorProxy.java:882)

        at sailpoint.integration.ConnectorExecutor.provision(ConnectorExecutor.java:165)

        at sailpoint.provisioning.PlanEvaluator.provision(PlanEvaluator.java:1541)

        at sailpoint.provisioning.PlanEvaluator.execute(PlanEvaluator.java:884)

        at sailpoint.provisioning.PlanEvaluator.execute(PlanEvaluator.java:788)

        at sailpoint.provisioning.PlanEvaluator.execute(PlanEvaluator.java:687)

        at sailpoint.api.Provisioner.execute(Provisioner.java:1685)

        at sailpoint.workflow.IdentityLibrary.provisionProject(IdentityLibrary.java:3058)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

        at sailpoint.server.ScriptletEvaluator.doCall(ScriptletEvaluator.java:136)

        at sailpoint.server.ScriptletEvaluator.evalSource(ScriptletEvaluator.java:65)

        at sailpoint.api.Workflower.evalSource(Workflower.java:5966)

        at sailpoint.api.Workflower.advanceStep(Workflower.java:5121)

        at sailpoint.api.Workflower.advance(Workflower.java:4510)

        at sailpoint.api.Workflower.startCase(Workflower.java:3091)

        at sailpoint.api.Workflower.launchSubcase(Workflower.java:5424)

        at sailpoint.api.Workflower.launchSubcases(Workflower.java:5317)

        at sailpoint.api.Workflower.advanceStep(Workflower.java:5108)

        at sailpoint.api.Workflower.advance(Workflower.java:4510)

        at sailpoint.api.Workflower.startCase(Workflower.java:3091)

        at sailpoint.api.Workflower.launchSubcase(Workflower.java:5424)

        at sailpoint.api.Workflower.launchSubcases(Workflower.java:5317)

        at sailpoint.api.Workflower.advanceStep(Workflower.java:5108)

        at sailpoint.api.Workflower.advance(Workflower.java:4510)

        at sailpoint.api.Workflower.startCase(Workflower.java:3091)

        at sailpoint.api.Workflower.launchSubcase(Workflower.java:5424)

        at sailpoint.api.Workflower.launchSubcases(Workflower.java:5317)

        at sailpoint.api.Workflower.advanceStep(Workflower.java:5108)

        at sailpoint.api.Workflower.advance(Workflower.java:4510)

        at sailpoint.api.Workflower.assimilate(Workflower.java:4160)

        at sailpoint.api.Workflower.handleWorkItem(Workflower.java:7638)

        at sailpoint.api.Workflower.process(Workflower.java:1824)

        at sailpoint.api.Workflower.process(Workflower.java:1847)

        at sailpoint.api.WorkflowSession.advance(WorkflowSession.java:473)

        at sailpoint.service.WorkflowSessionService.advance(WorkflowSessionService.java:105)

        at sailpoint.service.form.FormService.next(FormService.java:164)

        at sailpoint.service.form.FormService.submit(FormService.java:109)

        at sailpoint.rest.ui.form.BaseFormResource.submit(BaseFormResource.java:130)

        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

        at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)

        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)

        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)

        at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:160)

        at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)

        at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)

        at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)

        at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)

        at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)

        at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)

        at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)

        at org.glassfish.jersey.internal.Errors.process(Errors.java:315)

        at org.glassfish.jersey.internal.Errors.process(Errors.java:297)

        at org.glassfish.jersey.internal.Errors.process(Errors.java:267)

        at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)

        at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)

        at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)

        at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)

        at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)

        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)

        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)

        at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.rest.jaxrs.MethodOverrideFilter.doFilter(MethodOverrideFilter.java:90)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.rest.RestCsrfValidationFilter.doFilter(RestCsrfValidationFilter.java:69)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.rest.AuthenticationFilter.doFilter(AuthenticationFilter.java:100)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.web.SailPointContextRequestFilter.doFilter(SailPointContextRequestFilter.java:52)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.web.SailPointPollingRequestFilter.doFilter(SailPointPollingRequestFilter.java:109)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at sailpoint.web.ResponseHeaderFilter.doFilter(ResponseHeaderFilter.java:63)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197)

        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)

        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)

        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:201)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)

        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:544)

        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)

        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)

        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)

        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364)

        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:616)

        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831)

        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1629)

        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

        at java.lang.Thread.run(Thread.java:748)

Caused by: java.net.SocketException: Network is unreachable (connect failed)

        at java.net.PlainSocketImpl.socketConnect(Native Method)

        at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:476)

        at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:218)

        at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:200)

        at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:394)

        at java.net.Socket.connect(Socket.java:606)

        at java.net.Socket.connect(Socket.java:555)

        at java.net.Socket.<init>(Socket.java:451)

        at java.net.Socket.<init>(Socket.java:228)

        at sailpoint.connector.RPCService.execute(RPCService.java:412)

        ... 113 more

SailPoint Identity IQ JDBC Aggregation: A common mistake

 A common mistake that I have seen being made is how the aggregation query is written. 

Let's say there are three tables:

1. User
2. Groups
3. Membership

The names are exactly what is contained in the tables, User table contains the user data, Groups table contains the group data, Membership table contains the membership data.

Pay attention to both the queries, both will return the result and mostly pass the unit testing. The issue is that if a user is not part of any groups, they would not appear in the result set of query1

Incorrect Query

SELECT User.*, Membership.groupName 

FROM User, Membership 

WHERE User.Id = Membership.UserId;

Correct Query

SELECT User.*, Membership.groupName

FROM User

LEFT JOIN Membership 

ON User.Id = Membership.UserId;

Hope it helps !!

SailPoint IIQ: Delete WorkItems assigned to a User

• Query the 'spt_work_item' table. Pay attention to 'id' column(used for deleting the workitem) and 'owner' column (used to identifying the owner
• Use the console utility to delete the workitem
• Navigate to <tomcat>/webapps/iiq/WEB-INF/bin/
• Execute ./iiq console
• delete workitem ff8080817bffde5f017c029a820700ab (where id is the one you queried from database)
• If multiple ids have to be deleted, you can create a file with content like:

delete workitem ff8080813eb1de6f013eb3d811f10081
delete workitem ff8080813eb1de6f013eb3d82b6b0095
delete workitem ff8080813eb1de6f013eb3d83b1000a9
delete workitem ff8080813eb1de6f013eb3d846b900bd
delete workitem ff8080813eb1de6f013eb3d855cb00d1

•  Within IIQ console: source deleWorkItem.txt

Search for text within files in linux

 find ./ -name '*.txt' | xargs grep yourtext

./: root path to search

-name: file name

SailPoint IdentityIQ : Initialize LCM Module

> Shutdown tomcat
> cd apache_home/webapps/iiq/WEB-INF/bin
> ./iiq console
> import init-lcm.xml

> quit console
> Restart tomcat

Start mySQL on Linux

 Use one of the following options depending on your environment

service mysql start
service mysql stop
service mysql restart

service mysqld start 
service mysqld stop 
service mysqld restart

/etc/init.d/mysqld start 
/etc/init.d/mysqld stop 

/etc/init.d/mysqld restart 

Create Database Scheme for Your Custom Application via SQL File

For many activities like developing a web application, creating data lake or creating trusted/target system for learning IAM, a custom Database schema is required.

There is always an option to use tools (like SQL developer) or execute one off commands. This works well but the only issue is that we have to set these databases up so frequently that it would be nice to have a template which can be used everytime.

For this template, we simply need to create a .sql file and store it. Every time a new database has to be created, this can be leveraged. You will still have to update the table names etc.

This exercise will also help you understand, how to share your database creation details with fellow developers or even how the IAM tools like SailPoint IIQ or Oracle Identity Manager (RCU) shares the same with us.

I have used a MySQl server but it will work on all RDBMS with minor changes.

Create a .sql file and use the below structure:

/*

SQL file to create a custom database

-- Date: 2021-19-09 08:15

*/

CREATE DATABASE customapp;

SET GLOBAL validate_password_policy=LOW;

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser' IDENTIFIED BY 'admin@12345';

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser'@'%' IDENTIFIED BY 'admin@12345';

GRANT ALL PRIVILEGES ON customapp.*

    TO 'appUser'@'localhost' IDENTIFIED BY 'admin@12345';

USE customapp;

CREATE TABLE `UserTable` (

  `dbID` varchar(45) NOT NULL,

  `empID` varchar(45) DEFAULT NULL,

  `userName` varchar(45) DEFAULT NULL,

  `Inactive` varchar(45) DEFAULT NULL,

  `lastlogin` date DEFAULT NULL,

  PRIMARY KEY (`dbID`)

);

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('112','1a2c3a','RichardJackson','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('113','1a2c3b','MariaWhite','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('114','1a2c3c','CharlesHarris','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('115','1a2c3d','SusanMartin','TRUE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('156','1a2c3a4a','LarryMorgan','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('159','1a2c3a4d','MelissaBailey','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('160','1a2c3a4e','FrankRivera','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('161','1a2c3b4a','BrendaCooper','TRUE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('162','1a2c3b4b','ScottRichardson','FALSE','2009-04-04');

INSERT INTO `financeuser` (`dbID`,`empID`,`userName`,`Inactive`,`lastlogin`) VALUES ('163','1a2c3b4c','AmyCox','FALSE','2009-04-04');

Now login into database:

> mysql -u root -p

> source fileName.sql

The sql file can be shared and the devs will be able to create the schema in their sandboxes.

Hope this helps !!!