[Feature Request] Add API to link alert and case after a broken migration from TH3

[To-om]

Request Type

Feature Request

Feature Description

Related to #2232
The aim of this issue is to add an API for fixing broken links between alerts and cases after a migration from TheHive 3.
If possible, it is recommended to rerun migration from scratch using migration tool 4.1.13 or higher.

The script which reads alert from Elasticsearch 7 and uses the API to link with case could look like:

#!/bin/bash

# The URL of Elasticsearch
ES_URL=http://x.x.x.x:9200
# The name of TheHive 3 index
ES_INDEX=the_hive_17
# The URL of TheHive 4
TH_URL=http://x.x.x.x:9000
# The login and password of super admin user
TH_AUTH=admin:secret
# The name of the organisation used by the migration tool
TH_ORGANISATION=xxxx

linkCaseAndAlert() {
    CASE_NUMBER=$1
    ALERT=$2
    # The format of ALERT is "<type>;<source>;<sourceRef>"

    echo -n "Link case #${CASE_NUMBER} with alert ${ALERT} ... "
    RESULT=$(curl -s -u "${TH_AUTH}" "${TH_URL}/api/v1/alert/fixCaseLink" -H 'Content-type: application/json' -d '
    {
        "alertName": "'"${ALERT}"'",
        "caseNumber": "'"${CASE_NUMBER}"'",
        "organisation": "'"${TH_ORGANISATION}"'"
    }')
    [ -z "${RESULT}" ] && echo OK || echo ${RESULT}
}



SEARCH_RESULT=$(curl -s \
    -H 'Content-type: application/json' "${ES_URL}/${ES_INDEX}/_search?scroll=1m" \
    -d '
        {
            "query": {
                "bool": {
                    "must": [
                        { "term": { "relations": "alert" } },
                        { "exists": { "field": "case" } }
                    ]
                }
            },
            "sort": [
                "_doc"
            ],
            "fields": [ "type", "source", "sourceRef", "case" ],
            "_source": false
        }')

while :
do
    DOC_COUNT=$(jq '.hits.hits | length' <<< ${SEARCH_RESULT})
    [[ "$DOC_COUNT" -eq 0 ]] && break
    SCROLL_ID=$(jq ._scroll_id  <<< ${SEARCH_RESULT})
    jq -r '.hits.hits | map(.fields.case[0]+" "+.fields.type[0]+";"+.fields.source[0]+";"+.fields.sourceRef[0]) | join("\n")' <<< ${SEARCH_RESULT} \
    | while read CASE_ID ALERT
    do
        CASE_NUMBER=$(curl -s "${ES_URL}/${ES_INDEX}/_doc/${CASE_ID}?_source=caseId" | jq ._source.caseId)
        [[ "${CASE_NUMBER}" != "null" ]] && linkCaseAndAlert "${CASE_NUMBER}" "${ALERT}"
    done
    SEARCH_RESULT=$(curl -s -H 'Content-type: application/json' "${ES_URL}/_search/scroll" -d '
        {
            "scroll": "1m",
            "scroll_id": '"${SCROLL_ID}"'
        }')
done

[nicpenning]

Thanks @To-om for this fix. We pushed to this version and began using the script right away. For the most part, it seems that the alerts won't get linked.

Is there any way to troubleshoot this? Or is it possible that our SourceRef has some characters (-.:) causing this functionality to break? Below are some sample logs from running the script. The alerts should most definitely exist but not sure why they are not found.

Link case #11434 with alert ReportPhish;email;ReportPhish - 2021-03-12T12:38:58.6042824-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #11299 with alert ReportPhish;email;ReportPhish - 2021-03-09T13:36:52.8772114-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #5788 with alert ReportPhish;email;ReportPhish - 2020-09-10T12:49:13.2643440-05:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #11302 with alert ReportPhish;email;ReportPhish - 2021-03-09T14:27:49.6370134-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #11427 with alert ReportSpam;email;ReportSpam - 2021-03-12T10:24:23.2211385-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #11490 with alert ReportPhish;email;ReportPhish - 2021-03-16T08:12:45.7003815-05:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #3339 with alert ReportPhish;email;ReportPhish - 2021-03-12T09:39:14.1374711-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #7280 with alert ReportPhish;email;ReportPhish - 2020-11-10T11:01:42.1034017-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #11419 with alert ReportPhish;email;ReportPhish - 2021-03-12T08:14:07.8034124-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #7528 with alert ReportPhish;email;ReportPhish - 2020-11-18T10:08:58.2585903-06:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #7016 with alert ReportPhish;email;ReportPhish - 2020-10-30T11:36:11.7959968-05:00 ... {"type":"NotFoundError","message":"Alert not found"}
Link case #6431 with alert ReportPhish;email;ReportPhish - 2020-10-15T10:38:53.0145947-05:00 ... {"type":"NotFoundError","message":"Alert not found"}

[To-om]

The format of alert is <type>;<source>;<sourceRef>. There is a problem in your alert Id (ReportPhish;email;ReportPhish), ReportPhish doesn't look like an alert sourceRef. Can you check the output of the command

curl -s \
    -H 'Content-type: application/json' "${ES_URL}/${ES_INDEX}/_search" \
    -d '
        {
            "query": {
                "bool": {
                    "must": [
                        { "term": { "relations": "alert" } },
                        { "exists": { "field": "case" } }
                    ]
                }
            },
            "sort": [
                "_doc"
            ],
            "fields": [ "type", "source", "sourceRef", "case" ],
            "_source": false
        }

[nicpenning]

Here is an example of 2 docs:

{"_index":"the_hive_17","_type":"_doc","_id":"bdb87215c5516fd764af565ec16d3b30","_score":null,"_routing":"bdb87215c5516fd764af565ec16d3b30","fields":{"source":["email"],"type":["ReportPhish"],"sourceRef":["ReportPhish - 2020-06-09T09:32:04.8084567-05:00"],"case":["AXKVXS6OsIzjjffngHdE"]},"sort":[44]},{"_index":"the_hive_17","_type":"_doc","_id":"9ec366684e8e8e7918eaced4d46833a3","_score":null,"_routing":"9ec366684e8e8e7918eaced4d46833a3","fields":{"source":["email"],"type":["ReportSpam"],"sourceRef":["ReportSpam - 2020-05-07T14:36:04.1161368-05:00"],"case":["AXHwpSYjztNCwCW1uCHE"]},"sort":[75]}

[To-om]

The sourceRefs is not ReportPhish but ReportPhish - 2020-06-09T09:32:04.8084567-05:00, which seems correct.

Are you sure you set the correct organisation in script? Do you see that alerts in TheHive interface ?

[nicpenning]

Yes since we only had 1 organization. We don't see the alerts in TheHive 4 interface when searching. We used the sourceRef to search on.

[To-om]

If you don't see the alerts in TheHive, it means that they haven't been migrated from TheHive 3. In that case, the links with them cannot be fixed.
Did you migrate all alerts (or did you set a filter parameter in migrate tool)?

[nicpenning]

We are confident that the alerts were there after migration but over time went missing. Even alerts that were created after the migration seemed to have disappeared. Ever since 4.1.10 the alerts are staying and no longer going away.

This is the issue we started with: #2188 and thought there was a chance this fix would bring them back.

Maybe it's worth a shot to re-migrate?

[nicpenning]

This is the command we ran for the migrate back on:

sudo /opt/thehive/bin/migrate --drop-database --max-audit-age 1s --case-from-number 0 --output /etc/thehive/application.conf --main-organisation SOC --es-uri http://127.0.0.1:9200

We went from 3.5.1-1 to 4.1.2 (possibly)

This should include alerts correct?