[Bug] Computed query for ES contains deprecated function and therefore is broken as of ES 7.0

[jeffrey-e]

Request Type

Bug

Work Environment

Question	Answer
OS version (server)	RedHat
OS version (client)	7.9
TheHive version / git hash	3.5.0-1
Package Type	RPM

Problem Description

ES apparently has changed a function that is required for the computed stats generation:

• Issuelink: https://discuss.elastic.co/t/script-field-with-datefield-api-fails-after-7-0-1-update/180256
• Doc link: https://www.elastic.co/guide/en/elasticsearch/painless/current/painless-walkthrough.html#modules-scripting-painless-dates

Error:
2021-03-25 09:09:20,909 [ERROR] from org.elastic4play.database.DBConfiguration in application-akka.actor.default-dispatcher-211750 - ElasticSearch request failure: POST:/the_hive_16/_search?
StringEntity({"query":{"bool":{"must":[{"term":{"relations":{"value":"case"}}},{"match_all":{}}]}},"size":0,"aggs":{"agg_1":{"filter":{"match_all":{}},"aggs":{"agg_1":{"avg":{"script":{"lang":"painless","source":"(doc['endDate'].date.getMillis() - doc['startDate'].date.getMillis()) / 1000"}}}}}}},Some(application/json))
 => ElasticError(search_phase_execution_exception,all shards failed,None,None,None,List(ElasticError(script_exception,runtime error,None,None,None,null,None,None,None,List())),None,Some(query),Some(true),List(FailedShard(0,Some(the_hive_16),Some(g9TybSZSS_OMbjqXhq_dRA),Some(ElasticError(script_exception,runtime error,None,None,None,null,Some(CausedBy(illegal_argument_exception,Illegal list shortcut value [date].,Map())),None,None,List())))))
2021-03-25 09:09:20,909 [INFO] from org.elastic4play.ErrorHandler in application-akka.actor.default-dispatcher-211750 - POST /api/case/_stats returned 400
org.elastic4play.SearchError: Invalid search query
        at org.elastic4play.database.DBFind$$anonfun$apply$4.applyOrElse(DBFind.scala:126)
        at org.elastic4play.database.DBFind$$anonfun$apply$4.applyOrElse(DBFind.scala:124)
        at scala.concurrent.Future.$anonfun$recoverWith$1(Future.scala:417)
        at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:41)
        at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:64)
        at akka.dispatch.BatchingExecutor$AbstractBatch.processBatch(BatchingExecutor.scala:56)
        at akka.dispatch.BatchingExecutor$BlockableBatch.$anonfun$run$1(BatchingExecutor.scala:93)
        at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:23)
        at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:85)
        at akka.dispatch.BatchingExecutor$BlockableBatch.run(BatchingExecutor.scala:93)
        at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:48)
        at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:48)
        at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
        at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
        at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
        at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:175)

Steps to Reproduce

1. Create a counter dashboard widget using the computed.handlingDurationIn* field

Possible Solutions

Adjust the query to the new syntax as provided in the urls

[nadouani]

Isn't solved with 3.5.1?

[jeffrey-e]

Euhm I checked the changelog. Something similar was fixed for an update, but I do not see a relation to this issue.
Link: #1799

[nadouani]

Oh sorry you are using 7.9 so no need for 3.5.1

[jeffrey-e]

Just tested on a docker container with 3.5.1. The error seems to be gone indeed, but the result is always 0?
Tested in on my TH4 lab and there it worked fine though :)