We currently store DB records forever, and after two years of running MIG on thousands

Some numbers: command records <div class="snippet-clipboard-cont

Running this manually right now. <div class="highlight highlight-source-sql notran

Query to delete commands after 1 year: <div class="highlight highlight-source-sql

Running now: <div class="highlight highlight-source-sql notranslate position-relat

Purge database records periodically about mig HOT 6 OPEN

mozilla commented on July 24, 2024

Purge database records periodically

from mig.

Comments (6)

jvehent commented on July 24, 2024

Some numbers:

command records

mig=> select count(*) from commands;
20417300

agent records

mig=> select count(*) from agents;
21086710

Agent records that don't have any commands:

mig=> SELECT COUNT(*) from (SELECT id FROM agents WHERE agents.heartbeattime < NOW() - INTERVAL '30 days' EXCEPT SELECT agentid FROM commands) as q;
15033316

About 71% of agents never get targeted and can be deleted. I'm calling this a win :)

@ameihm0912 , @gdestuynder : any thoughts?

from mig.

gdestuynder commented on July 24, 2024

Best would probably be to make this configurable to accommodate different people's retention needs (including "0" for forever) - but yes i noticed the db was getting big before.
I also wonder how much space it would take if exported + compressed (for those who need long term history)

from mig.

jvehent commented on July 24, 2024

Running this manually right now.

DELETE FROM agents
WHERE id IN (SELECT id FROM agents
             WHERE agents.heartbeattime < NOW() - INTERVAL '30 days'
             EXCEPT SELECT agentid FROM commands);

from mig.

jvehent commented on July 24, 2024

Query to delete commands after 1 year:

SELECT COUNT(*) FROM commands WHERE finishtime < NOW() - INTERVAL '365 days';
 1076359

Query to delete agentdestroy commands after 90 days:

SELECT COUNT(*) FROM commands
WHERE actionid IN (SELECT id FROM actions
            WHERE operations->0->>'module'='agentdestroy'
            AND expireafter < NOW() - INTERVAL '90 days');
 74699

from mig.

jvehent commented on July 24, 2024

Running now:

DELETE FROM commands
WHERE finishtime < NOW() - INTERVAL '365 days';

DELETE FROM commands
WHERE actionid IN (SELECT id FROM actions
                   WHERE operations->0->>'module'='agentdestroy'
                   AND expireafter < NOW() - INTERVAL '90 days');

DELETE FROM agents
WHERE id IN (SELECT id FROM agents
             WHERE agents.heartbeattime < NOW() - INTERVAL '30 days'
             EXCEPT SELECT agentid FROM commands);

VACUUM ANALYZE;