I’ve been rocking UniFi equipment at home for a long while now, I’ve been through many updates to the UniFi platform, and as a result, my client database has become rather ugly and unwieldy.

Many moons ago, UniFi published a script to purge the MongoDB database to alleviate the issue, but this has aged and since been removed from documentation. I mean, there’s a good chance this action is unsupported, but they did once recommend it.

Anyway, the name table that is referenced in this ancient script no longer exists, so running the once functional script does nothing. At least it appears to do nothing (your mileage may vary – be warned!).

In a bid to reduce my stale client list from hundreds, to merely none at all, filtered by only those connecting in the last 7 days, I ended up rolling the following script, which can be executed from the UniFi OS shell.

mongo --port 27117

After connecting to mongo, switch to the ace database.

use ace;

Then, once we’re on the ace database, we can run our queries. The code below will search the User table and return clients who have not connected within 7 days.

// Current time in seconds minus 7 days
var cutoff = Math.floor(Date.now() / 1000) - (7 * 24 * 60 * 60);

// Get all matching documents into a variable
var oldUsers = db.user.find(
  { last_seen: { $lt: cutoff } },
  { _id: 1, hostname: 1, mac: 1, ip: 1, last_seen: 1 }
).toArray();

// Optional: Print them with human-readable last_seen
oldUsers.forEach(function(doc) {
  doc.last_seen_human = new Date(doc.last_seen * 1000);
  printjson(doc);
});

You can change this timeframe to whatever period you like, for example, 30 days, the first line would read;

var cutoff = Math.floor(Date.now() / 1000) - (30 * 24 * 60 * 60);

Under the assumption we want to delete the clients that have not connected recently, you would then run the following code, which will group up all the clients returned in the result, add them to a variable, and then delete them from the User table.

// Extract IDs from the results
var idsToDelete = oldUsers.map(function(doc) { return doc._id; });

// Delete them
db.user.deleteMany({ _id: { $in: idsToDelete } });

Altogether, this reads;

// Current time in seconds minus 7 days
var cutoff = Math.floor(Date.now() / 1000) - (7 * 24 * 60 * 60);

// Get all matching documents into a variable
var oldUsers = db.user.find(
  { last_seen: { $lt: cutoff } },
  { _id: 1, hostname: 1, mac: 1, ip: 1, last_seen: 1 }
).toArray();

// Optional: Print them with human-readable last_seen
oldUsers.forEach(function(doc) {
  doc.last_seen_human = new Date(doc.last_seen * 1000);
  printjson(doc);
});

// Extract IDs from the results
var idsToDelete = oldUsers.map(function(doc) { return doc._id; });

// Delete them
db.user.deleteMany({ _id: { $in: idsToDelete } });

Pressing return and executing this will remove the stale devices. Happy days.