ElasticSearch DB purge

[Raf1ty]

Request Type

Information

Work Environment

Question	Answer
OS version (server)	RedHat
TheHive version	2.10.2
ES index	the_hive_8

Problem Description

Hello,

We need to purge our TheHive ES database because it take too many space on our server due to the high number of file observables.
Do you have an easy way to remove all the file observables data related to cases opened before x days and which has been closed as false positive?

Thank you in advance for your help!
Raphael

[nadouani]

Hi @Raf1ty, I don't really think there is a way to do that except by cooking a script that plays with Thehive APIs and probably Elasticsearch APIs.

[fl0wc0ntr0l]

Hi @Raf1ty,

If you can install the delete-by-query plugin, you can use this API call to delete all cases older than X days. I had to come up with this due to the case similarity bug described in #280.

curl -XDELETE 'http://localhost:9200/the_hive_10/case/_query?pretty=true' -H 'Content-Type: application/json' -d '
{
"query": {
    "filtered": {
      "query": {
        "query_string": {
          "query": "*"
        }
      },
      "filter": {
        "range": {
          "createdAt": {
            "lte": "now-Xd"
          }
        }
      }
    }
  }
}'

[nadouani]

Well, this is a bit dangerous, because there are a lot of relations for a case. This could end up with inconsistent data, if for example, you delete the case but not the task, observables and audit log entries related to it etc...

[fl0wc0ntr0l]

Yes, it's not the safest thing, but neither is having a bug where I can't preview any alerts due to how many similar cases there are :)

[nadouani]

I agree with that. Issue #280 is blocking in your case.

[Raf1ty]

Thank you Nadouani, Miles,
In my case, I need to remove the observables related to these old cases, because they are taking most of our ES size.

I will try to implement this functionnality into a script based on ES searches and theHive API.

Thanks,
Best Regards,

[fl0wc0ntr0l]

This may help - I used a query like this to remove case tasks without a parent; you might be able to do something slightly modified to get observables too, but I've never tried it. The audit log poses a trickier task though; I never had issues stemming from case deletion, but like @nadouani said, this could be dangerous. Always double-check destructive actions :)

curl -XDELETE 'http://localhost:9200/the_hive_9/case_task/_query' -H 'Content-Type: application/json' -d '
{
	"filter": {
		"not": {
			"filter": {
				"has_parent": {
					"query": {
						"match_all": {
							
						}
					},
					"parent_type": "case"
				}
			}
		}
	}
}'

[nadouani]

I'll close this issue, since TheHive doesn't provide this type of purge feature. We can discuss it on https://gitter.im/TheHive-Project/TheHive if needed.

[Tux-Panik]

@Raf1ty Did you succeed to write a script to make this job?
If yes, may you please share it?

Thanks,
Regards,
Julien