SQL errors with ISE Open API bulk create

JaseNL — Mon, 22 Jul 2024 18:36:09 GMT

I have a python API client making changes to ISE endpoints (create, update and delete) using the Open API endpoint bulk API. I am aware that the ERS bulk API has a restriction on bulk request sizes - 500 (full endpoint data) or 5000 (endpoint ID only). I couldn't find any information about a similar limit on Open API so for the moment I'm assuming the same.

The client is working correctly in that it can successfully send bulk requests to the Open API (create, update and delete), and those requests are being correctly processed by the ISE server.

The only issue is that I have started seeing errors like the example below, when doing a bulk create, and these can occur whether the request contains 1000 endpoints or 200 (well below the ERS limit if indeed that even applies to Open API):

org.h2.jdbc.JdbcSQLException: Order by expression "98302081" must be in the result list in this case; SQL statement:

SELECT identityGroupID, identityGroupName, parentID FROM IdentityGroupDetails ORDER BY ? [90068-196]

I have the idea this might have something to do with performance, because it seems random, rather than being caused by any particular uploaded endpoint data structure. I can say this because I am testing the API client with a static endpoint dataset (a standard profiler_endpoints.csv export from the ISE Server) so endpoints are processed in the same order every time. Yet these errors can occur at any point in the bulk operation.

My client retrieves all the group names and IDs from the ISE server when it starts, so the endpoint names can be translated to valid IDs before endpoints are created or updated. So I also find it unlikely that the error message is a result of a bad group ID.

Cisco ISE version is 3.2.0.401

Server version is 3.2.0.542 patch 3,5,6

Any help or guidance understanding this would be greatly appreciated.

Thanks

Re: SQL errors with ISE Open API bulk create

Arne Bier — Tue, 23 Jul 2024 20:32:01 GMT

Is the API Gateway node under load when you make these queries? If it's a VM have you checked the CPU load perhaps? I have not tried this myself, but I recall crashing a PSN by deleting too many endpoints in the Context Visibility. But that was some years ago.

Might need a TAC case to be sure

Re: SQL errors with ISE Open API bulk create

JaseNL — Tue, 23 Jul 2024 21:22:39 GMT

Very little load I would say. This is a lab system and I have pretty much exclusive access for developing my API client. Since you mention it, I can delete 10,000 endpoints in one bulk request without any problem. Unless the deletes are corrupting the database ready for the next bulk create request (sounds nice but I'm not sure - the server was restarted very recently and I think I had problems straight away).

Looks like TAC's the way to go. Thanks for the suggestion anyway.

topic Re: SQL errors with ISE Open API bulk create in Network Access Control

SQL errors with ISE Open API bulk create

Re: SQL errors with ISE Open API bulk create

Re: SQL errors with ISE Open API bulk create