[Bug] Slow getting case observables via API get_case_observables

[jpferrero]

Request Type

Bug

Work Environment

Question	Answer
OS version (server)	CentOS 7
Virtualized Env.	True
Dedicated RAM	3x16 GB
vCPU	8 each node
TheHive version	4.11
Package Type	RPM
Database	Cassandra
Index type	Elasticsearch
Attachments storage	HDFS

Problem Description

Trying to get observables from a case_id via TheHive4py, it takes more than 3 minutes to run the query.

This is the TheHive4py call:

api = TheHiveApi(THEHIVE_URL, THEHIVE_API_KEY, cert=False)
api.get_case_observables("~492589064", query={}, sort=['-startDate', '+ioc'], range='all')

Time:

real 3m22.734s
user 0m0.141s
sys 0m0.023s

There are a total of 367848 of observables and 24872 cases in our database.

Thanks.

[jpferrero]

As a workaround it's possible to make a petition as the frontend does, and it spends less than a second to get the case observables:

curl -XPOST -H "Authorization: Bearer XXXXX" "https:///api/v1/query?name=observables" -H "Content-Type: application/json" -d '{"query":[{"_name":"getCase","idOrName":"~985923760"},{"_name":"observables"},{"_name":"sort","_fields":[{"startDate":"desc"}]},{"_name":"page","from":0,"to":100,"extraData":["seen","permissions","shareCount"]}]}'

[torsolaso]

I think this is related to #2149

There are several querys not migrated from thv3 to thv4

[mamoedo]

This also may be linked to #2116

[To-om]

This issue has been fixed by #2225

[zohkoo]

This issue has been fixed by #2225

We updated our instance to TheHive 4.1.13 and the issue is still there. I've made a test this afternoon and it tooks 20 minutes to retrieve the observables of a specific case via get_case_observables. I'm not sure it has really been fixed by #2225

I can confirm that the workaround suggested by @jpferrero is also working for us.

[ch0wm3in]

We have the same problem on 4.1.14