UCS Central - Purging Old Statistics

[Jeffrey Silberman]

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