DATABASE_ERROR: 'Fail to execute engine pagination' when filtering an exact search result

[brokkolax]

Description

The python client crashes when trying to filter the result of opencti_api_client.stix_cyber_observable.<read|list> or opencti_api_client.indicator.<read|list> for e.g. non revoked observables.

Environment

1. SUSE Linux Enterprise Server 15 SP5
2. OpenCTI version: 6.0.8
3. Other environment details: Python 3.11, running the opencti platform via docker-compose

Reproducible Steps

Working API-Call, searching for exact match

malware = opencti_api_client.stix_cyber_observable.list(
filters={
    "mode": "and",
    "filters": [{"key": "name", "mode":"or", "operator":"eq","values": ["malware.exe"]}],
    "filterGroups": []})
print(malware)

Non-Working API-Call, searching for non revoked exact match

malware = opencti_api_client.stix_cyber_observable.list(
    filters={
        "mode": "and",
        "filters": [{"key": "name", "mode":"or", "operator":"eq","values": ["malware.exe"]}],
        "filterGroups": [
      {
        "mode": "and",
        "filters": [
          {
            "key": ["revoked"],
            "mode": "and",
            "operator": "eq",
            "values": ["False"]
            }
        ],
        "filterGroups": []
      }
    ]
    }
)
print(malware)

Expected Output

Displaying a result depending on its revocation status.

Actual Output

INFO:api:Health check (platform version)...
INFO:api:Listing StixCyberObservables with filters
Traceback (most recent call last):
  File "/opt/docker/opencti/docker/api-test/api.py", line 13, in <module>
    malwares2 = opencti_api_client.stix_cyber_observable.list(
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/pycti/entities/opencti_stix_cyber_observable.py", line 664, in list
    result = self.opencti.query(
             ^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/site-packages/pycti/api/opencti_api_client.py", line 351, in query
    raise ValueError(
ValueError: {'name': 'DATABASE_ERROR', 'message': 'Fail to execute engine pagination'}

Additional information

The reason for filtering at this point is, that we want our SIEM (Graylog) to lookup specific values via the python client and only enrich those log messages that have a non revoked hit in Opencti, which is also flagged for Detection.
In our opinion there's no use for revoked Indicators/ non detection flagged Indicators for this use-case.

Thank you!

[Kedae]

Hi @brokkolax,

For this use-case I think your issue is that you set "False" instead of "false". Can you try and come back to us ?

[brokkolax]

Hey @Kedae, sorry for my late answer. I'm currently in the process of setting up a new server running opencti and will get back to you after testing your suggestion.

[nino-filigran]

Any feedback regarding @Kedae suggestion?

[brokkolax]

Hey @nino-filigran. Since my last comment I tried to test the suggestion multiple times, but I wasn't able to evaluate it. No matter which example I use, be it the working example from above or the example from the documentation - I only get the following error:

INFO:root:Listing Threat-Actors with filters null.
ERROR:root:Unknown type "ThreatActorsFiltering". Did you mean "ThreatActorsOrdering", "ThreatActorConnection", "ThreatActorEdge", "ThreatActorGroup", or "ThreatActorGroupEdge"?
Traceback (most recent call last):
  File "api2.py", line 6, in <module>
    opencti_api_client = OpenCTIApiClient("http://serverfqdn:8080", "token")
  File "/usr/lib/python3.6/site-packages/pycti/api/opencti_api_client.py", line 145, in __init__
    "OpenCTI API is not reachable. Waiting for OpenCTI API to start or check your configuration..."
ValueError: OpenCTI API is not reachable. Waiting for OpenCTI API to start or check your configuration...

Here it seems to be about the type "ThreatActorsFiltering" - maybe that changed in a recent update to opencti? The Website and its connectors are working fine, so the API should be reachable.

Also, please excuse the radio silence from my side, we're still very interested in getting this lookup working - there was just too much going on the past weeks to debug this error for multiple hours. Nevertheless I will focus on this topic now anytime you post something!

[nino-filigran]

No worries @brokkolax I understand that sometimes we are getting caught in something. Just to make sure I have full information: are you still using version 6.0.8 with python 3.1.1?

[brokkolax]

Oh boy, seems like several questionable decisions on my part have led to this issue:

• The new machine was running python3.6.15 which led to the Unknown type "ThreatActorsFiltering" error. After installing python3.11 that error has disappeared.
• Just like @Kedae suggested, the Database error occured because I passed "True" instead of "true" to the filter.
• I suspect all of the confusion on my end was caused by the following misconception: I initially tried to filter out revoked observables instead of revoked indicators. Obviously there's a big difference between these two data types, but for whatever reason, I didn't think of it at the time. So by filtering for a field ("revoked") that isn't even set for an observable, I always got an empty result. Assuming that this can't be a correct behaviour it seems that I tried anything that lead to a different output, hence the "True".

For future reference, in case anyone else has this kind of brain lag, here's a summary of my lessons learnt:

1. Don't use too old/ unsupported python versions (duh).
2. When filtering for a boolean value: Use all lowercase letters for true/false
3. Be sure to differenciate between observables and indicators: An indicator (i) is information that by itself could describe anything. An observable (O) is a collection of one or more indicators (i ∈ O, O = {i₁, ..., iₙ}) that, in the entirety of its indicators, forms a data set that can identify information (e.g. logs in your SIEM) worth observing. Also if you're trying to integrate the response of your script/ function call/ etc. somewhere else (e.g. your SIEM), consider the possibility that your query might return multiple entries and plan your script accordingly, e.g. by grouping specific data in an array or return a boolean value indicating if there's any entries matching your search at all.

So @nino-filigran and @Kedae thanks again for your help! Seems like there was no bug, but rather a problem on layer 8...

[nino-filigran]

@brokkolax No worries, what matters is that it works and you figured out the problem!