====== TSM drive usage riport ======

Lekérdezi 24 órás, óránkénti bontásokban a TSM szerver teljes drive használatát, library-kre bontva, és felrajzolja egy ASCII hisztogramon. (Linux/AIX kompitibilis, bash és perl kell a futáshoz.)

{{:shell:riportok:drive_usage.png?600|}}

<file sh tsm_drive_usage.sh>
#!/bin/bash
#########################################################
# TSM drive hasznalat riportolo szkript  v1.0           #
# Keszitette: Agoston Peter, tsm.agostonpeter.com       #
#########################################################
FILLER="#"
TMP_FILE=/tmp/dr_usage_matrix.tmp
TSMUSER=report
TSMPASS=report
TSMSERVER=tsm
####### Innen csak saját felelősségre módosíts #######
declare -A MATRIX
TODAY=$(date "+%Y-%m-%d")
printf "Milyen datumtol inditsam a lekerdezest? (Alapertelmezett: $TODAY) "; read DATE_TO;
DATE_TO="${DATE_TO:-$TODAY} 00:00:00.000000"
printf "Hany napra vissza? (Alapertelmezett: 1) "; read DAYS;
DAYS=${DAYS:-1}
LIBRARIES=$(dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y -commadel "select distinct library_name from drives where online='YES'")

bold=$(tput bold)                         # make colors bold/bright
#normal=$(tput sgr0)                      # normal text
normal=$'\e[0m'                           # (works better sometimes)

red="$bold$(tput setaf 1)"                # bright red text
green=$(tput setaf 2)                     # dim green text
fawn=$(tput setaf 3); beige="$fawn"       # dark yellow text
yellow="$bold$fawn"                       # bright yellow text
darkblue=$(tput setaf 4)                  # dim blue text
blue="$bold$darkblue"                     # bright blue text
purple=$(tput setaf 5); magenta="$purple" # magenta text
pink="$bold$purple"                       # bright magenta text
darkcyan=$(tput setaf 6)                  # dim cyan text
cyan="$bold$darkcyan"                     # bright cyan text
gray=$(tput setaf 7)                      # dim white text
darkgray="$bold"$(tput setaf 0)           # bold black = dark gray text
white="$bold$gray"                        # bright white text

while [[ "$DAYS" -gt 0 ]]; do
 DAYS=$(($DAYS-1))
 for LIBRARY in $LIBRARIES; do

NUM_OF_DRIVES=$(dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y -commadel "select count(*) from drives where online='YES' and library_name='${LIBRARY}'")
# Minden library-re kulon lefuttatjuk
SQL="select ORA, cast(round(sum(USAGE) / (select count(*) from drives where online='YES' and library_name='${LIBRARY}')) as dec(4,0)) from ( \
select concat(date(start_time) || ' ',substr(time(start_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)=substr(start_time,1,13) THEN timestampdiff(4,end_time-start_time) WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,timestamp(concat(date(start_time) || ' ', substr(time(start_time),1,2) || ':59:59.000000'))-start_time) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and library_name='${LIBRARY}' and start_time<timestamp('${DATE_TO}') - $((10#$DAYS-1)) days and start_time>timestamp('${DATE_TO}') - ${DAYS} days group by concat(date(start_time) || ' ',substr(time(start_time),1,2)) \
UNION ALL \
select concat(date(end_time) || ' ',substr(time(end_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,end_time-timestamp(concat(date(end_time) || ' ', substr(time(end_time),1,2) || ':00:00.000000'))) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and library_name='${LIBRARY}' and end_time<timestamp('${DATE_TO}') - $((10#$DAYS-1)) days and end_time>timestamp('${DATE_TO}') - ${DAYS} days group by concat(date(end_time) || ' ',substr(time(end_time),1,2)) \
) group by ORA ORDER BY ORA"
SQL="select ORA, cast(round(sum(USAGE) / (select count(*) from drives where online='YES' and library_name='${LIBRARY}')) as dec(4,0)) from (
select concat(date(start_time) || ' ',substr(time(start_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)=substr(start_time,1,13) THEN timestampdiff(4,end_time-start_time) WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,timestamp(concat(date(start_time) || ' ', substr(time(start_time),1,2) || ':59:59.000000'))-start_time) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and library_name='${LIBRARY}' and start_time<timestamp('${DATE_TO}') - $((10#$DAYS-1)) days and start_time>timestamp('${DATE_TO}') - ${DAYS} days group by concat(date(start_time) || ' ',substr(time(start_time),1,2))
UNION ALL
select concat(date(end_time) || ' ',substr(time(end_time),1,2)) as ORA, sum( cast(CASE WHEN substr(end_time,1,13)>substr(start_time,1,13) THEN timestampdiff(4,end_time-timestamp(concat(date(end_time) || ' ', substr(time(end_time),1,2) || ':00:00.000000'))) END as dec(5,2))) /60*100 as USAGE from summary where ACTIVITY='TAPE MOUNT' and library_name='${LIBRARY}' and end_time<timestamp('${DATE_TO}') - $((10#$DAYS-1)) days and end_time>timestamp('${DATE_TO}') - ${DAYS} days group by concat(date(end_time) || ' ',substr(time(end_time),1,2))
) group by ORA ORDER BY ORA"
SQL2="select concat(date(start_time+1 hours) || ' ',substr(time(start_time+1 hours),1,2)) as ORA, cast(CASE WHEN substr(end_time,1,13)>substr(start_time+1 hours,1,13) THEN timestampdiff(8,timestamp(concat(date(end_time) || ' ',substr(time(end_time),1,2)) || ':00:00.000000')-timestamp(concat(date(start_time+1 hours) || ' ',substr(time(start_time+1 hours),1,2)) || ':00:00.000000')) END as dec(5,0)) *60  as USAGE from summary where ACTIVITY='TAPE MOUNT' and library_name='${LIBRARY}' and start_time>timestamp('${DATE_TO}') - $((10#$DAYS+1)) days and start_time<timestamp('${DATE_TO}') - $((10#$DAYS-1)) days ORDER BY start_time"
SQL_DRIVE_USAGE="SELECT decimal(SUM(float(hour(end_time - start_time)*60+minute(end_time - start_time)))/1440*100/count(DISTINCT substr(drive_name,1,posstr(drive_name,' '))),5,1) as \"24H LOAD (%)\" FROM summary WHERE activity='TAPE MOUNT' AND date(current_timestamp) - 1 days < start_time and end_time<date(current_timestamp) and library_name='${LIBRARY}' GROUP BY library_name"

NR=0
rm -rf $TMP_FILE $TMP_FILE.2
# Matrix inicializalasa
for J in {1..24}; do
    for I in {1..24}; do
        MATRIX[$I,$J]=" "
    done
    printf "%02d, 0\n"  "$((10#$J-1))" >> $TMP_FILE
    printf "%02d, 0\n"  "$((10#$J-1))" >> $TMP_FILE.2
done
AKT_DATUM=$(dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y "select date(timestamp('${DATE_TO}') - ${DAYS} days) from sysibm.sysdummy1" | sed 's/ //g')

# Lekerdezem az adott oranal regebben kezdodott es kesobb vegzodott hasznalatot
# Az SQL a jelolt oratol kezdve annyi oran at tarto mountot ad vissza, ahanyszor az ertekben a 60 megvan.
# Ennek alapjan adok hozza ertekeket az adott naphoz
dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=y -commadel "${SQL2}" | grep "$AKT_DATUM" | sed -e 's/^[^ ]* //' | grep ',[0-9]*$' | while read SOR; do
    HOUR=$(echo $SOR | awk -F',' '{print $1}')
    VALUE=$(echo $SOR | awk -F',' '{print $2}')
    OLD_VALUE=$(grep "^$HOUR," $TMP_FILE.2 | awk -F',' '{print $2}')
    while [[ "$VALUE" -gt 0 ]]; do
        USAGE=$(echo "scale=2; (1/${NUM_OF_DRIVES}*100) + $OLD_VALUE" | bc | sed 's/\..*$//')
        perl -pi -e  "s/^$HOUR,.*/$HOUR, $USAGE/" $TMP_FILE.2
        HOUR=$((10#$HOUR+1))
        [[ $HOUR -lt 10 ]] && HOUR="0$HOUR"
        VALUE=$((10#$VALUE-60))
    done
done

# Az ures orakkal valo kiegeszitese az adatfajlnak
dsmadmc -id="$TSMUSER" -pa="$TSMPASS" -se="$TSMSERVER" -dataonly=y -commadel "$SQL" | sed 's/^[^ ]* //' | while read SOR; do
    HOUR=$(echo $SOR | awk -F',' '{print $1}')
    VALUE=$(echo $SOR | awk -F',' '{print $2}')
    perl -pi -e  "s/^$HOUR,.*/$HOUR, $VALUE/" $TMP_FILE
done
# A diagram matrix feltoltese
while read SOR; do
    NR=$((10#$NR+1))
    HOUR=$(echo $SOR | awk -F',' '{print $1}')
    VALUE=$(echo $SOR | awk -F',' '{print $2}')
    COL=$(($NR*2))
    MATRIX[1,$NR]=$HOUR
    for I in {2..11}; do
        # 10-re kerekites varazslas
        if [[ $(echo "scale=2; $VALUE/10" | bc | awk '{printf("%d\n",$1 + 0.5)}') -le $(((10#$I-2))) ]]; then
            # Az 50%-os segedmutato
            [[ $I -eq 7 || $I -eq 2 ]] && MATRIX[$I,$NR]="_" ||  MATRIX[$I,$NR]=" "
        else
            MATRIX[$I,$NR]="$FILLER"
        fi
    done
    MATRIX[$((10#$I+1)),$NR]="_"
done <<< "`paste $TMP_FILE $TMP_FILE.2 | awk '{print $1, ($2 + $4)}'`"

# Diagram matrix kiirasa a \ tengelyre tukrozve, majd - tengelyre tukrozve
echo $AKT_DATUM | sed  -e :a -e 's/^.\{1,'$((10#$NR*3))'\}$/ & /;ta'
for ((J=1;J<=NR;J++)) do
    for ((I=1;I<=NR;I++)) do
        if [[ ${MATRIX[$J,$I]} =~ [0-9] ]]; then
            printf "$bold%3s$normal" ${MATRIX[$J,$I]}
        elif [[ ${MATRIX[$J,$I]} =~ \# ]]; then
            printf "$bold%3s$normal" ${MATRIX[$J,$I]}${MATRIX[$J,$I]}
        elif [[ ${MATRIX[$J,$I]} =~ _ ]]; then
            printf "$darkgray%3s$normal" ${MATRIX[$J,$I]}${MATRIX[$J,$I]}
        else
            printf "%3s" ${MATRIX[$J,$I]}${MATRIX[$J,$I]}
        fi
    done
    [[ $J -eq 7 || $J -eq 2 || $J -eq 12 ]] && printf " _%3s\n" "$((($J-2)*10))%" || echo
done | sed '1!G;h;$!d' | tail -n 13

TOTAL_DRV_USAGE=$(dsmadmc -id=$TSMUSER -pa=$TSMPASS -se=$TSMSERVER -dataonly=yes -commadel "$SQL_DRIVE_USAGE" | egrep -v '(ANR|ANS)')
echo "${LIBRARY} (Teljes kihasznaltsag: ${TOTAL_DRV_USAGE}%)" | sed  -e :a -e 's/^.\{1,'$((10#$NR*3))'\}$/ & /;ta'
echo

 done
done
</file>