Manually Trim the Audit Database


Removing older data by archiving it in a CSV file can help reduce the size of your audit database as well as improve the performance of audit logging and the system where the audit logs are stored.

Trimming the audit database for FileCloud uses the following procedure:

You will need to log in to the MongoDB database to trim it. Use the following information when logging on.

Table 1. MongoDB Log In Options

OptionDescription

Example

(lightbulb) Always replace the sample values in the examples with the values used in your deployment.

--port <port>

Specifies the port where the mongod or mongos instance is listening.

If --port is not specified, mongo attempts to connect to port 27017.

--port 27017
--username <username>, -u <username>

Specifies a username with which to authenticate to a MongoDB database that uses authentication.

Use in conjunction with the --password and --authenticationDatabase options.

-u "myUserAdmin"
--password <password>, -p <password>

Specifies a password with which to authenticate to a MongoDB database that uses authentication.

Use in conjunction with the --username and --authenticationDatabase options.

To force mongo to prompt for a password, enter the --password option as the last option and leave out the argument.

-p "abc123"
--authenticationDatabase <dbname>

Specifies the database in which the user is created. See Authentication Database.

If you do not specify a value for --authenticationDatabase, mongo uses the database specified in the connection string.

--authenticationDatabase "admin"

To log in to the mongo shell, use the following command: (replace the settings in this example with settings used in your deployment)

mongosh --port 27017 -u "myUserAdmin" -p "abc123" --authenticationDatabase "admin"

You can use mongoexport to produce a JSON or CSV export of data stored in a MongoDB instance.

Table 1. MongoExport Options

OptionDescriptionExample
--db <database>, -d <database>Specifies the name of the database on which to run the mongoexport.
--db tonidoauditdb
--collection <collection>, -c <collection>Specifies the collection to export.
--collection audit
--type <string>

Default: json

New in version 3.0.

Specifies the file type to export. Specify csv for CSV format or json for JSON format.

If you specify csv, then you must also use either the --fields or the --fieldFile option to declare the fields to export from the collection.

--type=csv
--fields <field1[,field2]>, -f <field1[,field2]>

Specifies a field or fields to include in the export. Use a comma separated list of fields to specify multiple fields.

If any of your field names include white space, use quotation marks to enclose the field list.

For example, to export two fields, phone and user number, you would specify --fields "phone,user number".

For csv output formats, mongoexport includes only the specified field(s), and the specified field(s) can be a field within a sub-document.

--fields createdon,username,how,ip,useragent,operation,request,deviceinfo
--out <file>, -o <file>

The output directory where you need to export data as a csv.

You must change this location to match the directory in your deployment.

--out c:\xampp\test.csv


Export considerations:

  • You must run mongoexport against a running mongod or mongos instance as appropriate.
  • Avoid using mongoimport and mongoexport for full instance production backups. These utilities do not reliably preserve all rich BSON data types, because JSON can only represent a subset of the types supported by BSON. Use mongodump and mongorestore as described in MongoDB Backup Methods for this kind of functionality.

  To read more about this utility, read the MongoDB documentation for MongoExport.

To export the Audit database:

  1. Ensure that MongoDB is running before attempting to start the mongo shell.
  2. Log into the primary database as the admin user through mongo shell.
  3. Run the following command:

    mongoexport.exe --db tonidoauditdb --collection audit --type=csv --fields createdon,username,how,ip,useragent,operation,request,deviceinfo --out c:\xampp\test.csv
    
    

It is important to check that the data has been successfully exported before removing it from the MongoDB database.

To verify the exported data:

  1. Navigate to the output directory where you saved the csv file. For example: c:\xampp\test.csv
  2. Open the csv file in Excel or another editor.
  3. Verify the data looks correct.

After you have exported the data to a csv file, you can now drop the collections in the auditdb database.

The drop command removes a collection or view from the database.

The drop method also removes any indexes associated with the dropped collection.

  • The method provides a wrapper around the drop command.
  • This method obtains a write lock on the affected database and will block other operations until it has completed.
  • The db.collection.drop() method and drop command create an invalidate Event for any Change Streams opened on dropped collection.
  • Starting in MongoDB 4.0.2, dropping a collection deletes its associated zone/tag ranges.

 

To drop the exported data from auditdb:

  1. Ensure that MongoDB is running before attempting to start the mongo shell.
  2. Log in to the mongo shell

  3. To print a list of all databases on the server, run the following command:
    show dbs;
  4. To switch to the database provided as a parameter, run the following command:

    use tonidoauditdb

     

  5. To list all the databases available for use on the connected MongoDB instance, run the following command:

    show collections
  6. To remove an entire collection from a database, use the following command:

    db.audit.drop()
  7. You will see one of the following responses:

    Returns:
    true when successfully drops a collection.
    false when collection to drop does not exist.
  8. To list all the databases available for use on the connected MongoDB instance, run the following command:

    show collections




 

You should see responses similar to the following example: