# 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.

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/).

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.

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.

Specify settings by adding entries with the following syntax:

Core Server:

AA:B:CCC

Analysis Server:

SDW:AA:B:CCC

Where:

Variable	Description
A	The 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.
B	The 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.
C	The 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. It is possible to provide the value 0, but it is not recommended.
SDW	Signifies 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

Save the changes.

Open database_cleanup.conf in /var/opt/openit/etc.

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.

Specify settings by adding entries with the following syntax:

Core Server:

AA:B:CCC

Analysis Server:

SDW:AA:B:CCC

Where:

Variable	Description
A	The 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.
B	The 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.
C	The 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. It is possible to provide the value 0, but it is not recommended.
SDW	Signifies 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

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.

Go to the bin directory. It is by default in C:\Program Files\OpeniT\Core\bin\.
Open a command prompt with Administrator level privilege.

Run the following command:

database_cleanup.bat [options]

Options:

Options	Description
--force	Physically 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	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 that this option is ignored when using single.
--single	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.
--single	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.
--advice	This 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.
--verbose	This option gives more verbose output and prints files that can be considered for deletion.
--debug	This 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

will show:

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

will show:

C:\ProgramData\OpeniT\Data\\database/102/M/2017
C:\ProgramData\OpeniT\\Data\\database/102/W/2017

All identified deletions executed

Go to the bin directory. It is by default in /opt/openit/bin.
Open a command prompt with Administrator level privilege.

Run the following command:

database_cleanup.bat [options]

Options:

Options	Description
--force	Physically 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	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 `/var/opt/openit/etc`) is used. Note that this option is ignored when using single.
--single	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.
--single	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.
--advice	This 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 `/var/opt/openit/etc/temp` 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.
--verbose	This option gives more verbose output and prints files that can be considered for deletion.
--debug	This 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 --advice

will show:

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 /var/opt/openit/etc/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 --force

will show:

/data/database/102/M/2017
/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:

Open core_database_cleanup-win.oconf in C:\Program Files\OpeniT\Core\Configuration\scheduler.
Locate and set active value to true to activate periodic cleanup of old database based on the cleanup settings.
core_database_cleanup-win.oconf
```
...
 7|    core_database_cleanup
 8|    {
...
16|        active
17|        {
18|          type=bool
19|          value=true
...
```
To change the scheduling, locate when and change the value. The default value is sunday. This accepts any day of the week.
core_database_cleanup-win.oconf
```
...
57|    scheduling
58|    {
...
69|        when
70|        {
71|          type=string
72|          value=sunday
...
```
Save the changes.

Open core_database_cleanup-unix.oconf in /var/opt/openit/etc/scheduler.
Locate and set active value to true to activate periodic cleanup of old database based on the cleanup settings.
core_database_cleanup-unix.oconf
```
...
 7|    core_database_cleanup
 8|    {
...
16|        active
17|        {
18|          type=bool
19|          value=true
...
```
To change the scheduling, locate when and change the value. The default value is sunday. This accepts any day of the week.
core_database_cleanup-unix.oconf
```
...
57|    scheduling
58|    {
...
69|        when
70|        {
71|          type=string
72|          value=sunday
...
```
Save the changes.