UniFi - How to Remove (Prune) Older Data and Adjust Mongo Database Size

Overview


Readers will learn how to adjust the database size used by the UniFi Controller, using a script to prune old statistics information.

UniFi uses MongoDB to store relevant information about the connected devices, controller configuration, clients and statistics.

A UniFi Controller managing many devices or logging many clients may become very large over time if left unchecked.  The following article explains how to use the prune script in order to remove statistics that are older than X number of days, including alarms, events, guest entries, detected rogue APs, known clients, expired vouchers, and traffic statistics.

The prune script will not remove data that is essential for the correct functionality of the controller, such as users that have been blocked.

Note: As of V5, the UniFi Controller offers the option to prune the database from within the Controller itself.  Assuming you are able to log into the UniFi Controller, please use the Compact Database feature under Settings -> Maintenance instead of following this article.

Table of Contents


 

 

 

 

 

 

 

 

 

 

 

 

How to Prune a Controller Hosted on Windows


Back to Top

Prerequisites: The controller needs to be running prior to executing the script.  If it is not running, please start the controller before performing the following steps.  Always remember to create a backup of your controller prior to performing any major changes, such as this one.

Step 1: Download mongo

The Windows UniFi installer does not include the mongo binary.  Visit the MongoDB official download website, select version 2.4.14 from the dropdown, and download the .zip release that corresponds to your server's CPU architecture.

Step 2: Extract mongo

Extract \bin\mongo.exe to a working directory of your choice.  In this example, we will use C:\prune\ You may ignore all other files included in the package.

Step 3: Download Script

Download the script to the server by clicking here, and save it to your working directory.

Step 4: Open Command Prompt

Open the command prompt by pressing WINDOWS + R.  In the popup, type cmd and press ENTER.

Step 5: Enter Working Directory

In the command prompt, change to your working directory:

cd C:\prune\

Step 6: Perform Test Run

By default, the script is in "dryrun" mode and will indicate what will be pruned from the database, without actually doing it.  This step is to make sure the script actually runs as expected:

mongo.exe --port 27117 < mongo_prune_js.js

The output should look similar to this:

Step 7: Disable "dryrun"

Edit the script to disable dryrun and configure the number of days worth of data to keep.  By default, the script will keep 7 days worth of data.  Use notepad or a similar plain-text editor to modify the script - do not use a rich text editor such as Wordpad or Word.  An editor such as Notepad++ is recommended as it will not add hidden characters and respects line breaks.

Edit var days=7; to change how many days worth of data to keep; and change var dryrun=true; to var dryrun=false; to allow the script to actually prune the database rather than just do a test run.

Step 8: Prune the Database

Run the modified script, actually purging the database this time:

mongo.exe --port 27117 < mongo_prune_js.js

Note: As the database is actually being modified, this step may take considerably longer compared to the test run in Step 6.  Do not interrupt the console until you receive a message that says bye.

Step 9: Verify Completion

Verify that the operation completed successfully and that no errors were reported.  The output should be similar to this:

Step 10 (Optional): Cleanup

Delete the directory you created and the files inside, if you don't intend to use it again.

 

How to Prune a Controller Hosted on Mac OS X


Back to Top

Prerequisites: The controller needs to be running prior to executing the script.  If it is not running, please start the controller before performing the following steps.  Always remember to create a backup of your controller prior to performing any major changes, such as this one.

Step 1: Create Working Directory

Create a working directory on the computer.  For the purpose of this article, we will be creating a directory named /prune.

Step 2: Download mongo

The Mac OS X UniFi installer does not include the mongod binary.  Visit the MongoDB official download website, click the OSX tab, select version 2.4.14 from the dropdown, and download the .tgz package.

Step 3: Extract mongo

Move the downloaded package to your working directory, and extract it by double clicking it or using the application of your choice.

Step 4: Move mongo to Working Directory

Locate the bin/mongo binary file, and copy it to your working directory.  At this point the downloaded .tgz package and any extra extracted files can be deleted as we only need the mongo binary.

Step 5: Enter Working Directory

Open a Terminal window, and change directories to the working directory created in Step 1:

cd /prune

Step 6: Download Script

Download the pruning script:

curl "https://help.ubnt.com/hc/en-us/article_attachments/204082688/mongo_prune_js.js" -o "mongo_prune_js.js"

Step 7: Perform Test Run

Perform a test-run of the script.  By default, the script is in "dryrun" mode and will indicate what will be pruned from the database, without actually doing it.  This step is to make sure the script actually runs as expected:

./mongo --port 27117 < mongo_prune_js.js

The output should look similar to this:

Step 8: Disable "dryrun"

Edit the script to disable dryrun and configure the number of days worth of data to keep.  By default, the script will keep 7 days worth of data.  Use nano or a similar editor to modify the script, as such:

nano mongo_prune_js.js

Assuming you are using nano to edit the file, use the arrow keys to navigate the text.  Edit var days=7; to change how many days worth of data to keep; and change var dryrun=true; to var dryrun=false; to allow the script to actually prune the database rather than just do a test run.  When ready, press CTRL + O to save the file (pressing Enter to confirm) and CTRL + X to exit.

Step 9: Prune the Database

Run the modified script, actually purging the database this time:

./mongo --port 27117 < mongo_prune_js.js

Note: As the database is actually being modified, this step may take considerably longer compared to the test run in Step 7.  Do not interrupt the console until you receive a message that says bye.

Step 10: Verify Completion

Verify that the operation completed successfully and that no errors were reported.  The output should be similar to this:

Step 11: End Session

End the Terminal session:

exit

Then close the Terminal window.

Step 12 (Optional): Cleanup

Remove the working directory, if you don't intend to use it again.

 

How to Prune a Controller Hosted on Linux (Ubuntu, Debian and Cloud Key)


Back to Top

Prerequisites: The controller needs to be running prior to executing the script.  If it is not running, please start the controller before performing the following steps.  You can start the controller by issuing the following command after Step 2:

sudo service unifi start

Always remember to create a backup of your controller prior to performing any major changes, such as this one.

Step 1: Establish Connection

Connect to your server via SSH using your preferred client and authenticate.  The screenshots in this section of the article are from PuTTY on Windows.  If using a Linux or Mac client, you can connect to the server using the built-in terminal.

Step 2: Enter Working Directory

Change to your home directory, or create a working directory of your choice.  For this article, the home directory will be used.

cd ~

Step 3: Download Script

Download the pruning script to your server:

wget https://help.ubnt.com/hc/en-us/article_attachments/204082688/mongo_prune_js.js

Step 4: Perform Test Run

Perform a test-run of the script.  By default, the script is in "dryrun" mode and will indicate what will be pruned from the database, without actually doing it.  This step is to make sure the script actually runs as expected:

mongo --port 27117 < mongo_prune_js.js

The output should look similar to this:

Step 5: Disable "dryrun"

Edit the script to disable dryrun and configure the number of days worth of data to keep.  By default, the script will keep 7 days worth of data.  Use nano or a similar editor to modify the script, as such:

nano mongo_prune_js.js

Assuming you are using nano to edit the file, use the arrow keys to navigate the text.  Edit var days=7; to change how many days worth of data to keep; and change var dryrun=true; to var dryrun=false; to allow the script to actually prune the database rather than just do a test run.  When ready, press CTRL + O to save the file (pressing Enter to confirm) and CTRL + X to exit.

Note: If you receive an error indicating nano is not installed, you can install it with the following command, then run the previous command again:

sudo apt-get update && sudo apt-get -y install nano

Step 6: Prune Database

Run the modified script, actually purging the database this time:

mongo --port 27117 < mongo_prune_js.js

Note: As the database is actually being modified, this step may take considerably longer compared to the test run in Step 4.  Do not interrupt the console until you receive a message that says bye.

Step 7: Verify Completion

Verify that the operation completed successfully and that no errors were reported. The output should be similar to this:

Step 8 (Optional): Cleanup

Remove the script from your home directory, if you don't intend to use it again:

rm mongo_prune_js.js

Step 9: End Session

End the terminal session:

exit

 

Prune Script for Reference


Back to Top

The pruning script itself is available for reference below: 

// 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
	if (name === 'user') {
		query = { blocked: { $ne: true}, use_fixedip: { $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 });
		}
	}
}

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();

 

Thanks to UBNT-yhlee! The current script in the article is updated for v4, and it is also attached (mongo_prune_js.js). We've also left an outdated script up, which is for previous controller versions (old_prune.js). Credits to leonardogyn for the older version.

Related Articles


Powered by Zendesk