The Identity Governance product from Micro Focus is a great tool for ensuring that the accounts and permissions your employees have access to are properly governed.

You probably have an Identity Management system that is in charge of provisioning and deprovisioning access for users based on Human Resource’s changes to their employment status. Yet, you still need to prove that all those assignments are correct. Additionally you might have allowed ad hoc requests for access, or you might need to reapprove things on a regular schedule.

There might be additional systems that you do not directly manage via Identity Management but are still important to your organization.

In that case Identity Governance may be just what you need.

You set up Collectors to each system that pulls in the users, the accounts they have enabled, and the various permissions those system might grant them. This is usually polled, and not event driven (Though the newest version allows Change based collectors for eDirectory and Active Directory) so you have to collect this data on a schedule. Depending on how much data you have, and how often you collect, your database that stores it all is going to grow and grow.

You need to clean the database up periodically, and probably want to script it to clean up on a regular basis. What is nice is you can specify a date or a number of days to start the cleanup range. That is from a specific date, or everything older than say 10 days ago.

This is important, as you can imagine, Identity systems often have tens of thousands of users with tens to dozens of connected systems. Imagine how much database space collecting that much info, say every day could eat up.

I suppose I would have preferred if the system itself would do the purging, as a service or something else, however the approach taken was to ship a script that supports database cleanup operations.

I thought it would be helpful to show how the interface looks and how to use it, as an extension to what the documentation describes. I think the documentation does cover the bare bones minimum but could stand to be thickened a bit with more content such as this article.

You will find the script on Linux at:
/opt/netiq/idm/apps/idgov/bin/data-purge-utility.sh

You can run it in a console mode, but it asks you for 11 different parameters which are slightly painful to retype each time. Nicely, they provide a way to provide a properties file with all those values.

Here is what a session using the console mode might start out as:

[gcarman@idg bin]$ ./data-purge-utility.sh

Data Purge Utility
  This utility allows you to remove historical data from the operations database in
  NetIQ Identity Governance. It can purge items that are deleted or are no longer in
  use and are older than the current date and time or a specified retention date and
  time.
---------------------------------------------------------------------------------------

Identity Governance Service Host or IP Address: idg.acme.com
Identity Governance Service Port Number (Default=8080): 8443
Identity Governance Service Protocol: https,http (Default=http): https
Identity Governance Data Administrator User Name: gcarman
Identity Governance Data Administrator User Password:
Authentication Service (OSP) Host Name or IP Address (Default=idg.acme.com):
Authentication Service (OSP) Port Number (Default=8080): 8443
Authentication Service (OSP) server protocol: https,http (Default=http): https
Identity Governance OAuth (OSP) Client ID (Default=iac):
Identity Governance OAuth (OSP) Client Password:
Retention days: 10

Invalid client-id/password for authentication (OSP) server: iac/********

Identity Governance OAuth (OSP) Client ID (Default=iac):
Identity Governance OAuth (OSP) Client Password:

As you can see, that is a fair bit of typing, better to just set it all in the config file. However, realize you need to include the user password, and the OAuth secret in that file, so make sure it is kept secure, via file system permissions at least.

You provide the settings in key=value format, and the following is a fully filled out form I have been using that works.

	ar.host=idg.acme.com
	ar.port=8443
	ar.protocol=https
	ar.user.name=gcarman
	ar.user.password=MyPassword
	auth.host=idg.acme.com
	auth.port=8443
	auth.protocol=https
	auth.client.id=iac
	auth.client.password=testing
	retention.days=10

You will notice I have the same values for ar.host and auth.host. Their meaning differ as one is the Identity Governance box (ar.host) and the other is the OSP authentication host (auth.host). Obviously the port and protocol in both cases follow the same logic.

The biggest problem I had was with the auth.client.password, where the OSP client secret (really OAuth secret) is entered. Seems like my secret had some character that the script did not approve of. I had to change the password to something simpler. In this case it is easy enough to do, just use the configutil.sh script, run the X-Windows version (Default) and then on the Authentication Server Details tab, there is a field named iac, that you can provide the secret.

With the Identity Apps in Identity Manager, you needed to set it in OSP, on the Authentication tab of configupdate.sh, but also on the other side, in the application that consumed it. For example, the Data Collection Service driver in the IDM engine, had a GCV with the OAuth secret defined. But it was also defined in the OSP side.

Here there is only one place to set it, just in the configutil.sh GUI. This is then stored in the database, in the dbo.ISM_GLOBAL_CONFIG table, and used by the Web App when it loads and reads it from the database, as well as by OSP when you try to login.

Once you have the file prepared, you provide a reference to it on your command line with the switch:
datapurge.parameter.file=propertiesFile.txt

If you do that, it will look like this assuming you start in the idgov/bin directory, and the properties file is in the same directory:

[gcarman@idg bin]$ ./data-purge-utility.sh datapurge.parameter.file=propertiesFile.txt

Data Purge Utility
  This utility allows you to remove historical data from the operations database in
  NetIQ Identity Governance. It can purge items that are deleted or are no longer in
  use and are older than the current date and time or a specified retention date and
  time.
---------------------------------------------------------------------------------------
   Requesting purgeable Review Definitions older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Review Instances older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Technical Roles older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable SoD Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable SoD Cases older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Snapshots older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Data Sources older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Collections older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Bulk Data Update Definitions older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Business Roles older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Risk Score Statuses older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Access Requests older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Request Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Request Approval Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Certification Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Analytics Facts older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Unregistered Facts older than Aug 11, 2018 12:00:00 AM ... [ok]

       Retention Date/Time: Aug 11, 2018 12:00:00 AM
       Entity Type                    # Purgeable
       ==========================================
    1. Review Definition              0
    2. Review Instance                0
    3. Technical Role                 0
    4. SoD Policy                     0
    5. SoD Case                       0
    6. Snapshot                       65
    7. Data Source                    1
    8. Collection                     21
    9. Bulk Data Update Definition    0
   10. Business Role                  0
   11. Risk Score Status              0
   12. Access Request                 0
   13. Request Policy                 0
   14. Request Approval Policy        0
   15. Certification Policy           0
   16. Analytics Fact                 12
   17. Unregistered Fact              0

Enter number of entity type to purge (1-17) or command (Q=Quit, ?=Help): 

As you can see this system has 21 collections and 65 snapshots that can be purged as well as some Analytics Fact items.

What is interesting is that if you have enabled some extra logging you can see that this tool is making REST calls under the covers in the background. If you are looking for information on tracing in Identity Governance you can check out a Wiki page I am working on that tries to show all the known log classes, and offer examples where each might be useful or helpful.

Look for that here:
https://wiki.microfocus.com/index.php/Access_Governance_Suite

I have something similar for User Application Error levels here:
https://wiki.microfocus.com/index.php/User_App_Log_Levels

The idea is, I collected all the known classes from the places they are defined out of the box, with the shipped product, and to then leave space for anyone to update with more information. The naming is such that we have to guess at the functionality from the class name. For example in the snippet below, the class reporting a message coming from the class: com.netiq.iac.server.common.audit.AuditLogFilter

From context it is clear that iac refers to the Identity Governance main class, no doubt an earlier name when it was first being developed. But the rest is non-obvious. The log levels inherit so you can enable logging at a class near the top and it will enable logging in all levels down below. But then you get too much in the log so you want to tune it down and only enable the classes you are interested in seeing data.

However the shipping log properties files do not show all possible classes, nor are they documented anywhere I can find, so I decided I would try and track all the classes of interest I see in logs in the Wiki. I would ask that if you run into an error, a message, or even an interesting tidbit to go and try to add it into the Wiki under the proper class. (If you are uncomfortable editing, feel free to email me with the information). This way the next guy who has an issue might find it via searching. Right now, if you search on most of this stuff, there is nothing out there (Except maybe the Support forums). The more we keep the Wiki updated, the more likely it is to help others in the future.

Anyway, I entered 6 at the menu to look at the Snapshots set that can be purged, and then in the trace, snagged a REST query for Snapshots to purge, which gets a REST response:

[FINE] 2018-08-21 08:15:53 com.netiq.iac.server.common.audit.AuditLogFilter logRequestOutcome - [IG-SERVER] API server request{"sender":"10.1.158.23","user":{"userName":"Geoffrey Carman","userId":"gcarman","uniqueUserId":"dfea2b8c40394db293eeaa7ef9066fc4"},"body":"[EMPTY]","headers":{"host":"idg.acme.com:8443","content-type":"application/json","connection":"Keep-Alive","accept-encoding":"gzip,deflate","accept":"application/json","user-agent":"Apache-HttpClient/UNAVAILABLE (java 1.5)"},"params":{"retentionTime":"1533963600041"},"path":"/api/data/mgt/purgeable/SNAPSHOT","method":"GET"}

Here we the REST request, doing a HTTP GET, for the endpoint /api/data/mgt/purgeable/SNAPSHOT for the last ten days by specifying a retentionTime with a Java time date. (Millisecond count since 1970, stored in a 64 bit integer).

Then we get the response, which I will trim for brevity, since it shows 65 snapshots, that really do not help elucidate the issue much.

API server response{"body":"{\"purgeableType\":\"SNAPSHOT\",\"purgeEntities\":[{\"id\":23,\"type\":\"PUBLICATION\",\"time\":1532688778688},{\"id\":46,\"type\":\"PUBLICATION\",\"time\":1532946623994}]}","status":200}

Formatted, the JSON data looks like:

{
   "body":"{\"purgeableType\":\"SNAPSHOT\",\"purgeEntities\":
   [
   {\"id\":23,\"type\":\"PUBLICATION\",\"time\":1532688778688
   },{\"id\":46,\"type\":\"PUBLICATION\",\"time\":1532946623994
}
]
}","status":200
}

This query returns something similar to this in the console mode interface which is shown like this:

       Retention Date/Time: Aug 11, 2018 12:00:00 AM
       Snapshot ID     Type                     End Time
       =====================================================
    1. 23               PUBLICATION              Jul 24, 2018 2:08:16 PM
    2. 46               PUBLICATION              Jul 24, 2018 3:04:38 PM

Enter numbers of item(s) to purge, or command (Q=Quit, ?=Help): 

For fun, I tried to see what ?Help returned, which is below:

Help:
Enter numbers of one or more items to purge, or one of the following commands:

   ?,h,help        = Show this help.
   q,quit,exit     = Exit purge utility.
   s,show          = Show parameters.
   c,change        = Change parameters.
   r,refresh       = Refresh list of Collections.
   date            = Change retention date/time. List will be refreshed after changing retention date/time.
   d,days          = Change retention days. List will be refreshed after changing retention days.
            = Return to entity types menu.

   NOTE: Multiple items may be specified using number ranges and/or comma-separated values. For example:

      1,3,7-9 purges items 1, 3, 7, 8, and 9

   The '*' may be used to purge ALL Collections older than Aug 11, 2018 12:00:00 AM.

I think the key takeaways from this help menu are really two fold. First, that asterisk (*) will do everything older than the 10 days ago, that I specified in my properties file as retention.days=10 or entered on the console prompt. Second that you can select the specific snapshots to purge, either one at a time, with a simple number. Like entering 1 or 7.

You can also specify a range, like 9-14 and you can specify 1, 3, 7 to select the three snapshots. Even better, you can combine all these options together.

This gives you sufficient flexibility to do much of what you need. (Side note: This is similar to the script the IDM installer used in console (not GUI) mode, when selecting drivers to install).

So in my case, I entered an asterisk, so I could purge all the old stuff. Which then prompts with:

Are you sure you want to purge all Collections older than Aug 11, 2018 12:00:00 AM? (y=yes,n=no,default=n): y

   Purging 65 Snapshots ... [ok]
   Requesting purgeable Snapshots older than Aug 11, 2018 12:00:00 AM ... [ok]

It then finished, did the query for Snapshots with a date specified, and it found:

There are no Collections older than Aug 11, 2018 12:00:00 AM that are currently purgeable.
Enter command (Q=Quit, ?=Help):

Excellent, cleared up some space right? Well amusingly, I was watching in the MS-SQL management tool, and the DB was about 29GB is size when I started and it went UP (yes UP!) to 35GB after the purge. I suspect I need to get my DBA to compact the database now, since the change log on the database has to track the deleted areas until we clean up. After my next clean up attempt it went up to 44GB. I seem to be moving backwards quite quickly at this point.

One thing that confused me was, once you are in a sub menu like Collections or Snapshots, how do you move back one level. Q quits. Esc does nothing. Turns out it is simply a carriage return at an empty prompt. I find that a smidgen non-intuitive, but whatever works for them.

Then we are back, checking to see what is pending and needs to be cleared.


   Requesting purgeable Review Definitions older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Review Instances older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Technical Roles older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable SoD Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable SoD Cases older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Snapshots older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Data Sources older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Collections older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Bulk Data Update Definitions older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Business Roles older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Risk Score Statuses older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Access Requests older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Request Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Request Approval Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Certification Policies older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Analytics Facts older than Aug 11, 2018 12:00:00 AM ... [ok]
   Requesting purgeable Unregistered Facts older than Aug 11, 2018 12:00:00 AM ... [ok]

       Retention Date/Time: Aug 11, 2018 12:00:00 AM
       Entity Type                    # Purgeable
       ==========================================
    1. Review Definition              0
    2. Review Instance                0
    3. Technical Role                 0
    4. SoD Policy                     0
    5. SoD Case                       0
    6. Snapshot                       0
    7. Data Source                    1
    8. Collection                     21
    9. Bulk Data Update Definition    0
   10. Business Role                  0
   11. Risk Score Status              0
   12. Access Request                 0
   13. Request Policy                 0
   14. Request Approval Policy        0
   15. Certification Policy           0
   16. Analytics Fact                 12
   17. Unregistered Fact              0

Now I actually ran into a bug, that I am told is due to be fixed in SP2 for Identity Governance 3, and included in Identity Governance 3.5 that is specific to this task with Microsoft SQL Server that means I cannot quite purge some stuff.

Are you sure you want to purge all Collections older than Aug 11, 2018 12:00:00 AM? (y=yes,n=no,default=n): y

   Purging 21 Collections ... [FAILED]

Unexpected response code received: 489
Error code from server: InternalExceptionOccured
Error message from server: Encountered unexpected error.

This was not so helpful, since it just returns an error, 489 that is not documented, and I had to go get support to tell me that there is a fix required.

I went and looked in the days catalina log (note: catalina.out is not useful, instead it is catalina.2018-08-21.log for the specific day):

[FINE] 2018-08-21 08:12:17 com.netiq.iac.persistence.service.ReviewService autoApproveReviewItemsInternal - [IG-SERVER] Review item approval monitor thread
[SEVERE] 2018-08-21 08:12:23 com.netiq.iac.common.logging.IACLoggingUtils logExceptionError - [IG-SERVER] Encountered unexpected error: could not execute statement
com.netiq.common.i18n.LocalizedException: Encountered unexpected error: could not execute statement
        at com.netiq.iac.common.IacException.(IacException.java:105)
        at com.netiq.iac.persistence.spi.exception.IacPersistenceException.(IacPersistenceException.java:118)
        at com.netiq.iac.persistence.dao.DataTransactor.execute(DataTransactor.java:133)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion.deleteDataProductions(SnapshotDeletion.java:1189)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion$8.eval(SnapshotDeletion.java:532)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion$8.eval(SnapshotDeletion.java:1)
        at com.netiq.iac.persistence.dao.ops.ResultTransactor.execute(ResultTransactor.java:113)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion.deleteSnapshot(SnapshotDeletion.java:541)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion.access$6(SnapshotDeletion.java:494)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion$5.eval(SnapshotDeletion.java:450)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion$5.eval(SnapshotDeletion.java:1)
        at com.netiq.iac.persistence.dao.ops.ResultTransactor.execute(ResultTransactor.java:113)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion.deleteSnapshot(SnapshotDeletion.java:457)
        at com.netiq.iac.persistence.service.SnapshotPurgeService$3.doIn(SnapshotPurgeService.java:107)
        at com.netiq.iac.persistence.dao.DataTransactor.execute(DataTransactor.java:121)
        at com.netiq.iac.persistence.service.SnapshotPurgeService.purgeEntities(SnapshotPurgeService.java:112)
        at com.netiq.iac.server.rest.DataManagementService.purgeEntities(DataManagementService.java:378)
        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 com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
        at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)
        at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
        at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
        at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
        at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
        at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
        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 com.netiq.iac.common.j2ee.NoCacheFilter.doFilter(NoCacheFilter.java:65)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at com.netiq.common.i18n.impl.BestLocaleChooserFilter.doFilter(BestLocaleChooserFilter.java:234)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at com.netiq.iac.server.common.audit.AuditLogFilter.doFilter(AuditLogFilter.java:134)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at com.netiq.iac.server.j2ee.RestApiAuthFilter.doFilter(RestApiAuthFilter.java:145)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at com.netiq.iac.server.j2ee.AuthFilter.doFilter(AuthFilter.java:239)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
        at com.netiq.iac.server.j2ee.CORSFilter.doFilter(CORSFilter.java:80)
        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:199)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
        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: org.hibernate.exception.SQLGrammarException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
        at org.hibernate.engine.query.spi.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:211)
        at org.hibernate.internal.SessionImpl.executeNativeUpdate(SessionImpl.java:1346)
        at org.hibernate.internal.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:389)
        at com.netiq.iac.persistence.dao.ops.SnapshotDeletion$18.doIn(SnapshotDeletion.java:1175)
        at com.netiq.iac.persistence.dao.DataTransactor.execute(DataTransactor.java:121)
        ... 74 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1547)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:548)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:479)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7344)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2713)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:224)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:204)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:426)
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
        at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
        ... 79 more

Alas, you can look at the Caused by: segment to see a usually more helpful error, which says:

	Incorrect syntax near ')'.

That is not much more helpful than the 489 error really. Be nice if it showed the full SQL statement that it did not like.

I did get a similar error later, after I enabled some more logging and saw this “Caused by”:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK_COL_ACCT_OWNER_PERM". The conflict occurred in database "igops", table "dbo.COLLECTED_ACCOUNT_OWNER", column 'ACCOUNT'.

This one at least shows much more useful information. The thing is, I think this is a different error, since the log levels I enabled (com.netiq.daas) is not involved in the error reported. I expect I have a couple of issues here, but it was interesting to see the differences.

I will try and update once I find out more about these errors, and I guess we have to wait for SP2 or the 3.5 release for the proper fix for now. Guess my DB will just get to get bigger until that is resolved.

That is about all I have to say about the purge tool for now. I have some more interesting snippets, that I will write up when I get some more information to include.

1 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 51 vote, average: 5.00 out of 5 (1 votes, average: 5.00 out of 5)
You need to be a registered member to rate this post.
Loading...

Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment.  It just worked for at least one person, and perhaps it will be useful for you too.  Be sure to test in a non-production environment.

Leave a Reply

No Comments
geoffc
By: geoffc
Sep 12, 2018
3:14 pm
Reads:
268
Score:
5
Active Directory Authentication Automation Cloud Computing Cloud Security Configuration Customizing Data Breach DirXML Drivers End User Management Identity Manager Importing-Exporting / ICE/ LDIF Intelligent Workload Management IT Security Knowledge Depot LDAP Monitoring Open Enterprise Server Passwords Reporting Secure Access Supported Troubleshooting Workflow