franz-josef-kaiser · February 19, 2022 10:59 · Feb 19, 2022 · Feb 19, 2022 · Feb 19, 2022 · Feb 19, 2022
diff --git a/README.md b/README.md
@@ -6,6 +6,7 @@ It's purpose is to clean up the UniFi Controllers MongoDB regularily and should
 The [official docs can be found here](https://help.ui.com/hc/en-us/articles/204911424-UniFi-Remove-prune-older-data-and-adjust-mongo-database-size#4).
 
 Usage:
+**Switch** `var dryrun = false;` **before running the script**.
 ```shell
 # `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.

diff --git a/README.md b/README.md
@@ -1,3 +1,4 @@
+# How to prune and clean up a UniFi Controller MongoDB
 This script is a copy of [this thread in the UI community forums](https://community.ui.com/questions/How-to-prune-the-database/9ae4f2f1-618c-4486-96d1-a58556588826) 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.
@@ -11,6 +12,14 @@ Usage:
 mongo --port 27117 < mongo_prune_js.js
 ```
 
+If MongoDB crashed and is not running:
+```shell
+sudo systemctl start mongod
+```
+
+---
+### Disclaimer
+
 The code is a 1:1 mirror of what is officially availabel [via UI help](https://help.ui.com/hc/article_attachments/115024095828/mongo_prune_js.js) 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.
diff --git a/README.md b/README.md
@@ -2,6 +2,15 @@ This script is a copy of [this thread in the UI community forums](https://commun
 
 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](https://help.ui.com/hc/en-us/articles/204911424-UniFi-Remove-prune-older-data-and-adjust-mongo-database-size#4).
+
+Usage:
+```shell
+# `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
+```
+
 The code is a 1:1 mirror of what is officially availabel [via UI help](https://help.ui.com/hc/article_attachments/115024095828/mongo_prune_js.js) 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.
diff --git a/unifi-mongodb_prune.js → mongo_prune_js.js b/unifi-mongodb_prune.js → mongo_prune_js.js
diff --git a/README.md b/README.md
@@ -2,6 +2,6 @@ This script is a copy of [this thread in the UI community forums](https://commun
 
 It's purpose is to clean up the UniFi Controllers MongoDB regularily and should probably best be used with a cron job.
 
-The code is a 1:1 copy and untested yet (02.2022).
+The code is a 1:1 mirror of what is officially availabel [via UI help](https://help.ui.com/hc/article_attachments/115024095828/mongo_prune_js.js) 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.
diff --git a/unifi-mongodb_prune.js b/unifi-mongodb_prune.js
@@ -1,109 +1,88 @@
-// keep N-day worth of datavar days=7;
+// 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 = false;
+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++) {
+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    
+		query = {time: {$lt:time_criteria}};
+	}
+
+	// rogue ap
 	if (name === 'rogue') {
-		query = {
-			last_seen: {
-				$lt: time_criteria_in_seconds
-			}
-		};
+		query = {last_seen: {$lt:time_criteria_in_seconds}};
 	}
-	// removes vouchers expired more than '$days' ago    
-	// active and unused vouchers are NOT touched    
+
+	// 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
-			}
-		};
+		query = {end_time: {$lt:time_criteria_in_seconds}};
 	}
+
 	// guest authorization
 	if (name === 'guest') {
-		query = {
-			end: {
-				$lt: time_criteria_in_seconds
-			}
-		};
+		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    
+	// blocked/fixed_ip. Also noted users should not be deleted.
 	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
-				}
-			}]
+		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
-			});
+			db.runCommand({ compact: name });
 		}
 	}
 }
+
+if (!dryrun) db.repairDatabase();
+
 use ace_stat;
 var collectionNames = db.getCollectionNames();
-for (i = 0; i < collectionNames.length; i++) {
+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
-			}
-		};
+
+	// 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
-			});
+			db.runCommand({ compact: name });
 		}
 	}
 }
-if (!dryrun) db.repairDatabase();
+
+if (!dryrun) db.repairDatabase();
diff --git a/README.md b/README.md
@@ -2,4 +2,6 @@ This script is a copy of [this thread in the UI community forums](https://commun
 
 It's purpose is to clean up the UniFi Controllers MongoDB regularily and should probably best be used with a cron job.
 
-The code is a 1:1 copy and untested yet (02.2022).
+The code is a 1:1 copy 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.
diff --git a/README.md b/README.md
@@ -1,3 +1,5 @@
 This script is a copy of [this thread in the UI community forums](https://community.ui.com/questions/How-to-prune-the-database/9ae4f2f1-618c-4486-96d1-a58556588826) 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.
+It's purpose is to clean up the UniFi Controllers MongoDB regularily and should probably best be used with a cron job.
+
+The code is a 1:1 copy and untested yet (02.2022).
diff --git a/README.md b/README.md
@@ -0,0 +1,3 @@
+This script is a copy of [this thread in the UI community forums](https://community.ui.com/questions/How-to-prune-the-database/9ae4f2f1-618c-4486-96d1-a58556588826) 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.
diff --git a/unifi-mongodb_prune.js b/unifi-mongodb_prune.js
@@ -0,0 +1,109 @@
+// keep N-day worth of datavar 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 = false;
+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();
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,3 @@
		This script is a copy of [this thread in the UI community forums](https://community.ui.com/questions/How-to-prune-the-database/9ae4f2f1-618c-4486-96d1-a58556588826) 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.
No results found