README.md

How to prune and clean up a UniFi Controller MongoDB

This script is a copy of this thread in the UI community forums code, but reformatted.

It's purpose is to clean up the UniFi Controllers MongoDB regularily and should probably best be used with a cron job.

The official docs can be found here.

Usage: Switch var dryrun = false; before running the script.

# `27117` is the default MongoDB port
# If not, @see `/var/lib/unifi/system.properties` for `unifi.db.port=27117` that should hold the port if uncommented.
mongo --port 27117 < mongo_prune_js.js

If MongoDB crashed and is not running:

sudo systemctl start mongod

---

Disclaimer

The code is a 1:1 mirror of what is officially availabel via UI help and untested yet (02.2022).

The license and usage is unknown. Please contact me directly if you have concerns or need to have this taken down.

mongo_prune_js.js

// keep N-day worth of data
var days=7;

// change to false to have the script to really exclude old records
// from the database. While true, no change at all will be made to the DB
var dryrun=true;

var now = new Date().getTime(),
	time_criteria = now - days * 86400 * 1000,
	time_criteria_in_seconds = time_criteria / 1000;

print((dryrun ? "[dryrun] " : "") + "pruning data older than " + days + " days (" + time_criteria + ")... ");

use ace;
var collectionNames = db.getCollectionNames();
for (i=0; i<collectionNames.length; i++) {
	var name = collectionNames[i];
	var query = null;

	if (name === 'event' || name === 'alarm') {
		query = {time: {$lt:time_criteria}};
	}

	// rogue ap
	if (name === 'rogue') {
		query = {last_seen: {$lt:time_criteria_in_seconds}};
	}

	// removes vouchers expired more than '$days' ago
	// active and unused vouchers are NOT touched
	if (name === 'voucher') {
		query = {end_time: {$lt:time_criteria_in_seconds}};
	}

	// guest authorization
	if (name === 'guest') {
		query = {end: {$lt:time_criteria_in_seconds}};
	}

	// if an user was only seen ONCE, $last_seen will not be defined
	// so, if $last_seen not defined, lets use $first_seen instead
	// also check if $blocked or $use_fixedip is set. If true, do NOT purge the
	// entry no matter how old it is. We want blocked/fixed_ip users to continue
	// blocked/fixed_ip. Also noted users should not be deleted.
	if (name === 'user') {
		query = { blocked: { $ne: true}, use_fixedip: { $ne: true}, noted: { $ne: true}, $or: [
				{last_seen: {$lt:time_criteria_in_seconds} },
				{last_seen: {$exists: false}, first_seen: {$lt:time_criteria_in_seconds} }
			]
		};
	}

	if (query) {
		count1 = db.getCollection(name).count();
		count2 = db.getCollection(name).find(query).count();
		print((dryrun ? "[dryrun] " : "") + "pruning " + count2 + " entries (total " + count1 + ") from " + name + "... ");
		if (!dryrun) {
			db.getCollection(name).remove(query);
			db.runCommand({ compact: name });
		}
	}
}

if (!dryrun) db.repairDatabase();

use ace_stat;
var collectionNames = db.getCollectionNames();
for (i=0; i<collectionNames.length; i++) {
	var name = collectionNames[i];
	var query = null;

	// historical stats (stat.*)
	if (name.indexOf('stat')==0) {
		query = {time: {$lt:time_criteria}};
	}

	if (query) {
		count1 = db.getCollection(name).count();
		count2 = db.getCollection(name).find(query).count();
		print((dryrun ? "[dryrun] " : "") + "pruning " + count2 + " entries (total " + count1 + ") from " + name + "... ");
		if (!dryrun) {
			db.getCollection(name).remove(query);
			db.runCommand({ compact: name });
		}
	}
}

if (!dryrun) db.repairDatabase();