===== TSM HTML report using ksh =====

Environment variables
<cli prompt='#'>
root@tsmprod - /root/scripts > cat .env
# global variables for all scripts
export binpath=/root/scripts
export sn=`basename $0 | cut -d. -f1`
export HOSTNAME=`/usr/bin/hostname`
export logpath=/root/scripts/logs
export logname=$logpath/$sn.log
export configfile=$binpath/$sn.cfg
</cli>

Script that generates an HTML report and send using scp to a web server:
<cli prompt='#'>
root@tsmprod - /root/scripts # cat /root/scripts/tsm_daily_report.sh
</cli>
<code>
#!/bin/ksh93
#set -x
#====================================================================
#@(#) Script to generate a HTML report on TSM activity
#       format of line before HMTL :
#          html_tag;nagios_tag;reserved;output_from_select_TSM
#====================================================================
# version 1.0

dir=`dirname $0`
. $dir/.env

# Needs rpm package coreutil
DATE_BIN=/opt/freeware/bin/date
YESTERDAY=$($DATE_BIN +%Y-%m-%d -d "1 day ago")
TODAY=$($DATE_BIN +%Y-%m-%d)

DSMADMC="/usr/tivoli/tsm/client/ba/bin64/dsmadmc -id=admin -pa=password"
OPT_COMMA="-comma"
OPT_TAB="-tab"
OPT_DATAONLY="-dataonly=yes"
#NAGIOSTAG="0=OK, 1=WARNING, 2=CRITICAL, 3=UNKNOWN"
TMPFILE=/tmp/tsm_daily_result
COLOR[0]="#57E964"      # green
COLOR[1]="#F87217"      # orange
COLOR[2]="#FF2400"      # red
COLOR[3]="yellow"       # yellow
COLOR[4]="#56A5EC"      # blue
COLOR[5]="#FFFFFF"      # white
#FFA500 : orange
TAG=TAG    # reserved for future
loghtml=$logpath/tsm_daily_report.html
confluhost="root@192.168.211.147"
conflupath="/var/www/html_doc"

backup_vm ()
{
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select TO_CHAR(CHAR(START_TIME),'YYYY-MM-DD HH24:MI:SS') as START_TIME,TO_CHAR(CHAR(END_TIME),'YYYY-MM-DD HH24:MI:SS') as END_TIME,SUB_ENTITY as NODE_NAME,SCHEDULE_NAME,SUCCESSFUL,TRANSLATE('a bc:de:fg', DIGITS(END_TIME-START_TIME), '_______abcdefgh_____',' ') as ELAPTIME,cast(float(bytes)/1024/1024/1024 as dec(8,2)) as GB from summary_extended where entity like 'VM_%' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE
echo "TH;4;TAG;START_TIME;END_TIME;NODE_NAME;SCHEDULE_NAME;SUCCESSFUL;ELAPTIME;GB" > $TMPFILE.backup_vm

cat $TMPFILE | while read line
do
  status=$(echo $line | cut -d';' -f5)
  if [ "$status" == "YES" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=2
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.backup_vm
done


}



read_events ()
{
# Suppress REASON and add ELAPTIME : SCHEDULED_START;ACTUAL_START;DOMAIN_NAME;SCHEDULE_NAME;NODE_NAME;STATUS;RESULT;REASON;COMPLETED;ELAPTIME
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select TO_CHAR(CHAR(SCHEDULED_START),'YYYY-MM-DD HH24:MI:SS') as SCHEDULED_START,TO_CHAR(CHAR(ACTUAL_START),'YYYY-MM-DD HH24:MI:SS') as ACTUAL_START,DOMAIN_NAME,SCHEDULE_NAME,NODE_NAME,STATUS,RESULT,TO_CHAR(CHAR(COMPLETED),'YYYY-MM-DD HH24:MI:SS') as COMPLETED,TRANSLATE('a bc:de:fg', DIGITS(COMPLETED-ACTUAL_START), '_______abcdefgh_____',' ') as ELAPTIME from events where SCHEDULED_START>=current_timestamp-24 hours" | egrep "$YESTERDAY|$TODAY" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE
#echo "Client Schedules"
echo "TH;4;TAG;SCHEDULED_START;ACTUAL_START;DOMAIN_NAME;SCHEDULE_NAME;NODE_NAME;STATUS;RESULT;COMPLETED;ELAPTIME" > $TMPFILE.client
#echo "Admin Schedules"
echo "TH;4;TAG;SCHEDULED_START;ACTUAL_START;SCHEDULE_NAME;STATUS;RESULT;COMPLETED;ELAPTIME" > $TMPFILE.admin

cat $TMPFILE | while read line
do
  status=$(echo $line | cut -d';' -f6)
  if [ "$status" != "Future" ]
  then
    DOMAIN_NAME=$(echo $line | cut -d';' -f3)
    if [ "$DOMAIN_NAME" = "" ]
    then
      # Admin schedule
      if [ "$(echo $line | cut -d';' -f6-7)" = "Completed;0" ]
      then
        NAGIOSTAG=0
      else
        if [ "$status" = "Completed" ]
        then
          NAGIOSTAG=1
        else
          if [[ "$status" = "Started" || "$status" = 'In|Progress'  ]]
          then
            NAGIOSTAG=3
          else
            NAGIOSTAG=2
          fi
        fi
      fi
      line1=$(echo $line | cut -d';' -f1-2,4,6-)
      echo "TD;$NAGIOSTAG;$TAG;$line1" >> $TMPFILE.admin.$NAGIOSTAG
    else
      # Client schedule
      if [ "$(echo $line | cut -d';' -f6-7)" = "Completed;0" ]
      then
        NAGIOSTAG=0
      else
        if [ "$status" = "Completed" ]
        then
          NAGIOSTAG=1
        else
          if [[ "$status" = "Started" ]] || [[ "$status" = 'In|Progress' ]]
          then
            NAGIOSTAG=3
          else
            NAGIOSTAG=2
          fi
        fi
      fi
      echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.client.$NAGIOSTAG
    fi
  fi
done

$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT activity, cast(float(sum(bytes))/1024/1024/1024 as dec(8,2)) as GB FROM summary WHERE activity<>'TAPE MOUNT' AND activity<>'EXPIRATION' AND start_time >current_timestamp-24 hours  GROUP BY activity" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.activitytotal
echo "TH;4;TAG;BACKUP;ARCHIVE;RESTORE;RETRIEVE;FULL_DBBACKUP;STGPOOL|BACKUP;DELETE|FILESPACE;MIGRATION;RECLAMATION;SW|MIGRATION" > $TMPFILE.total
value1=""
for act in $(head -1 $TMPFILE.total | cut -d';' -f4- | sed 's/;/\ /g')
do
  value=$(grep "^$act;" $TMPFILE.activitytotal | cut -d';' -f2)
  value1="$value1;$value"
done
echo "TD;5;TAG$value1" >> $TMPFILE.total




$DSMADMC $OPT_COMMA $OPT_DATAONLY "select SEVERITY,TO_CHAR(CHAR(DATE_TIME),'YYYY-MM-DD HH24:MI:SS') as DATE,ORIGINATOR,NODENAME,SCHEDNAME,DOMAINNAME,MESSAGE from actlog where ( SEVERITY='W' or SEVERITY='E' or SEVERITY='D' ) and date_time>=current_timestamp-24 hours" | sed 's/\ /\|/g' | sort > $TMPFILE.actlog
echo "TH;4;TAG;DATE_TIME;ORIGINATOR;NODENAME;SCHEDNAME;DOMAINNAME;MESSAGE ID;MESSAGE" > $TMPFILE.error


cat $TMPFILE.actlog | egrep -v "ANR2034E|ANR1701E|ANR2111W|ANR3247W" | sed 's/^W/3;TAG/g' |  sed 's/^E/2;TAG/g' | sed 's/^D/1;TAG/g' | sed 's/^/TD;/g' | sed 's/,/;/1' | sed 's/,/;/1' | sed 's/,/;/1' | sed 's/,/;/1' | sed 's/,/;/1' | sed 's/,/;/1' | sed 's/\|/;/2' >> $TMPFILE.error
}

check_library ()
{
COUNT='count(*)'
echo "TH;4;TAG;Library_name;scratch" > $TMPFILE.scratch
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select library_name,$COUNT as Scratch  from libvolumes WHERE status='Scratch' group by library_name" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.scratch1
echo "TH;4;TAG;DESTINATION_NAME;DESTINATION_TYPE;ONLINE" > $TMPFILE.paths
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from paths" | cut -d',' -f3,4,14 | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.paths1
echo "TH;4;TAG;LIBRARY_NAME;DRIVE_NAME;DEVICE_TYPE;ONLINE" > $TMPFILE.drives
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from drives" | cut -d',' -f1,2,3,4 | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.drives1

NAGIOSTAG=0
TAG=TAG
for line in $(cat $TMPFILE.scratch1)
do
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.scratch
done
rm $TMPFILE.scratch1

for line in $(cat $TMPFILE.paths1)
do
  if [ $(echo $line | grep YES > /dev/null;echo $?) -eq 0 ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=1
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.paths
done
rm $TMPFILE.paths1

for line in $(cat $TMPFILE.drives1)
do
  if [ $(echo $line | grep YES > /dev/null;echo $?) -eq 0 ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=1
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.drives
done
rm $TMPFILE.drives1

}

check_volume_error ()
{
echo "TH;4;TAG;VOLUME_NAME;ACCESS;WRITE_ERRORS;READ_ERRORS" > $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,ACCESS,WRITE_ERRORS,READ_ERRORS from volumes where ACCESS<>'READWRITE'" | sed 's/,/;/g' | sed 's/\ /\|/g' | egrep -iv "found|return"| sed "s/^/TD;2;$TAG;/" >> $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,ACCESS,WRITE_ERRORS,READ_ERRORS from volumes where WRITE_ERRORS<>'0' or READ_ERRORS<>'0'" | sed 's/,/;/g' | sed 's/\ /\|/g' | egrep -iv "found|return" | sed "s/^/TD;1;$TAG;/" >> $TMPFILE.volume_error
}


auditocc ()
{
NAGIOSTAG=0
TAG=TAG
echo "TH;4;TAG;NODE_NAME;BACKUP_MB;BACKUP_COPY_MB;ARCHIVE_MB;ARCHIVE_COPY_MB;TOTAL_MB;LASTACC_DATE;DOMAIN_NAME" > $TMPFILE.auditocc

$DSMADMC $OPT_COMMA $OPT_DATAONLY "select n.NODE_NAME,a.BACKUP_MB,a.BACKUP_COPY_MB,a.ARCHIVE_MB,a.ARCHIVE_COPY_MB,a.TOTAL_MB,DATE(n.lastacc_time) as LASTACC_DATE,n.DOMAIN_NAME from  nodes n, auditocc a WHERE n.node_name=a.node_name order by TOTAL_MB desc" | sed 's/,/;/g' | sed 's/\ /\|/g' | sed "s/^/TD;$NAGIOSTAG;$TAG;/" >> $TMPFILE.auditocc
}

daily_backuparch_per_node ()
{
NAGIOSTAG=0
TAG=TAG
HEADER="TH;4;TAG;DOMAIN_NAME;NODE_NAME"

for days in 0 1 2 3
do
(( BEGIN = 24 * days ))
(( END = (24 * days) + 24 ))
HEADER=$(echo ${HEADER}';'DAY-${days}'(GB)')


$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT no.domain_name,su.entity,cast(float(sum(su.bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as GB FROM summary su, nodes no where su.entity=no.node_name and ( su.activity='ARCHIVE' OR su.activity='BACKUP' ) AND su.end_time>current_timestamp-$END hours and end_time<current_timestamp-$BEGIN hours GROUP BY su.entity,no.domain_name"  | sed 's/,/;/g' | sed 's/\ /\|/g' >> $TMPFILE.daily_backup-arch_per_node.$days


if [ ! -f $TMPFILE.daily_backup-arch_per_node ]
then
  cp $TMPFILE.daily_backup-arch_per_node.$days $TMPFILE.daily_backup-arch_per_node
else
  sed -e 's/$/;/' $TMPFILE.daily_backup-arch_per_node > $TMPFILE.daily_backup-arch_per_node.tmp
  mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.$days)
  do
    node=$(echo $line | cut -d';' -f2)
    value=$(echo $line | cut -d';' -f3)
    sed -e "/$node;/ s/$/$value/" $TMPFILE.daily_backup-arch_per_node > $TMPFILE.daily_backup-arch_per_node.tmp
    mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node
  done
fi
done
mv $TMPFILE.daily_backup-arch_per_node $TMPFILE.daily_backup-arch_per_node.tmp
echo $HEADER > $TMPFILE.daily_backup-arch_per_node
cat $TMPFILE.daily_backup-arch_per_node.tmp | sort | sed "s/^/TD;$NAGIOSTAG;$TAG;/" >> $TMPFILE.daily_backup-arch_per_node
rm $TMPFILE.daily_backup-arch_per_node.*

}

admin_shedule_status ()
{
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select START_TIME,END_TIME,TRANSLATE('a bc:de:fg', DIGITS(end_time-start_time), '_______abcdefgh_____',' ') as ELAPTIME,ENTITY,SUCCESSFUL from summary where ACTIVITY='PROCESS_END' and START_TIME>=current_timestamp-24 hours" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.sum_admin.tmp


echo "TH;4;TAG;SCHEDULED_START;SCHEDULED_STOP;DURATION;ADMIN_TASK;STATUS" > $TMPFILE.sum_admin

cat $TMPFILE.sum_admin.tmp | egrep "^$YESTERDAY|^$TODAY" | sort | while read line
do
  status=$(echo $line | cut -d';' -f5)
  if [ "$status" == "YES" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=2
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.sum_admin
done

rm $TMPFILE.sum_admin.tmp

}


convert_html ()
{
#<TABLE BORDER="1" style="background:#FFFF00">
#  <CAPTION> Voici le titre du tableau </CAPTION>
#  <TR style="background:#80BFFF"><TH> Titre A1 </TH><TH> Titre A2 </TH><TH> Titre A3 </TH><TH> Titre A4 </TH></TR>
#  <TR style="background:#00FF00"><TH> Titre B1 </TH><TD> Valeur B2 </TD><TD> Valeur B3 </TD><TD> Valeur B4 </TD></TR>
#  <TR><TH> Titre B1 </TH><TD> Valeur B2 </TD><TD> Valeur B3 </TD><TD> Valeur B4 </TD></TR>
#</TABLE>

echo '<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<h1>TSM Report '$(date "+%d-%m-%Y %H:%M")'</h1>
<body>
<br>
<h2>TSM events</h2>
<br>
<table border="1">
<tbody>'

for i in admin volume_error scratch paths drives client backup_vm error total sum_admin auditocc daily_backup-arch_per_node
do
  echo '<h3>TSM '$(echo $i)'</h3><br>'

  cat $TMPFILE.$i* > $TMPFILE.tempo
  for nb in 0 1 2 3 4 5
  do
    for begin in TH TD
    do
      cat $TMPFILE.tempo | sed "s/^$begin;$nb;TAG;/\<TR\ style=\"background:${COLOR[$nb]}\"><TD>/g" > $TMPFILE.tempo1
      mv $TMPFILE.tempo1 $TMPFILE.tempo
    done
  done
  cat $TMPFILE.tempo | sed 's/;/<\/TD><TD>/g' | sed 's/\|/\ /g' | sed 's/$/<\/TD><\/TR>/g'

  echo '</tbody>
</table>
<table border="1">
<tbody>
<BR><BR>'
done


echo '
</body>
</html>'
}

copy_to_confluence ()
{
if [ -s $tmpfile ]
then
  scp $loghtml $confluhost:$conflupath
fi
}

main ()
{
date
rm $TMPFILE*
backup_vm
read_events
check_library
check_volume_error
auditocc
daily_backuparch_per_node
admin_shedule_status
convert_html 1> $loghtml
copy_to_confluence
#rm $TMPFILE*
date
}

main > $logname 2>&1

</code>