Select statement to get the average total data backed up by each node for a full week

[shanafin]

I'm trying to get the average backup size for each node over an entire week. The goal is to create a list that will show every node that backs up an average of 50 GB or more each night. Does anyone know the select statement that will do this?

I used this one to get a one day outlook...

select node_name as "NODE", CAST ((lastsess_recvd/1024/1024/1024) as decimal(14,2))as "GB_RECEIEVED" from nodes order by GB_RECEIEVED desc

Thanks,
Steve

 

[KB]

Have you tried a query against the activity summary table?

The activity summary table will have the results of the backup jobs. If you set the retention on the activity summary to be equal or greater than a week you can get the information you need. I would use an ODBC query and something like Excel's pivot tables to calculate the averages per node.

Ken

 

[mateinone]

Gday mate,

if on AIX try the following see if it is what you want...
It will create a list, sorted by the servers with the highest average backup per day that they are backing up.
I wrote it for a multi server environment so you need to give the servername as an argument.

So run with the following command
# weekly.ksh tsmserver

#############weekly.ksh#############
#!/usr/bin/ksh
clear

usage()
{
echo "\n\tError......"
echo "\tUsage: `basename $0` [TSMSERVER] "
echo "\t\t"
echo ""
exit 1
}

TSM="$1"
DSMSYS="/usr/tivoli/tsm/client/ba/bin/dsm.sys"

if [[ ! $# -eq 1 ]]
then
usage
elif [[ ! -f $DSMSYS || ! `grep -i $1 $DSMSYS|wc -l` -ge 1 ]]
then
echo "There is no $DSMSYS file or \nthe entry $TSM is not in the $DSMSYS file"
echo "exiting...."
return 1;
fi

TSM_PSWD=/your/passfile/location
TSM_CMD=dsmadmc
read ID PASSWORD < ${TSM_PSWD}
tsmCMD="${TSM_CMD} -id=${ID} -pa=${PASSWORD} -se=$TSM -dataonly=yes -comma"

NODES=`$tsmCMD "select node_name from nodes where (node_name in select entity from summary where start_time>=current_timestamp -7 days and activity='BACKUP'))"`
for nodes in $NODES
do
BYTES=`$tsmCMD "select sum(bytes) from summary where entity='${nodes}' and start_time>=current_timestamp - 168 hours and successful='YES'"`
DAYS=`$tsmCMD "select cast(start_time as date) from summary where entity='${nodes}' and start_time>=current_timestamp - 168 hours and successful='YES'"|sort -u |wc -l`
echo $nodes $BYTES $DAYS|awk '{printf "%-20s%.3f\n", 1,$2/1024/1024/1024/$3}'
done |sort +1 -n -r

#############END###################

 

[max]

Ugh

hey guys ... why so complicated ???

try this ....

SELECT entity AS node, CAST(AVG(bytes/1024/1024/1024) AS DECIMAL(8,2)) AS "GB" FROM SUMMARY WHERE activity='BACKUP' AND examined<>0 AND DAYS(current_timestamp)-DAYS(start_time) < 8 GROUP BY entity

that should be fine for you

cheers
max

 

[mateinone]

hey guys ... why so complicated ???

try this ....

SELECT entity AS node, CAST(AVG(bytes/1024/1024/1024) AS DECIMAL(8,2)) AS "GB" FROM SUMMARY WHERE activity='BACKUP' AND examined<>0 AND DAYS(current_timestamp)-DAYS(start_time) < 8 GROUP BY entity

that should be fine for you

cheers
max

yeap, I was using date, not day parameter, which is what happens when i do crap like this at 3 in the morning, this select still needs tuning, because it is not picking up anything other than system nodes, so ORACLE nodes are not being picked up for example

 

[shanafin]

Thanks Guys. That did the trick.

Steve