Trim Statistics DB

Trim Statistics DB deletes all MOVEit Automation audit records older than a certain number of days, and optionally saves them to tab-separated files.

By default, MOVEit Automation has a pre-installed task that uses this script with parameters set to specific values. If you want to create your own task using this script, note the required parameters.

Input Parameters

• DirLog (Required) - The folder into which to write the system and error logs. In the pre-installed task, this parameter is set to \Program Files\MOVEit\MICStats\Logs. If you do not want system and error logs, set this value to blank.
• DirArchive - The optional folder into which to write the tab-delimited archives. In the pre-installed task, this parameter is set to \Program Files\MOVEit\MICStats. If you do not want deleted records archived to a text file, set this to an empty value.
• TrimStatsDB_DaysToRetainArchive - The number of days to retain the tab-delimited archive files. If the value is not set to zero, .tsv files older than the specified number of days that are found in "\Program Files\MOVEit\MICStats\Logs" (or the value of DirLog) are deleted. Deleted files are not put into the Recycle Bin, and cannot be retrieved.
• DSNArchive - The optional ODBC DSN (data source name) into which to save a copy of the records being deleted. In the pre-installed task, this parameter is set to an empty string, which means that the delete database records are not written to another database.
• DaysToKeep (Required) - The number of days to retain records. In the pre-installed task, this parameter is set to 40. This parameter applies to the task runs, stats and audit tables in the database.
  The archive files from each of the tables are stored in a .tsv file in the format <table-name>-<archive-date yyyy-mm-dd>.tsv For example, TaskRuns-2020-06-29.tsv
  Each .tsv archive file contains specific report information:
  • TaskRuns-<yyyy-mm-dd>.tsv - task run report data
  • Stats-<yyyy-mm-dd>.tsv - file/folder activity data
  • Audit-<yyyy-mm-dd>.tsv - audit logs of user actions and changes to the Automation configuration.
• DaysToKeepNoXfers - The number of days to retain TaskRuns records marked "No Xfers". If not supplied, no special action is taken. If you have many tasks that run frequently, you can set this parameter to a small number of days in order to reduce the size of the database and increase its performance. The default is empty, which means no special processing is done for these records.
• TrimStatsDB_ArchiveNoXfers - Specifies whether to archive or discard TaskRuns records that are marked "No Xfers". Default is No, meaning that records are discarded.
• DaysToKeepAudit - The number of days to retain Audit records. This parameter applies only to the Audit table in the database. If no value is specified, the value that was applied to DaysToKeep is used.
• TrimStatsDB_DateFormat - The date format that is in use. Usually only used on Windows systems outside the United States. The legal values are MonthDayYear (default) and DayMonthYear.
• TrimStatsDB_CommandTimeout - The number of seconds a client connection to the database remains actives without timing out. Default is 180 seconds (3 minutes).

Notes

Use this built-in script as the only step of a task. Process-level per-file or once-after-all-files settings will be ignored.

Over time, the statistics database can accumulate many records, slowing performance and using excessive amounts of disk space. To prevent this, MOVEit Automation is typically configured to run this built-in script periodically, optionally saving them to a file or another database before deletion.

Example #1

Bob has noticed that the various task history dialogs in MOVEit Automation have been sluggish lately and asks Progress for some advice. Progress notices that Bob has more than two hundred tasks that poll FTP servers every five minutes, all day long. Most (98%) of these polls do not yield files, but MOVEit Automation continues to log this information during each poll. Over the course of a single day, this means that more than 55,000 "no xfer" actions are logged to the database. Bob knows that "no xfer" information can be of value in debugging, but Progress would prefer that he remove them after a few days and only retain "success" or "failure" audit entries for long periods of time. Bob and Progress decide to retain one week of "no xfer" records, and to keep 60 days of "success" and "failure" logs in the database. The "no xfer" records will not be saved to the longer term archive logs.

To accomplish this with MOVEit Automation:

1. Select the existing "Trim Stats DB" task, which already has a process and schedule.
2. Set process parameters:
   • DaysToKeep = "60"
   • (Add) DaysToKeepNoXfers = "7"