Comments (6)
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.
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.
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.
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.
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;
from mig.
The queries above deleted millions of records, but no space was reclaimed. This may be an AWS RDS quirk, or a Posgres one, I'm not sure yet.
from mig.
Related Issues (20)
- Implement API endpoint for documentation retrieval
- Implement API endpoint to retrieve module documentation
- Implement API endpoint for creating an action
- Implement API endpoint to dispatch a created action to the MIG API
- Implement API endpoint to check the status of a dispatched action
- Implement an action dispatch service
- Implement an action management service
- Implement API endpoints for action retrieval and adding signatures
- Deprecate mig.ninja namespace
- Feature Request: Get mig to parse ps -u output for processes run by specific usernames
- Client daemon fails to build with Yara module support
- Add options for gathering LLDP information via the netstat module HOT 2
- x509 Module HOT 1
- Kubernetes agent deployment
- Move all documentation over to markdown HOT 5
- Results count should indicate from how many systems
- SystemD unit file doesn't get updated when a new agent is installed
- Email address in Makefile is <noreply@> rather than something useful
- CODE_OF_CONDUCT.md file missing
- Ownership Transfer HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from mig.