Does feed delta merge remove correlations?

[github-germ]

MISP 2.4.137

Not sure if this is my misunderstanding or possibly a bug... Looking forward to an education. Thank you.

I have detected correlation orphans in our correlations db table, i.e. for a given correlations row either the attribute_id or the 1_attribute_id no longer exists in the attributes table. In our instance that's about 9k orphans out of a total 13.6M correlations.

I then performed this mysql query and found that in all cases the MISP event was a MISP feed which had delta merge enabled.

select id,info,date,from_unixtime(timestamp) from events
where id in (select distinct(c.event_id)
from correlations as c left outer join attributes as a 
on c.attribute_id=a.id where a.id is NULL);

I'm not well versed with PHP, so bear with me please as I may be missing how this all fits together; however, It seems to my naive eye like correlations might not be deleted during the feed delta merge process.

When I look at the saveFreetextFeedData function in Model/Feed.php where delta_merge is processed, it's not clear if this line triggers any calls to remove the deleted attributes from correlations.

$this->Event->Attribute->saveMany($attributesToDelete); // We need to trigger callback methods

What I'm not seeing, perhaps due to my lacking knowledge, is similar pruning of the correlations that I see in Model/Attribute.php with the afterSave function call to the __beforeSaveCorrelation function which calls Correlation->deleteAll.

[github-germ]

As part of my continued PHP learning curve :-), I am amending my hypothesis above, and certainly would like feedback as we do need to understand root cause to these orphans and how to prevent.

Revised Hypothesis: Perhaps the call to the cakePHP saveMany invokes the afterSave callback in Model/Attribute.php which calls __beforeSaveCorrelation which does appear to remove the affected correlations.

If that is true, then I'd appreciate help in devising another reason why we have correlation orphans across many delta-merge feeds.

Issues with these orphans include:

• WebUI Event View correctly does not display those deleted attributes in the attribute list
  • while the Related Events lists many attributes in other events
  • producing quite incorrect and confusing results.
• Same in query results to our MISP consumers.

Please advise. Thanks!!

[mokaddem]

Hello,
Your revised hypothesis is correct. As per the documentation, saveMany triggers callbacks invoking the afterSave function on all attributes being soft deleted.

One hypothesis that I have: The line you were referring to d022b0f actually fixed the issue you are experiencing (it's fixed in 2.4.127 and onward). Would you mind checking if the dates of the orphaned correlations you noticed and the 2.4.127 fix align?

One way to do it (even though, unreliable..) would be to check what is the latest orphaned correlation ID compared to the current max(ID) of all correlations.

Thanks in advance!

[mokaddem]

I pushed a way to clean orphaned correlations on an instance (b86af24).
However, I would appreciate that you refrain from using the tool until we find out with a relative degree of certitude why or how you ended up having these entries..
Thanks!

[github-germ]

Thanks a lot for your reply!

As you will see below, I believe these orphans have occurred after 2.4.127; hence, root cause is a mystery. We can recorrelate to clean house, but we do need to prevent these from recurring as they present many false impressions to our data consumers.

I'm eager to see what you suggest next. Thx again.

Here's some "evidence"...

Here''s the upgrades on this MISP instance:

• 2020-Sep .125 -> .131
• 2021-Jan .131 -> .133
• 2021-Mar .133 -> .137 (which is where we are now; hope to goto .141 soon)

Please refer to my 2021-Jan issue #6931. At that point we were already on 2.4.133, and I recorrelated our MISP instance. So, I believe I can deduce that these orphans occurred after 2.4.127, i.e. on 2.4.133 or 2.4.137.

I cannot pinpoint the latest orhpan timestamp as at one point last year our db was totally over correlated so we recorrelated, and you can see the huge disparity between max ans count, e.g.

mysql> select max(id) from correlations;
+-----------+
| max(id)   |
+-----------+
| 174685973 |
+-----------+
1 row in set (0.00 sec)

mysql>  select c.id from correlations as c left outer join attributes as a on c.attribute_id=a.id where a.id is NULL order by c.id desc limit 1;
+----------+
| id       |
+----------+
| 94599794 |
+----------+
1 row in set (7.61 sec)

mysql>  select count(*) from correlations;
+----------+
| count(*) |
+----------+
| 14816444 |
+----------+
1 row in set (0.13 sec)

mysql>

[github-germ]

We are now on 2.4.143, and still are getting correlations orphans.

• WebUI shows `check orphaned attributes' displays:
  • Orphaned attributes…2154 (Removal recommended)
• Here's my check via MySQL indicating attribute values marked correlated which are not!!

mysql> select a.type,count(*) as cnt
    -> from correlations as c
    -> join attributes as a
    ->  on c.attribute_id=a.id and upper(c.value) != upper(a.value1)
    ->     and upper(c.value) != upper(a.value2)
    -> group by a.type;
+------------------------+-------+
| type                   | cnt   |
+------------------------+-------+
| url                    |  6122 |
| ip-dst                 | 13559 |
| domain                 |  4742 |
| hostname               |   140 |
| md5                    |   220 |
| link                   |    36 |
| email-src              |    59 |
| email-subject          |    22 |
| email-src-display-name |    50 |
| email-header           |    42 |
| email-reply-to         |    24 |
| sha256                 |   191 |
| ip-src                 |    27 |
| threat-actor           |     1 |
| filename               |    10 |
| comment                |    14 |
| email-attachment       |     6 |
| campaign-id            |     4 |
| ssdeep                 |   168 |
| sha1                   |   195 |
+------------------------+-------+
20 rows in set (19.82 sec)

mysql>