One of the main features of UCS Central is the ability to store statistics in an internal or external database for use in monitoring, trending and capacity planning. Regardless of how big or small the underlying database is sized, there may come a time when administrators will want to purge "old" statistics data, according to whatever retention period applies.
The following script can be used to accomplish this task. The script below will not directly connect to the database, but rather this script provides the commands that would need to be applied.
#!/bin/bash
re='^[0-9]+$'
echo "*******************************************************************************************************"
echo " UCS Central - Purge old Statistics Data"
echo "This script will generate the required SQL scripts that you can run in your database to purge old data"
echo "*******************************************************************************************************"
echo
if [ -z "$*" ] || ! [[ $1 =~ $re ]] ; then
echo "Usage: generate_stats_trim_sqls.sh <number_of_weeks_to_retain_stats_data>"
echo
exit 1
fi
current_date=`date +%D`
seconds_since_epoch=`date -d "$current_date" +%s`
oldest_time_collected_to_retain=`expr \( $seconds_since_epoch \- \( $1 \* 604800 \) \) \* 1000`
declare -a table_arr=("adaptorHBAVnicStats" "adaptorNICVnicStats" "adaptorVnicStats" "computeMbPowerStats" "computeMbTempStats" "computeRackUnitMbTempStats" "equipmentChassisStats" "equipmentFanStats" "equipmentNetworkElementFanStats" "equipmentPsuStats" "equipmentRackUnitFanStats" "equipmentRackUnitPsuStats" "etherRxStats" "etherTxStats" "fcStats" "processorEnvStats")
echo
echo "Run the following SQL queries in your database to delete old statistics data prior to $1 weeks:"
echo
for table_name in ${table_arr[@]}
do
echo "delete from \"$table_name\" where \"timeCollected\" < $oldest_time_collected_to_retain;"
done
Sample Output would look like this:
[root@max downloads]# ./generate_stats_trim_sql.sh 5
*******************************************************************************************************
UCS Central - Purge old Statistics Data
This script will generate the required SQL scripts that you can run in your database to purge old data
*******************************************************************************************************
Run the following SQL queries in your database to delete old statistics data prior to 5 weeks:
delete from "adaptorHBAVnicStats" where "timeCollected" < 1389945600000;
delete from "adaptorNICVnicStats" where "timeCollected" < 1389945600000;
delete from "adaptorVnicStats" where "timeCollected" < 1389945600000;
delete from "computeMbPowerStats" where "timeCollected" < 1389945600000;
delete from "computeMbTempStats" where "timeCollected" < 1389945600000;
delete from "computeRackUnitMbTempStats" where "timeCollected" < 1389945600000;
delete from "equipmentChassisStats" where "timeCollected" < 1389945600000;
delete from "equipmentFanStats" where "timeCollected" < 1389945600000;
delete from "equipmentNetworkElementFanStats" where "timeCollected" < 1389945600000;
delete from "equipmentPsuStats" where "timeCollected" < 1389945600000;
delete from "equipmentRackUnitFanStats" where "timeCollected" < 1389945600000;
delete from "equipmentRackUnitPsuStats" where "timeCollected" < 1389945600000;
delete from "etherRxStats" where "timeCollected" < 1389945600000;
delete from "etherTxStats" where "timeCollected" < 1389945600000;
delete from "fcStats" where "timeCollected" < 1389945600000;
delete from "processorEnvStats" where "timeCollected" < 1389945600000;
[root@max downloads]#
[root@max downloads]#n