NetLock RMMNetLock RMM Docs
IV — Administration

Database management

Retention and cleanup of the history tables, the daily table optimisation window, optimisation on demand, and the ad-hoc SQL console.

Database management

Self-hosted only: The Database Management page is hidden on cloud deployments; the hosted team manages optimisation and backup centrally, and the page renders the same "Some settings that are available in the On-Premises version are not visible here" notice as Overview and Licensing. The Retention page described in A.3.1 is available on cloud instances as well, with the retention ceilings the cloud platform enforces and without the device telemetry switches.

Two pages share this chapter. Settings → Retention at /settings/retention controls how much history the database keeps and shows what the cleanup is doing. Settings → Database at /settings/database shows the MySQL status, rebuilds tables on demand and, for power users, runs ad-hoc SQL against the schema. The SQL Console tab is gated behind its own permission flag, so not every administrator with settings_database_enabled will see it.

Connection settings, backups and schema changes are handled outside these pages. The connection string is loaded from appsettings.json at startup and cannot be edited from the UI, and schema upgrades run on their own when the web console starts. A.3.4 lists where each of these tasks is handled.

A.3.1 Retention & Cleanup

Settings → Retention is the page of this chapter that admins use most. It lets you set, per table, how long NetLock RMM keeps historical rows before the cleanup removes them, and it shows the state of the cleanup itself.

Retention page with the status card at the top and the retention cards below

The retention cards

Each card stands for one table of history data and has two controls:

  • Enable cleanup: a per-table toggle. When off, the cleanup never touches this table, regardless of the day count. When on, the cleanup removes rows older than the configured threshold.
  • Delete entries older than (days): a numeric input in days. The value is interpreted relative to the row's own timestamp column; rows younger than the threshold are retained.

The cards are grouped: device telemetry (CPU, RAM and disk history, self-hosted only), compliance and audit (events history, audit logs, API request log, device action queue, device authentication log), database maintenance (the daily optimisation window, see below), website uptime monitoring (check results, resolved incidents, SSL snapshots), remote screen recordings and the event retention rules, which give single kinds of events a retention of their own. Two retention settings live elsewhere: the update history and the scheduled patch run history are configured on the Settings tab of /patch-management, but the same cleanup deletes them, so they appear in the status card like every other table.

The section Integrations holds the history of integration connections (finished jobs only, 30 days by default), their log (14 days by default, counted from the last occurrence of an entry), the records of removed devices that integrations and the device.deleted webhook read (30 days), and the daily device usage behind usage figures and contract quantities (400 days). Keep the usage rows at least as long as your longest billing period. The Open alerts card does not delete anything. It sets how many days an open alert of any kind stays open without a new event before it ends (30 by default). One-shot alerts such as an antivirus finding also end when the event is read or hidden.

The events history keeps some events regardless of their age: the event that opened an alert stays while the alert is open, and while an integration still follows it through a ticket. Later events of an alert age normally, and an event ages normally once its alert is resolved and no ticket link is open. The section Integrations shows how many events are kept this way. The event rules and the size limit of a cloud instance respect the same condition.

Three quick buttons at the top set every toggle at once (Enable all, Disable all) or load the recommended values (Apply recommended defaults). Nothing is applied until you click the one Save retention settings button at the bottom of the page. The confirmation names the time of the next cleanup run, which is when the new values take effect.

Choosing retention values

The right values depend on your compliance requirements and your disk budget. A few rules of thumb:

  • Operational tables (telemetry samples, uptime check results) usually work well with 30 to 90 days.
  • Audit-adjacent tables (who did what, when) typically need longer. The page recommends at least 365 days, and regulated environments often go beyond that.
  • Session recordings grow fastest on disk, so with tight storage a shorter retention here saves the most space. The Remote Screen Control page (Chapter A.7) also exposes a separate Auto purge specifically for session recordings.

Start with Apply recommended defaults, watch the database size on the Overview page (Chapter A.1.1) and the Table sizes on Settings → Database, and narrow down from there.

How the cleanup runs

The cleanup is a background job of the web console. It runs once right after the web console starts and then every hour, measured from the end of the previous run. On every run it reads the current retention settings and deletes, table by table, the rows that are older than the retention of that table. A table with millions of expired rows is finished in one run. If a delete fails, the run logs it and continues with the next table.

The run only deletes. The space of deleted rows stays inside the table files, where InnoDB reuses it for new rows, so with a rolling retention the files settle at a stable size on their own. Handing that space back to the file system means rebuilding the table, and that is what the daily optimisation window below does.

The status card

The card under the page title shows what the cleanup of this web console process is doing. The left half is the cleanup:

  • a chip that says Idle or Running; while a run is in progress the card names the table being deleted from, the batch number and the rows deleted so far, and the page redraws every two seconds until the run has ended;
  • the last run with its time, trigger (scheduled or manual) and duration, followed by one chip per table with the rows it lost; a table whose delete failed is marked in red with a hint to the log;
  • the time of the next scheduled run.

The right half is the database optimisation: the last optimisation (time, trigger, duration, number of tables, bytes handed back), the next window with its time zone (or the note that the window is switched off), and the tables the next window would rebuild right now.

The card keeps no history across restarts: after a restart it says that no run has happened yet until the first run, which starts within seconds.

Four buttons close the card:

  • Run cleanup now starts a cleanup run right away and hands it to the background job; the page does not wait for it. The same run cleans up the session recordings on the NetLock server. A run that is already in progress is reported instead of being queued.
  • Optimize now runs the daily optimisation window right away, with the same tables the scheduled window would pick. Read the note on the search index below before using it during working hours.
  • Estimate pending rows counts, for every table configured on this page, the rows the next run would delete with the values currently in the form, including unsaved changes, so you can see what lowering a retention would remove before saving. Event retention rules are not applied to the estimate. A count stops at one million rows and says "more than".
  • Refresh reads the state and the candidate tables again.

Every action needs the same permissions as the page; Run cleanup now and Optimize now are written to the audit log.

The daily optimisation window

Deleting rows frees space inside the table files; rebuilding a table (OPTIMIZE TABLE) rewrites the whole table and its indexes and is the only way to hand that space back to the disk. A rebuild is expensive because it writes the entire table once, so the web console does it once a day, in a window you set in the Database maintenance section of the page:

  • Optimize tables daily switches the window on or off.
  • Start time is the start of the window on the wall clock of the instance display time zone (Settings → Globalization); the caption under the picker names that zone. The window stays open for one hour after the start time, so a run that is delayed by a long cleanup still fits.

Within the window, the web console rebuilds every retention table whose reclaimable free space is at least 64 MB and at least a quarter of the file; a table below that threshold is left alone, because the rewrite would cost more than the space it returns. The inventory search index is rebuilt in every window regardless of the threshold: its full-text index keeps the entries of deleted rows until the table is rebuilt, and that space is invisible to the statistics the threshold is read from. The status card names the tables the next window would pick.

Two things to know about the window:

  • A window the web console was not running for is skipped, not caught up. A restart after the window does not rebuild every table in the middle of the day.
  • The rebuild of the inventory search index holds a write lock on that table for its duration (seconds on a small installation, a few minutes on a large one). Inventory syncs that arrive in that time wait for the lock; nothing is lost. The default start time is therefore 03:00, and Optimize now asks for confirmation before it runs.

If innodb_file_per_table is switched off on your MySQL server (it is on by default and in the compose files of the installation guide), every table lives in the shared system tablespace: the free space the threshold reads describes that shared file and a rebuild never hands space back to the operating system. The window still compacts the tables, but the disk usage you see will not drop.

A.3.2 Optimising database tables on demand

The Status tab of Settings → Database has an Optimize database action. Clicking it opens a confirmation dialog:

Optimize database tables?

This runs OPTIMIZE TABLE on all major tables. Tables may be briefly locked; running against a multi-GB database can take several minutes. Continue?

Choosing Optimize rebuilds the core tables of the schema, every table the retention cleanup deletes from (events, audit logs, telemetry history, API request log, update history, patch history, uptime monitoring, device authentication log) and the inventory search index, one table after the other. There is no time limit per table. Each rebuild runs to completion, and a table that fails is logged while the run continues with the rest. The action reclaims the space freed by deleted rows and rebuilds the indexes.

Caution: OPTIMIZE TABLE rebuilds each table it processes, and the inventory search index is write-locked while it is rebuilt. On a large NetLock RMM database the whole run can take several minutes, during which parts of the Console will feel sluggish. Run the action off-peak, ideally from within a maintenance window (see Chapter A.2.2) to prevent noisy notifications for the duration.

When to run it

With the daily window in place there is rarely a reason to run the action by hand. Reasonable triggers include:

  • You lowered a retention substantially, the next cleanup has run, and you want the space back before the night. Optimize now on the Retention page does the same for the retention tables and asks first.
  • You restored a database from a dump or moved it between servers and want the files compacted once.
  • You switched the daily window off and compact the tables on your own schedule.

A.3.3 The SQL Console tab

The SQL Console tab of Settings → Database gives administrators a free-form SQL editor against the NetLock RMM database. It is a power-user tool and is gated separately:

Requires permission: settings_system_mysql_console. Without this flag the tab does not appear at all.

SQL Console tab showing a query in the textarea and a result grid beneath it

The editor

The tab has a multi-line Query textarea with placeholder text Enter SQL query..., and two buttons.

  • Execute runs the query against the current NetLock RMM database. The default query timeout is 30 seconds; long-running statements are cut off to prevent a runaway query from holding the connection pool.
  • Clear empties the editor.

Query results render as a standard table beneath the editor. Column headers come from the query's column list, and rows are paginated when results are large.

The Cell Value dialog

Clicking a cell opens the Cell Value dialog, which shows the full value without table-row truncation. The dialog includes:

  • The originating column name as its title.
  • The cell's length in characters.
  • A Copy button that places the value on the clipboard.
  • A Close button.

When the cell contains JSON, which is common for NetLock RMM fields that store structured configuration, the dialog pretty-prints it with indentation. This is the fastest way to inspect a single policy's JSON blob or a theme palette entry without leaving the Console.

Guardrails and responsibility

The SQL Console runs against the live production database under the Console's connection credentials. Statements run without a preview and without read-only enforcement: an UPDATE, INSERT, or DELETE issued here is committed immediately. Two principles keep this safe:

  1. Only grant settings_system_mysql_console to deployment operators who need it. Treat it as a high-risk permission.
  2. Use the console for read queries by default. When you do need to mutate data, assume you will need to re-run the same statement against a backup; take a logical dump of the affected tables first.

The SQL Console is not a substitute for the regular admin pages. Prefer the dedicated UI for anything it can do (Retention, Users, Tenants, Policies), and reserve the SQL Console for diagnostics and the rare fix that no UI covers.

What the SQL Console cannot do

  • It cannot switch databases; the connection is fixed to the one configured in appsettings.json.
  • It cannot source multi-statement scripts; run one statement at a time.
  • It cannot stream large result sets. The practical result size is limited by how many rows the page can render.

A.3.4 What these pages do not cover

The following tasks are handled elsewhere, or outside the Console:

  • Connection string editing. MySQL.Connection_String is read once from appsettings.json at startup. To change it, edit the file on the Console host and restart the Console.
  • Backup and restore. The Console has no backup or restore function. Backups are your responsibility at the host level; the recommended approach is mysqldump on a schedule outside NetLock RMM, with backups shipped to offsite storage.
  • Schema upgrades. The web console applies the schema changes of a release on its own when it starts; no manual step is needed. Deploy the web console before the server, because the web console is what applies the schema changes.
  • MySQL server upgrades. The status card shows the running MySQL version and a warning when that version is older than the recommended release (currently 8.4). The upgrade itself happens at the host level; Upgrade MySQL to 8.4 covers it for the Docker container.
  • Per-tenant retention. Retention is global: it applies to the table, not to the rows of a specific tenant. If a tenant requires a different retention, filter the reports you deliver to that tenant; the database configuration cannot do this.
  • Automatic backup on the optimise action. Neither Optimize database nor the daily window takes a backup before running; both assume you have your own backup strategy in place. Take a backup out-of-band if you need a guaranteed rollback point.
  • Query history in the SQL Console. The SQL Console does not record a history of past queries. If you rely on a particular diagnostic statement repeatedly, keep it in your own notes or a script file rather than expecting the Console to remember it.

Suggested operator workflow

The following routine suits most deployments:

  1. On a fresh deployment, open Settings → Retention, click Apply recommended defaults, and Save retention settings. Leave the daily optimisation window on and move its start time into your quietest hour if 03:00 is not it.
  2. A few days later, review the database size in Settings → Overview (Chapter A.1.1) and the table sizes on Settings → Database. If growth is faster than expected, narrow down the retention on the noisiest tables (typically telemetry, events and the API request log), use Estimate pending rows to see what the change removes, and save again.
  3. After lowering a retention by a lot, wait for the next cleanup run (the status card names the time) and either leave the space to the nightly window or click Optimize now.
  4. Delegate the SQL Console permission (settings_system_mysql_console) only to the operators who regularly need it; revoke it for people who occasionally need one-off lookups but can have you run the query for them.

Permissions

CapabilityRequired flags
View the Retention page, save its settings, run or estimate the cleanup, run the optimisation windowsettings_enabled, settings_retention_enabled
View the Database page and run Optimize databasesettings_enabled, settings_database_enabled
See the SQL Console tabsettings_enabled, settings_database_enabled, settings_system_mysql_console
  • Chapter A.1 — System overview & licensing. The Overview page reports live MySQL health and active queries.
  • Chapter A.2 — Updates & maintenance. Use a maintenance window to cover a large optimise run.
  • Chapter A.7 — Remote screen control. Session recordings have their own retention control that sits alongside the Retention page's.
  • Chapter 12 — Events & Audit. Event and audit retention and the event retention rules are driven from the Retention page.
  • How-to — Upgrade MySQL to 8.4. What to do when the status card warns about the MySQL version.