Skip to main content
Version: 10.2

Configuring Database Cleanup

This tool from Open iT is designed to efficiently sanitize the database by removing outdated data and decreasing its size when it has become excessively large.

note

Database cleanup cannot be used to fix corrupted data, archive data, or for other data-level activity. The administrator should follow the usual practices or contact Open iT Support in such instances.

Restrictions on Usage​

Due to possible inconsistencies in the database, this tool should not run while loading data. The data loaded will be newer than the data deleted by this program.

This program must be run by the Open iT user or someone with read privileges for the Open iT database. If using the force option, the user needs additional write privileges. When generating a listing file, the indicated directory must also be writable.

To clean up data types that use (calculate) MAX/MIN values, make sure to retain the data one level below the levels that are still active. For example, when deleting data for the first half of a year while still collecting data for the last half of the year, the monthly data from the year's first half must be retained to ensure the correct calculation of yearly MAX/MIN values. To identify which data types calculate MAX/MIN, refer to the acc_types file (it is by default in C:\Program Files\OpeniT\Core\Configuration\ or /var/opt/openit/etc/).

note

Reports generated using virtual classifications will no longer work as intended if the day level of data for the time covered by the report has been removed. For data collected weekly (as may be the case with File Space data), this applies to the lowest level of data, that is, when the weekly data for those data types are deleted.

Database Cleanup Settings​

This configuration file includes parameters that determine how the cleanup process is executed and which data type is targeted for deletion.

  1. Open database_cleanup.conf in C:\Program Files\OpeniT\Core\Configuration\.

    Notice that it contains instructions on how to edit the file. If configuring Analysis Server, follow the instructions specified for Analysis Server; and if configuring Core Server, follow the instructions specified for Core Server.

  2. Specify settings by adding entries with the following syntax:

    Syntax
    Core Server:

    AA:B:CCC

    Analysis Server:

    SDW:AA:B:CCC

    Where:

    VariableDescription
    AThe number representing the Open iT data type. In Core Server, this accepts the archive types (e.g., licpoll, licpoll2, freeze). In Analysis Server, it supports the wild card symbol "*" which means all data types.
    BThe level of data to clean up from the database. Specifying a high data level means cleaning all the data below it. Specifying 'M' will clean all the data in M, W, D, and H. In Analysis Server, only Y, M, and W are supported.
    • Y - Year
    • M - Month
    • W - Week
    • D - Day
    • H - Hour and 5 minutes
    In Analysis Server, only Y, M, and W are supported.
    CThe age of the data before it is deleted. This parameter is exclusive; it does not include the current Year, Month, Week, Day, or Hour of execution. Setting 4 in week 16 will delete all data up to week 11. Note: It is possible to provide the value 0, but it is not recommended.
    SDWSignifies that the entry is for Analysis Server or SQL Data Warehouse.
    • Example 1: The following configuration deletes StorageAnalyzer data types, Filesystems and Filespace, in the Core Server and Analysis Server that are older than three years.
      • 24:Y:3
      • 29:Y:3
      • SDW:24:Y:3
      • SDW:29:Y:3
    • Example 2: The following configuration deletes Licenseevents data types in the Core Server that are older than 42 months or 3 and a half years.
      • 89:M:42
      • 90:M:42
      • 91:M:42
      • 104:M:42
      • 107:M:42
      • 108:M:42
      • 109:M:42
    • Example 3: The configuration below deletes all data types in the Analysis Server that are older than five years.
      • SDW:*:Y:5
    Variable Descriptions

  3. Save the changes.

Manually Running Database Cleanup​

Database cleanup has various options that may be used, either alone or with other options, to fulfill the requirements.

  1. Go to the bin directory. It is by default in C:\Program Files\OpeniT\Core\bin\.

  2. Open a command prompt with Administrator level privilege.

  3. Run the following command:

    Command Syntax
    database_cleanup.bat <options>

    OPTIONS:

    OptionsDescription
    --forcePhysically deletes files specified in the configuration. No cleanup is performed if this option is not given, but a list showing what could be deleted, according to the configuration, will still be displayed.
    Be careful in using this option, either with or without other options. If other options are not specified along with this, then defaults will be used. The same is true for options such as config and databasedir.
    --config <CONFIGFILE>This option specifies the configuration file and is used along with the force option. If not given, the default configuration file database_cleanup.conf (usually in C:\Program Files\OpeniT\Core\Configuration\) is used.
    Note: This option is ignored when using single.
    --single <TARGET>This option specifies a single target for deletion. When chosen, this ignores the configuration file altogether. When the force option is not used along with this, a list of deletable variables will be shown without actually deleting the target.
    --adviceThis option displays suggestions for rules for all data types that exist in the database and are not targeted by any cleanup rules. Thereafter, the program exits. This option is ignored when using single.
    --listdeletions <LOGFILE>This option sends a list of deletable variables to the log file named LOGFILE. If the log file is not given, a generated log file named TIMESTAMP will be used and placed in the Log directory (it is C:\Program Files\OpeniT\Core\Log by default). This file has information on whether force was used, whether the deletions were completed in a normal manner or not, and which files were targeted. It is advisable to keep track of the deletions performed using this utility. The listdeletions does so by generating a log file containing the targeted (deleted) files. If an existing log file name is given, listdeletions appends the list to that file. Otherwise, it creates a new file as specified and adds the entries.
    --verboseThis option gives more verbose output and prints files that can be considered for deletion.
    --debugThis option prints out usable logs for debugging the program.
    --help or -?This option prints out the notes and list of options to use the database cleanup tool.
    Options Descriptions

    Example 1
    database_cleanup.bat --advice
    Example 1 Output 
    As far as we can detect, you have some datatypes that aren't set up to be cleaned.
    If you wish to do so, you can use the following as an advisory on various option
    on how to remove data.

    You can paste the best options from this into your config-file.
    Currently C:\\Program Files\OpeniT\Core\\Configurations/database_cleanup.conf

    # Rules for datatype: 102 'Olap Total Use'
    102:Y:3   #Delete all data older than 3 years
    102:M:18  #Delete montly data older than 18 months
    102:D:180 #Delete daily data older than 180 days (~ 6 months)
    102:H:90  #Delete hourly and 5-minute data older than 90 days (~3 months)

    # Rules for datatype: 95 'Usergroup License Use v3.0'
    95:Y:3   #Delete all data older than 3 years
    95:M:18  #Delete montly data older than 18 months
    95:D:180 #Delete daily data older than 180 days (~ 6 months)
    95:H:90  #Delete hourly and 5-minute data older than 90 days (~3 months)
    Example 2
    database_cleanup.bat --force
    Example 2 Output
    C:\ProgramData\OpeniT\Data\\database/102/M/2017
    C:\ProgramData\OpeniT\\Data\\database/102/W/2017

    All identified deletions executed

Configuring Periodic Database Cleanup​

In addition to manually running database cleanup, a built-in scheduler functionality allows administrators to automate the process of running cleanup tasks at regular intervals. To set up periodic database cleanup:

  1. Open a command prompt with Administrator level privileges.

  2. Go to the bin directory, which is by default in C:\Program Files\OpeniT\Core\bin, run the command:

    Command Syntax
    cd $BIN_DIR
    Example
    cd C:\Program Files\OpeniT\Core\bin

  3. Once in the directory, activate the periodic cleanup of old database based on the cleanup settings, run the command:

    Command Syntax
    openit_oconfinit -u "core_database_cleanup-win.root.scheduler.jobs.core_database_cleanup.general.active=true"

  4. To change the scheduling, run the command:

    Command Syntax
    openit_oconfinit -u "core_database_cleanup-win.root.scheduler.jobs.core_database_cleanup.scheduling.start-triggers.trigger#1.when=<day_of_week>"
    note

    The default value is sunday. This accepts any day of the week.

    Example
    openit_oconfinit -u "core_database_cleanup-win.root.scheduler.jobs.core_database_cleanup.scheduling.start-triggers.trigger#1.when=monday"