as you know, you can define the retention period for qos-values in the data_engine. So after a certain amount of time, the qos-values will be deleted.
But there is one problem that might remain:
Let's imagine you monitor disk capacity for drive H: on a windows server called "serverA". You would then find qos-values for source=serverA, target=H: in your qos-tables.
Now, this drive no longer exists and the probe stopped collecting the values, after your retention period is over, those values will be deleted.
But in the Service Level Manager as well as in the Performance Report Designer / QosChart-Portlet in UMP, you will find a entry for serverA -> H: but without any values. That entry ( in the s_qos_data-table by the way) is a orphan.
You might want to clean up such data on a regular basis, especially when you did a lot of testing with some probes but in the end did not collect all QoS-metrics for production use.
For this purpose, I wrote a small lua-script which you can run in your environment. It is built to be run inside the Nimsoft Alarm Server (nas) and could be scheduled to be run automatically.
All you need to adapt is to modify the header of the lua-script:
- change iDays to the amount of days after which you want to get rid of qos-definitions. 7 means that 7 days after the last value was received for this Source/Target-combination, the data will be deleted.
- modify sDatabaseType to mysql if you're running mySql as the sql-queries are slightly different. If you run oracle, let me know and I will adapt the script with the correct statement for oracle.
NOTE: when you run this script the first time, the runtime might be quite long (many hours even). In this case, please do NOT execute the script directly but modify the script header to bHot = 0, the script will then print out SQL-statements which you can execute manually on your sql-commandline of your choice to clean up the database.
It is suggested that you run the script with bHot=0 at least for the first time and test the statements with a qos-type which is not critical for you. Please also make sure you have a backup of your database just in case you end up deleting qos-values you did not want to delete.