====== Reporting script for TSM 7.1 ======

<code>
#----------------------------
backup_vm ()
{
echo "# Collect backup_vm"
#ACTIVITY_DETAILS: VMware

$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,ENTITY from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE

echo "TH;4;TAG;START_TIME;END_TIME;NODE_NAME;SCHEDULE_NAME;SUCCESSFUL;ELAPTIME;GB;DM_NAME" > $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

#cp $TMPFILE.backup_vm /root/backup_vm.txt
}

#----------------------------
error_vm_backup ()
{
echo "# Collect Error in VM backup"
# Check VM backups, filespace name like \VMFULL

echo "TH;4;TAG;DATACENTER;VM;LAST_BACKUP;DAYS_SINCE_LAST_BACKUP" > $TMPFILE.failed_vm
#$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS,cast(date(current_timestamp)-date(backup_end) as integer) as "Days" from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

CURDATE=$($DATE_BIN +%Y-%m-%d)

cat $TMPFILE | while read line
do
  LASTACC=$(echo $line | cut -d';' -f3)
  nbdays=`echo $(( ($($DATE_BIN -d $CURDATE +%s) - $($DATE_BIN -d $LASTACC +%s) )/(60*60*24) ))`
  if [ "$nbdays" -le "${alert_vm_backup[0]}" ]
  then
    NAGIOSTAG=0
  else
    if [ "$nbdays" -le "${alert_vm_backup[1]}" ]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=2
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$nbdays" >> $TMPFILE.failed_vm
done
}

</code>

This is a bash script running on AIX and Linux.
It daily report a tabular view on 
  - DB / Logs status
  - admin schedules 
  - volumes error
  - scratch tapes
  - library path and drives
  - client schedules
  - TDP fo VE VM backup status
  - TSM errors and warnings
  - Total daily backup volumes
  - detailled admin tasks
  - clients occupancy sort by bigger
  - daily backup/archive per node

==== Improvements ====

You can encrypt/decrypt the TSM USR's password

Decrypt
<code>
PASS=ThisIsTheKey12
PASSWD=$(openssl enc -a -d -aes-256-cbc -in /etc/tsm/file.pwd -pass pass:$PASS)
if [ $? -ne 0 ]; then
        echo "Wrong password"
        exit 1
fi
DSMADMC="/usr/bin/dsmadmc -se=$TSMSRV -id=$USR -password=$PASSWD -noconfirm"
</code>

Encrypt
<code>
# openssl enc -a -aes-256-cbc -in /tmp/mypasswd -out /etc/tsm/file.pwd
enter aes-256-cbc encryption password: ThisIsTheKey12
Verifying - enter aes-256-cbc encryption password: ThisIsTheKey12
</code>

===== Script =====

Depending on the status of the result, you 'll have different colors green (OK), orange (WARNING), red (CRITICAL)
<cli prompt='>'>
root@tsmsrv - /root/scripts > cat .env
</cli>

<code>
# global variables for all scripts
export binpath=/root/scripts
export sn=`basename $0 | cut -d. -f1`
export HOSTNAME=`hostname -s`
export logpath=/root/scripts/logs
export logname=$logpath/$sn.log
export configfile=$binpath/$sn.cfg
export destination="myemail@google.com"
export mail_undeliverable="myemail@google.com"
</code>

<cli prompt='>'>
root@tsmsrv - /root/scripts > cat tsm_daily_report.cfg
</cli>

<code>
export tsm_admin=admin
export tsm_password=passwd
export tsmservers="TSMS74 TSMC75"
export tsmemailsender="${HOSTNAME}.root@google.com"
export tsmemailreceiver="tsmadmin@google.com,tsmadmin1@google.com"
export node_expired="NODE1,NODE2"                # excluded nodes and filespace from expired report
export expired=30 # number of days before node or filespace is consider as obsolete
# Alerting threshold var=(warning critical)
alert_db_disk=(80 90)
alert_log_fs=(80 90)
alert_dispace_fs=(90 95)
alert_stg_disk=(80 90)
alert_stg_maxscr=(80 90)
alert_libr_maxscr=(5 3)
alert_vm_backup=(2 4)              # nb days for VM backup
# Activity log statistics (reduce report size)
activity_stat=yes
filespace_stat=yes
</code>

<cli prompt='>'>
root@tsmsrv - /root/scripts > cat tsm_daily_report.sh
</cli>

<code>
#!/bin/bash
#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
# version 1.1 report send by mail
# version 1.2 suppress unused informations
# version 1.3 add statistics on 5 latest daily backups, and dedup stats
# version 1.4 add expired nodes and filespaces
# version 1.5 check node replication
# version 1.6 centralize alert threshold in cfg file (08/2016)
# version 1.7 add container space (09/2019)
# version 1.8 add check VM filespace (12/2019)

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

os=$(uname -s | tr 'A-Z' 'a-z')

if [[ $os == "aix" ]]
then
  # Needs rpm package coreutil
  DATE_BIN=/opt/freeware/bin/date
  if [ ! -x /opt/freeware/bin/date ]
  then
    echo "Install bash and coreutil packages rpm or bff"
    exit 1
  fi
  DSMADMC1="/usr/tivoli/tsm/client/ba/bin64/dsmadmc -id=${tsm_admin} -pa=${tsm_password}"
  HOSTNAME=$(/usr/bin/hostname -s)
else
  if [[ $os == "linux" ]]
  then
    DATE_BIN=/bin/date
    DSMADMC1="/opt/tivoli/tsm/client/ba/bin/dsmadmc -id=${tsm_admin} -pa=${tsm_password}"
    HOSTNAME=$(/bin/hostname -s)
  else
    echo "ERROR: Unknown OS"
    exit 1
  fi
fi

YESTERDAY=$($DATE_BIN +%Y-%m-%d -d "1 day ago")
TODAY=$($DATE_BIN +%Y-%m-%d)
EXPIRED=$($DATE_BIN +%Y-%m-%d -d "$expired day ago")

OPT_COMMA="-comma"
OPT_TAB="-tab"
OPT_DATAONLY="-dataonly=yes"

tmplog=$logpath/tmp
TMPFILE=$tmplog/tsm_daily_result

#NAGIOSTAG="0=OK, 1=WARNING, 2=CRITICAL, 3=UNKNOWN"
COLOR[0]="#57E964"      # green
COLOR[1]="#F87217"      # orange #F7BE81
COLOR[2]="#FF2400"      # red    #DF013A
COLOR[3]="yellow"       # yellow #F3F781
COLOR[4]="#56A5EC"      # blue
COLOR[5]="#FFFFFF"      # white
#FFA500 : orange

TAG=TAG    # reserved for future
apachehost="root@10.10.10.10"
apachepath="/var/www/htdocs"

#----------------------------
db_log ()
{
echo "# Collect db_log"
# DB
db_info=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select DATABASE_NAME,TOT_FILE_SYSTEM_MB,USED_DB_SPACE_MB from db")
echo "TH;4;TAG;DB/LOG_NAME;TOTAL_FILESYSTEM_SIZE_GB;PERCENT_USED" > $TMPFILE.db_log
filesys_sz=$(echo $db_info | cut -d',' -f2)
filesys_used=$(echo $db_info | cut -d',' -f3)
db_name=$(echo $db_info | cut -d',' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))

if [ "$pct_used" -gt "${alert_db_disk[1]}" ]
then
  NAGIOSTAG=2
else
  if [ "$pct_used" -gt "${alert_db_disk[0]}" ]
  then
    NAGIOSTAG=1
  else
    NAGIOSTAG=0
  fi
fi
# Check latest backup DB date
if [ $($DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from db where LAST_BACKUP_DATE>=current_timestamp-24 hours" | tr ',' '\n' | wc -l) -lt "10" ]
then
  NAGIOSTAG=2
fi
echo "TD;$NAGIOSTAG;$TAG;$db_name;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log

#$DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from db where LAST_BACKUP_DATE<=current_timestamp-24 hours"

# Logs
log_info=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select TOTAL_SPACE_MB,USED_SPACE_MB,ARCHLOG_TOL_FS_MB,ARCHLOG_USED_FS_MB from log")
filesys_sz=$(echo $log_info | cut -d',' -f1 | cut -d'.' -f1)
filesys_used=$(echo $log_info | cut -d',' -f2 | cut -d'.' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))
NAGIOSTAG=0
echo "TD;$NAGIOSTAG;$TAG;ACTIVE_LOG;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log

filesys_sz=$(echo $log_info | cut -d',' -f3 | cut -d'.' -f1)
filesys_used=$(echo $log_info | cut -d',' -f4 | cut -d'.' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))

if [ "$pct_used" -gt "${alert_log_fs[1]}" ]
then
  NAGIOSTAG=2
else
  if [ "$pct_used" -gt "${alert_log_fs[0]}" ]
  then
    NAGIOSTAG=1
  else
    NAGIOSTAG=0
  fi
fi
echo "TD;$NAGIOSTAG;$TAG;ARCHIVE_LOG;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log
}

#----------------------------
last_backup_db_tsm ()
{
echo "# Check last DB backup date"
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select LAST_BACKUP_DATE,LAST_REORG,TRANSLATE('a bc:de:fg', DIGITS(current_timestamp-LAST_BACKUP_DATE), '_______abcdefgh_____',' ') as ELAPTIME from db" | sed 's/\./,/g' | cut -d',' -f1,3,5 | sed 's/,/;/g' | sed 's/\ /|/g' > $TMPFILE.last_backup_db_tsm1

if [ $(tail -1 $TMPFILE.last_backup_db_tsm1 | egrep -q '^[0-9][0-9]';echo $?) -ne 0 ]
then
  echo "ERROR;ERROR" > $TMPFILE.last_backup_db_tsm1
  NAGIOSTAG=2
else
  if [ $(tail -1 $TMPFILE.last_backup_db_tsm1 | cut -d';' -f3 | cut -d'|' -f1) -gt 0 ]
  then
    NAGIOSTAG=2
  else
    NAGIOSTAG=0
  fi
fi
echo "TH;4;TAG;LAST_BACKUP_TSM_DB_DATE;LAST_REORG_DB" > $TMPFILE.last_backup_db_tsm
echo "TD;$NAGIOSTAG;$TAG;"$(tail -1 $TMPFILE.last_backup_db_tsm1 | cut -d';' -f1-2) >> $TMPFILE.last_backup_db_tsm
rm $TMPFILE.last_backup_db_tsm1
}

#----------------------------
backup_vm ()
{
echo "# Collect backup_vm"
#ACTIVITY_DETAILS: VMware

$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,ENTITY from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE

echo "TH;4;TAG;START_TIME;END_TIME;NODE_NAME;SCHEDULE_NAME;SUCCESSFUL;ELAPTIME;GB;DM_NAME" > $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
}

#----------------------------
error_vm_backup ()
{
echo "# Collect Error in VM backup"
# Check VM backups, filespace name like \VMFULL

echo "TH;4;TAG;DATACENTER;VM;LAST_BACKUP;DAYS_SINCE_LAST_BACKUP" > $TMPFILE.failed_vm
#$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS,cast(date(current_timestamp)-date(backup_end) as integer) as "Days" from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

CURDATE=$($DATE_BIN +%Y-%m-%d)

cat $TMPFILE | while read line
do
  LASTACC=$(echo $line | cut -d';' -f3)
  nbdays=`echo $(( ($($DATE_BIN -d $CURDATE +%s) - $($DATE_BIN -d $LASTACC +%s) )/(60*60*24) ))`
  if [ "$nbdays" -le "${alert_vm_backup[0]}" ]
  then
    NAGIOSTAG=0
  else
    if [ "$nbdays" -le "${alert_vm_backup[1]}" ]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=2
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$nbdays" >> $TMPFILE.failed_vm
done
}

#----------------------------
read_events ()
{
echo "# Collect 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' ) and date_time>=current_timestamp-24 hours" | sed 's/\ /|/g' | sed 's/;/\-/g' | sort | egrep -v "ANR2034E|ANR1701E|ANR2111W|ANR2992W|ANR1695W|ANR0944E|ANR4976W|ANR0482W|ANR0568W|ANR3763E|ANR3755E|ANR3693W|ANR3692W|ANR1794W|ANR1946W" > $TMPFILE.actlog
echo "TH;4;TAG;DATE_TIME;ORIGINATOR;NODENAME;SCHEDNAME;DOMAINNAME;MESSAGE ID;MESSAGE" > $TMPFILE.error

cat $TMPFILE.actlog | sed 's/^W/3;TAG/g' |  sed 's/^E/2;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' | sed 's/"//g' >> $TMPFILE.error

if [ ${activity_stat} == "yes" ]
then
  cat /dev/null > $TMPFILE.error1
  for i in $(cat $TMPFILE.error | cut -d';' -f1-3,5-8 | sort -u )
  do
    j=$(echo $i | cut -d';' -f4-)
    cnt=$(grep "$j" $TMPFILE.error | wc -l)
    echo "$i;$cnt" >> $TMPFILE.error1
  done
  echo "TH;4;TAG;ORIGINATOR;NODENAME;SCHEDNAME;DOMAINNAME;ERROR/WARNING|COUNT" > $TMPFILE.error
  cat $TMPFILE.error1 | grep -v '^TH;4;TAG;' >> $TMPFILE.error
  rm $TMPFILE.error1 > /dev/null 2>&1
fi
}

#----------------------------
stgpool_stats ()
{
echo "# Collect stgpool_stats"
# Add 02-2016
echo "TH;4;TAG;DEVCLASS_NAME;DIRECTORY;CAPACITY;SPACE_FREE;%USED" > $TMPFILE.dirspace
$DSMADMC $OPT_COMMA $OPT_DATAONLY "q dirspace" | egrep -v "ANR2034E|ANS8001I" | sed -e 's/\ //g' -e 's/"//g' > $TMPFILE.dirspace.tmp

for line in $(cat $TMPFILE.dirspace.tmp)
do
  i=$(echo $line | cut -d',' -f1)
  j=$(echo $line | cut -d',' -f2)
  end=$(echo $line | cut -d',' -f3-)
  k=$(echo $end | sed 's/,//g' | tr 'A-Z' ' ' | awk '{print $1}')
  l=$(echo $end | sed 's/,//g' | tr '0-9' ' ' | awk '{print $1}')
  m=$(echo $end | sed 's/,//g' | tr 'A-Z' ' ' | awk '{print $2}')
  n=$(echo $end | sed 's/,//g' | tr '0-9' ' ' | awk '{print $2}')
  if [[ "$l" == "K" || "$l" == "B" || "$k" == "0" ]]
  then
    pct_used=0
  else
    case "$l" in
       "M") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "G") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "T") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "P") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024*1024*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
    esac
    case "$n" in
       "B") used=0
            ;;
       "K") used=0
            ;;
       "M") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "G") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "T") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "P") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024*1024*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
    esac
    pct_used1=$(echo "scale=2;100*(1-($used/$cap))" | bc)
    pct_used=$(echo $pct_used1 | cut -d'.' -f1)
    if [ "$pct_used" == "" ]
    then
      pct_used=0
    fi
  fi
  if [[ "$pct_used" -gt "${alert_dispace_fs[1]}" ]]
  then
    NAGIOSTAG=2
  else
    if [[ "$pct_used" -gt "${alert_dispace_fs[0]}" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$i;$j;$k|$l;$m|$n;${pct_used}|%" | sed 's/\ //g' >> $TMPFILE.dirspace
done
cp $TMPFILE.dirspace $TMPFILE.dirspace1
rm $TMPFILE.dirspace.tmp

# Add 09-2019
echo "TH;4;TAG;STGPOOL_NAME;DIR_NAME;TOTAL_SPACE_MB;FREE_SPACE_MB;%USED" > $TMPFILE.dirspace
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from STGPOOL_DIRS" | egrep -v "ANR2034E|ANS8001I" | awk -F',' '{print $1","$2","$6","$5}' > $TMPFILE.dirspace.tmp
for line in $(cat $TMPFILE.dirspace.tmp)
do
  i=$(echo $line | cut -d',' -f1)
  j=$(echo $line | cut -d',' -f2)
  k=$(echo $line | cut -d',' -f3)
  l=$(echo $line | cut -d',' -f4)
  pct_used=$(echo "scale=2;100*(1-($l/$k))" | bc | cut -d'.' -f1)
  pct_used1=$(echo ${pct_used} | cut -d'.' -f1)
  if [ "$pct_used1" == "" ]
  then
    pct_used=0
  fi
  if [[ "$pct_used1" -gt "${alert_dispace_fs[1]}" ]]
  then
    NAGIOSTAG=2
  else
    if [[ "$pct_used1" -gt "${alert_dispace_fs[0]}" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$i;$j;$k;$l;${pct_used}|%" >> $TMPFILE.dirspace
done
echo >> $TMPFILE.dirspace1
cat $TMPFILE.dirspace >> $TMPFILE.dirspace1
mv $TMPFILE.dirspace1 $TMPFILE.dirspace
rm $TMPFILE.dirspace.tmp

# Add 10-2015
echo "TH;4;TAG;STGPOOL_NAME;STGPOOL_SIZE_GB" >> $TMPFILE.stgpool_occ
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select stgpool_name,sum(reporting_mb)/1024 from occupancy group by stgpool_name" | sed 's/,/;/g' | sed 's/\ /\|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.stgpool_occ.tmp
cat $TMPFILE.stgpool_occ.tmp | sed 's/^/TD;5;TAG;/' >> $TMPFILE.stgpool_occ
rm  $TMPFILE.stgpool_occ.tmp

echo "TH;4;TAG;STGPOOL_NAME;POOLTYPE;DEVCLASS;NEXTSTGPOOL;DEDUPLICATE;EST_CAPACITY_MB;PCT_UTILIZED;PCT_LOGICAL;MAXSCRATCH;NUMSCRATCHUSED;SPACE_SAVED_MB;DEDUP_RATE(%);DEDUP_RATIO" > $TMPFILE.stgpool_stats
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select STGPOOL_NAME,POOLTYPE,DEVCLASS,NEXTSTGPOOL,DEDUPLICATE,EST_CAPACITY_MB,PCT_UTILIZED,PCT_LOGICAL,MAXSCRATCH,NUMSCRATCHUSED,SPACE_SAVED_MB from stgpools" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.stgpool_stats.tmp

for line in $(cat $TMPFILE.stgpool_stats.tmp)
do
  DEVCLASS=$(echo $line | cut -d';' -f3)
  NEXTSTGPOOL=$(echo $line | cut -d';' -f4)
  PCTUTIL=$(echo $line | cut -d';' -f7)
  MAXSCRATCH=$(echo $line | cut -d';' -f9)
  MAXSCRATCHUSED=$(echo $line | cut -d';' -f10)
  SAVEDMB=$(echo $line | cut -d';' -f11)
  if [[ $MAXSCRATCH -eq "" ]]
  then
    NAGIOSTAG=0
  else
    if [ $MAXSCRATCH = 0 ]
    then
      if [ $MAXSCRATCHUSED = 0 ]
      then
        NAGIOSTAG=0
      else
        NAGIOSTAG=2
      fi
    else
      (( pctused = 100 * MAXSCRATCHUSED / MAXSCRATCH ))
      if [ $pctused -gt "${alert_stg_maxscr[1]}" ]
      then
        NAGIOSTAG=2
      else
        if [ $pctused -gt "${alert_stg_maxscr[0]}" ]
        then
          NAGIOSTAG=1
        else
          NAGIOSTAG=0
        fi
      fi
    fi
  fi
  if [[ "$SAVEDMB" -ne "" ]]
  then
    ESTCAPMB=$(echo $line | cut -d';' -f6)
    STOREDMB=$(echo "scale=2;$ESTCAPMB*$PCTUTIL/100" | bc)
    PCTDEDUP=$(echo "scale=1;100*$SAVEDMB/($SAVEDMB+$STOREDMB)" | bc)
    RATIO=$(echo "scale=1;($SAVEDMB+$STOREDMB)/$STOREDMB" | bc)
    end=$(echo "$PCTDEDUP;$RATIO"":1")
  else
    end=$(echo ";")
  fi
  # Check usage of a stg type DISK or CONTAINER
  if [[ "$DEVCLASS" == "DISK" || "$DEVCLASS" == "" ]] 
  then
    if [ "$NEXTSTGPOOL" = "" ]
    then
      pctused=$(echo $PCTUTIL | cut -d'.' -f1)
      if [ "$pctused" -gt "${alert_stg_disk[1]}" ]
      then
        NAGIOSTAG=2
      else
        if [ "$pctused" -gt "${alert_stg_disk[0]}" ]
        then
          NAGIOSTAG=1
        else
          NAGIOSTAG=0
        fi
      fi
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$end" >> $TMPFILE.stgpool_stats
done
rm $TMPFILE.stgpool_stats.tmp
}

#----------------------------
check_library ()
{
echo "# Collect check_library"
COUNT='count(*)'
echo "TH;4;TAG;Library_name;scratch" > $TMPFILE.scratch
for libr in $($DSMADMC $OPT_TAB $OPT_DATAONLY "select LIBRARY_NAME from libraries" | egrep -v "^ANR|^ANE|^ANS")
do
  library=$(echo $libr | sed "s/^/\'/" | sed "s/$/\'/")
  scr=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select $COUNT as Scratch  from libvolumes WHERE status='Scratch' and library_name=$library")
  line=$(echo "$libr $scr")
  echo $line | sed 's/\ /\;/g' >> $TMPFILE.scratch1
done

#$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_TYPE;ONLINE;OFFLINE" > $TMPFILE.paths
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select DESTINATION_TYPE,ONLINE from paths" | egrep -v "^ANR|^ANE|^ANS" | 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 LIBRARY_NAME,DRIVE_NAME,DEVICE_TYPE,ONLINE from drives" | egrep -v "^ANR|^ANE|^ANS" | sed 's/,/;/g' | sed 's/\ /|/g' > $TMPFILE.drives1

NAGIOSTAG=0
TAG=TAG
for line in $(cat $TMPFILE.scratch1 2>/dev/null)
do
  if [ $(echo $line | cut -d';' -f2) -le "${alert_libr_maxscr[1]}" ]
  then
    NAGIOSTAG=2
  else
    if [ $(echo $line | cut -d';' -f2) -le "${alert_libr_maxscr[0]}" ]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.scratch
done
rm $TMPFILE.scratch1 2>/dev/null

for type in $(cat $TMPFILE.paths1 2>/dev/null | cut -d';' -f1 | sort | uniq)
do
  online=$(cat $TMPFILE.paths1 | grep "^$type" | cut -d';' -f2 | grep 'YES' | wc -l | awk '{print $1}')
  offline=$(cat $TMPFILE.paths1 | grep "^$type" | cut -d';' -f2 | grep -v 'YES' | wc -l | awk '{print $1}')
  if [ "$offline" -eq "0" ]
  then 
    NAGIOSTAG=0
  else
    if [ "$offline" -ge "$online" ]
    then
      NAGIOSTAG=2
    else
      NAGIOSTAG=1
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$type;$online;$offline" >> $TMPFILE.paths
done
rm $TMPFILE.paths1 2>/dev/null

for line in $(cat $TMPFILE.drives1)
do
  end=$(echo $line | cut -d';' -f4)
  if [ "$end" != "YES" ]
  then
    NAGIOSTAG=1
    echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.drives
  fi
done
for library in $(cat $TMPFILE.drives1 | cut -d';' -f1 | sort | uniq)
do
  online=$(cat $TMPFILE.drives1 | grep "^$library" | cut -d';' -f4 | grep 'YES' | wc -l | awk '{print $1}')
  total=$(cat $TMPFILE.drives1 | grep "^$library" | wc -l | awk '{print $1}') 
  ((offline = total - online))
  type=$(cat $TMPFILE.drives1 | grep "^$library" | cut -d';' -f3 | tail -1)
  if [ "$offline" -eq "0" ]
  then
    NAGIOSTAG=0
  else
    if [ "$offline" -ge "$online" ]
    then
      NAGIOSTAG=2
    else
      NAGIOSTAG=1
    fi 
  fi
  echo "TD;$NAGIOSTAG;$TAG;$library;"'|'";$type;$online"'|/|'"$total" >> $TMPFILE.drives
done
rm $TMPFILE.drives1 2>/dev/null
}

#----------------------------
check_volume_error ()
{
echo "# Collect check_volume_error"
echo "TH;4;TAG;VOLUME_NAME;STGPOOL_NAME;ACCESS;WRITE_ERRORS;READ_ERRORS" > $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,STGPOOL_NAME,ACCESS,WRITE_ERRORS,READ_ERRORS from volumes where ACCESS<>'READWRITE' and ACCESS<>'OFFSITE'" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -iv "found|return" | sed 's/^\//#/' | grep -v '^#' | sed "s/^/TD;2;$TAG;/" >> $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,STGPOOL_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/^\//#/' | grep -v '^#' | sed "s/^/TD;1;$TAG;/" >> $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select CONTAINER_NAME,STGPOOL_NAME,STATE from containers where STATE<>'AVAILABLE'" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -iv "found|return" | sed 's/^\//#/' | grep -v '^#' | sed "s/^/TD;1;$TAG;/" | sed "s/$/;0;0/" >> $TMPFILE.volume_error
}

#----------------------------
auditocc ()
{
echo "# Collect 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;/" | egrep -v "ANR2034E|ANS8001I" >> $TMPFILE.auditocc
}

#----------------------------
daily_backuparch_per_node ()
{
echo "# Collect daily_backuparch_per_node"
NAGIOSTAG=0
TAG=TAG
HEADER="TH;4;TAG;DOMAIN_NAME;NODE_NAME"

$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT node_name,domain_name FROM nodes"  | sed 's/,/;/g' | sed 's/\ /|/g' | sed 's/$/;/' > $TMPFILE.qnode

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

  $DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT entity,cast(float(sum(bytes)) / 1024 / 1024 / 1024 AS DECIMAL(8,2)) as GB FROM summary where ( activity='ARCHIVE' OR lower(activity) like '%backup%') AND entity<>'' AND end_time>current_timestamp-$END hours and end_time<current_timestamp-$BEGIN hours GROUP BY entity"  | sed 's/,/;/g' | sed 's/\ /|/g' | grep -v '^;' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.daily_backup-arch_per_node.tmp

  cat /dev/null > $TMPFILE.daily_backup-arch_per_node.$days
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.tmp)
  do
    node_name=$(echo $line | awk -F'|' '{print $1}' | awk -F';' '{print $1}')
    dom=$(grep "^$node_name;" $TMPFILE.qnode | cut -d';' -f2)
    line2=$(echo $line | cut -d';' -f2)
    echo "${dom};${node_name};${line2}" >> $TMPFILE.daily_backup-arch_per_node.$days
  done

  # Keep only 1 line per node
  cat /dev/null > $TMPFILE.daily_backup-arch_per_node.tmp
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.$days)
  do
    domnode=$(echo $line | cut -d';' -f1,2)
    grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.tmp > /dev/null 2>&1
    if [ $? -ne 0 ]
    then
      numoccurence=$(grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.$days | wc -l | awk '{print $1}')
      if [ "$numoccurence" -gt 1 ]
      then
        final_amount="0.00"
        for amount in $(grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.$days | cut -d';' -f3)
        do
          final_amount=$(echo "${final_amount} + ${amount}" | bc | sed 's/^\./0\./')
        done
        echo "$domnode;$final_amount" >> $TMPFILE.daily_backup-arch_per_node.tmp
      else
        echo "$line" >> $TMPFILE.daily_backup-arch_per_node.tmp
      fi
    fi
  done
  mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node.$days
done

if [ ! -f $TMPFILE.daily_backup-arch_per_node ]
then
  cat $TMPFILE.daily_backup-arch_per_node.* | cut -d';' -f1,2 | sort | uniq > $TMPFILE.daily_backup-arch_per_node
fi

count=0
while [[ $count -le $days ]] 
do
  cat $TMPFILE.daily_backup-arch_per_node | sed 's/$/;/' > $TMPFILE.daily_backup-arch_per_node.tmp
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.${count})
  do
    domnode=$(echo $line | cut -d';' -f1,2)
    value=$(echo $line | cut -d';' -f3)
    grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.tmp > /dev/null 2>&1
    if [ $? -eq 0 ]
    then
        sed -e "/$domnode;/ s/$/$value/" $TMPFILE.daily_backup-arch_per_node.tmp > $TMPFILE.daily_backup-arch_per_node
        mv $TMPFILE.daily_backup-arch_per_node $TMPFILE.daily_backup-arch_per_node.tmp
    else
        echo "ERROR: field not found"
    fi
  done
  mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node
  count=$(expr $count + 1)
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_schedule_status ()
{
echo "# Collect admin_shedule_status"
$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,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 | sed 's/|//g')
  if [ "$status" == "YES" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=2
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.sum_admin
done

rm $TMPFILE.sum_admin.tmp
}

#----------------------------
check_expired_filespaces ()
{
echo "# Collect expired filespaces"
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,FILESPACE_ID,FILESPACE_TYPE,CAPACITY,PCT_UTIL,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS from filespaces where node_name<>'' order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | sed -e 's/"//g' -e "s/'//g" | sed -e 's/\\a/\\A/g' -e 's/\\b/\\B/g' -e 's/\\c/\\C/g' -e 's/\\f/\\F/g' -e 's/\\n/\\N/g' -e 's/\\r/\\R/g' -e 's/\\t/\\T/g' -e 's/\\v/\\V/g' | sed -e 's/^/"/' -e "s/^/'/" | sed -e 's/$/"/' -e "s/$/'/" | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.filespaces_last_access.tmp

$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_ID,sum(REPORTING_MB) as MB,sum(NUM_FILES) as nb_files from occupancy group by NODE_NAME,FILESPACE_ID order by NODE_NAME" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.occupancy_per_filespace.tmp

echo "TH;4;TAG;NODE_NAME;FILESPACE_NAME;FILESPACE_ID;FILESPACE_TYPE;CAPACITY;PCT_UTIL;LAST_ACCESS;REPORTING_SIZE_MB;NB_FILES" > $TMPFILE.filespaces_last_access

for line in $(cat $TMPFILE.filespaces_last_access.tmp)
do
  end=$(echo $line | cut -d';' -f7 | sed -e 's/"//g' -e "s/'//g")
  node=$(echo $line | cut -d';' -f1 | sed -e 's/"//g' -e "s/'//g")
  fsid=$(echo $line | cut -d';' -f3)
  grep "^$node;$fsid;" $TMPFILE.occupancy_per_filespace.tmp > /dev/null 2>&1
  if [ $? -eq 0 ]
  then
    occ=$(grep "^$node;$fsid;" $TMPFILE.occupancy_per_filespace.tmp | cut -d';' -f3,4)
  else
    occ="EMPTY;"
  fi
  node_exception=$(echo ${node_expired} | sed -e 's/^/;/' -e 's/,/;\|;/g' -e 's/$/;/')
  if [ $(echo ";$node;" | egrep "${node_exception}" > /dev/null;echo $?) -eq 0 ]
  then
    NAGIOSTAG=4
  else
    if [[ "$end" < "$EXPIRED" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$occ" | sed -e 's/"//g' -e "s/'//g" >> $TMPFILE.filespaces_last_access
done

rm $TMPFILE.filespaces_last_access.tmp $TMPFILE.occupancy_per_filespace.tmp
if [ ${filespace_stat} == "no" ]
then 
  rm $TMPFILE.filespaces_last_access > /dev/null 2>&1
fi
}

#----------------------------
check_expired_nodes ()
{
echo "# Collect expired nodes"
$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT node_name,domain_name,platform_name,client_os_level,TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as TSM_Version,DATE(lastacc_time) AS LASTACC_TIME,TCP_NAME,TCP_ADDRESS FROM nodes order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.nodes_last_access.tmp

for line in $(cat $TMPFILE.nodes_last_access.tmp)
do
  node=$(echo $line | cut -d';' -f1 | sed -e "s/^/'/" -e "s/$/'/")
  nb_sched=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select count(*) from associations where node_name=$node")
  echo "$line;${nb_sched}" >> $TMPFILE.nodes_last_access.tmp1
done
mv $TMPFILE.nodes_last_access.tmp1 $TMPFILE.nodes_last_access.tmp

echo "TH;4;TAG;NODE_NAME;DOMAIN_NAME;PLATFORM_NAME;CLIENT_OS_LEVEL;TSM_LEVEL;LAST_ACCESS;TCP_NAME;TCP_ADDRESS;NB_SCHEDULE" > $TMPFILE.nodes_last_access

for line in $(cat $TMPFILE.nodes_last_access.tmp)
do
  end=$(echo $line | cut -d';' -f6)
  node=$(echo $line | cut -d';' -f1)
  node_exception=$(echo ${node_expired} | sed -e 's/^/;/' -e 's/,/;\|;/g' -e 's/$/;/')
  if [ $(echo ";$node;" | egrep "${node_exception}" > /dev/null;echo $?) -eq 0 ]
  then
    NAGIOSTAG=4
  else
    if [[ "$end" < "$EXPIRED" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.nodes_last_access
done

rm $TMPFILE.nodes_last_access.tmp
}

#----------------------------
repl_status ()
{
echo "# Collect replication status"
# count the number of filespaces for node where replication is enabled, and compare to latest 2 days success replicated filespaces
echo "TH;4;TAG;Node_name;Num_filespace;Num_replicated_filespaces" > $TMPFILE.replication
for node in $($DSMADMC $OPT_COMMA $OPT_DATAONLY "select node_name from nodes where REPL_STATE='ENABLED' and REPL_MODE='SEND'" | egrep -v "ANR2034E|ANS8001I")
do
  node1=$(echo "'"$node"'")
  nb=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select count(*) from filespaces where node_name=$node1")
  nb1=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FSID,START_TIME,COMP_STATE from REPLICATIONVIEW where node_name=$node1 and COMP_STATE='COMPLETE'" | egrep "$YESTERDAY|$TODAY" | cut -d',' -f1,2 | sort -u | wc -l)
  if [ "$nb" = "$nb1" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=1
  fi
  echo "TD;$NAGIOSTAG;TAG;$node;$nb;$nb1" >> $TMPFILE.replication
done
}

#----------------------------
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>
server=$1
tsmversion=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "show versionhistory" | tail -1 | cut -d',' -f2  | grep -v "ANS8001I")

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

for i in db_log last_backup_db_tsm admin volume_error scratch dirspace stgpool_stats paths drives client failed_vm backup_vm replication error total sum_admin stgpool_occ auditocc daily_backup-arch_per_node filespaces_last_access nodes_last_access
do
  nb_line=$(cat $TMPFILE.$i* | wc -l)
  if [ "$nb_line" -gt "1" ]
  then
    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>'
  fi
done


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

#----------------------------
copy_to_apache ()
{
if [ -s $htmlfile ]
then
  #scp $htmlfile $apachehost:$apachepath
  newname=$(echo $htmlfile | sed "s/_${TODAY}//" | rev | cut -d'/' -f1 | rev)
  cp $htmlfile /home/lpar2rrd/lpar2rrd/www/$newname
  mv $htmlfile /report/TSM
fi
}

#----------------------------
send_email ()
{
newname=$(echo $htmlfile | rev | cut -d'/' -f1 | rev)
if [[ $os == "aix" ]]
then
  uuencode $htmlfile $newname | mailx -r "$tsmemailsender" -s "TSM daily report $TODAY from location $server" $tsmemailreceiver
else
  if [[ $os == "linux" ]]
  then
    echo | mailx -a "$htmlfile" -r "$tsmemailsender" -s "TSM daily report $TODAY from location $server" $tsmemailreceiver
  fi
fi
}

#############################
# Main
#############################
main ()
{
date
for server in $(echo $tsmservers)
do
  echo "********Collecting info on TSM server: $server"
  mkdir -p $tmplog
  DSMADMC=$(echo "$DSMADMC1 -se=$server")
  rm  $TMPFILE* 2> /dev/null
  htmlfile=$logpath/tsm_daily_report_${TODAY}_${server}.html
  if [ $($DSMADMC "q db" | grep -q ANS1017E; echo $?) -eq 0 ]
  then
    echo "TH;2;TAG;Server|Down!!!!" > $TMPFILE.db_log
    echo "TD;2;TAG;Unable|to|contact|the|server." >> $TMPFILE.db_log
  else
    db_log
    last_backup_db_tsm
    backup_vm
    error_vm_backup
    read_events
    check_library
    check_volume_error
    auditocc
    stgpool_stats
    admin_schedule_status
    daily_backuparch_per_node
    check_expired_nodes
    check_expired_filespaces
    repl_status
  fi
  convert_html $server 1> $htmlfile
  send_email $server
  #copy_to_apache
  rm $TMPFILE*
done

date
}

main > $logname 2>&1
</code>

===== How to read this report =====

==== TSM Client Management ====

=== Introduction on TSM backup Management ===

Your backup infrastructure is based on TSM (Spectrum Protect), each physical or virtual server is backup using a TSM client locally or using agents for component like Exchange, MSSQL, VMware...

Every day you can generate an HTML report based on a UNIX script, and can be sent by email, or copied to a apache server.

=== Description and analyze of my TSM report === 

This TSM report is divided in parts that we will detailed.

=== TSM server reporting ===

Cette partie est composée des éléments en tête du rapport et sont vitale à la disponibilité du serveur TSM. En cas de couleur autre que du vert (orange ou rouge), ces alertes doivent être remontée au niveau de support supérieur .
TSM db_log, ceci concerne directement la DB TSM
TSM admin, ceci est en relation avec les taches administratives sur le serveur TSM
TSM scratch, nombre de K7 vide disponible dans les robot de lackup
TSM dirspace, espace disk pour les backup
TSM stgpool_stats, espace disk pour les backups
TSM paths, acces au robot de backup
TSM drives, disponibilité des lecteurs

4.2.  TSM clients reporting
Cette partie est composée des éléments relatifs aux clients TSM. En cas de couleur autre que du vert (orange ou rouge), ces alertes doivent être traitées dans la mesure du possible par le helpdesk. Pour cela différentes rubriques sont à consulter.
1. TSM client
Ce sont les backups des clients TSM, ils sont schedulés par le serveur TSM. Pour les backups standard en mode fichier on travaille en incrémental forever (schedule : DAILY_BKP), pour les autres composants, le type de schedule est décrit dans le nom du schedule (Ex : DAILY_FULLEXCH…)
Les schedules en rouge et orange doivent être contrôlés, et dans la mesure du possible corrigés par le helpdesk.
Check des status 
MISSED : client TSM non joignable depuis le serveur TSM (soit le client n’existe pas soit son service TSM scheduler n’est pas démarré. Ce status ne doit pas persister dans le temps, si le client n’est plus utilisé il faut le desassocier du serveur TSM (voir avec l’admin TSM)
SEVERED : A voir dans la rubrique TSM error, ou bien aller sur la machine qui se backuppe et si c’est un serveur Windows aller contrôler le fichier c:\TSM\logs\dsmerror.log . En cas de doute demander à un administrateur TSM
FAILLED : voir la rubrique TSM error et rechercher le client en question, ou bien aller sur la machine qui se backuppe et si c’est un serveur Windows aller contrôler le fichier c:\TSM\logs\dsmerror.log
En général il faut ajuster la liste d’exclusion (ceci est à faire de manière cohérente en réfléchie en fonction du type de fichier) cf TSM client exclusion
COMPLETED 4, 8, different du status 0, 

2. TSM replication
En principe le status de la réplication doit être verte. Si durant plusieurs jours on constate un status différent, alors demander l’avis à un administrateur TSM

4.3.  TSM statistiques reporting
Cette partie en fin de document comporte :
TSM total : stats globale de backup et restore sur les dernières 24h
TSM sum_admin : toutes les tâches administratives, schedulees ou lancées automatiquement par le serveurs TSM
TSM stgpool_occ : volumétrie stockgée dans les stgpool TSM
TSM auditocc : volumes de données stockées sur le server TSM
TSM daily_backup-arch_per_node: stats de backup journalière par node (client)
TSM filespaces_last_access: statistiques sur les filesystems ou drives (en windows), avec la volumétrie stockée sur TSM et la date du dernier backup (en orange pour info les filespaces non accédés depuis plus de 30 jours
TSM nodes_last_access: information sur les type d’Os, version d’OS, version de TSM…et en orange les nodes TSM non accédés depuis plus de 30 jours 


==== TSM clients Management ====

==== How TSM client is backup ? ====

Each TSM client (node) is associated to a Policy Domain. This Policy domain is a classification of servers base on similar characteristics Ex: Windows, UNIX, Oracle...

In each Policy domain you have a default management class which define the policy for backup (retention, location of backup...), and optionally other management classes.

On TSM client, the configuration file is located by default in c:\Program Files\Tivoli\TSM\baclient\dsm.opt for Windows and /opt/tivoli/tsm/client/ba/bin/dsm.opt for linux/UNIX, holds the scope to backup, the DOMAIN.
  __Ex:__ DOMAIN c :, ALL-LOCAL,…
If not specified by an **INCLUDE** option, then the **default management class from Policy Domain** will applied to backup objects (files, folders, ...).   

==== How to avoid backup error on TSM ? ====

Some files can be locked during backup and are reported as error on TSM server. You can also find this list in the error file on TSM client, located on TSM client installation directory, or in a specific place like c:\TSM\logs\dsmerror.log or /var/log/tsm/dsmerror.log like specified in dsm.opt or dsm.sys (linux).

You have to analyse the kind of file generated errors when backup, ask to yourself the good questions:
<cli prompt='#'>
- Is the file an base OS file ?
- Is this file daily in error ?
- Is it a DB file ?
- Is it a temporary file ? Important or not ?
</cli>

Basic case is MSSQL database:
<code>
Ex : Error processing '\\ytiprctx01\c$\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\mydb.mdf': the object is in use by another process (SESSION: 132642)
</code>
This file is part of Database and cannot be backup as simple file, then you can stop the database before the backup (option to add in dsm.opt : PRE/POST SCHEDULE) to freeze the DB, take a backup and release the DB. Or this database can be excluded because it's not useful, then exclude it from backup. Other solution to have a consistent backup is to create a dump on disk and backup it, then exclude the files .ldf and .mdf, or use a sepecific agent to do an online backup on TSM disk.
  Ex : EXCLUDE "*:\...\MSSQL\...\*.mdf"

Cache, spooler, temporary, ... files can be exclude. But do not exclude known type files like .xlsx or .docx when it generate once or more times error ! You have to guarantee the ability to restore all important files on your server. 
<code>
Error processing '\\nfmprctx01\c$\Users\stock\AppData\Roaming\Microsoft\Windows\Cookies\02C0ENCN.txt': access to the object is denied
Error processing '\\nfmprctx01\c$\Users\stock\AppData\Local\Microsoft\Windows\WebCache\V0100004.log': access to the object is denied (SESSION: 132607)
Error processing '\\prexchbeerens01\c$\ProgramData\Microsoft\Windows Defender\Scans\History\CacheManager\MpSfc.bin': the object is in use by another process (SESSION: 131796)
</code>

Other trouble, for example VSS error: the error GLOBALROOT is a problem related to Windows VSS. An upgrade of the TSM client on the Windows server should solve this issue.
  Error processing '\\?\GLOBALROOT\Device\HarddiskVolume1\EFI\Microsoft\Boot\BCD': the object is in use by another process (SESSION: 132206)

After each change in the file dsm.opt, restart TSM scheduler or dsmcad service.
For information on UNIX/Linux 2 files are used: dsm.opt (containing domain) and dsm.sys (containing include/exclude options).

==== TSM client include/exclude syntax ====
 
Include / exclude options are always read **from end to top**, so when you want to include files from directory  c:\home\mes fichiers\ and exclude the rest of the directory c:\home\, put options like this:
<code>
Exclude c:\home\...\*
Include c:\home\mes fichiers\...\*
</code>

Other examples :
<code>
exclude "*:\...\MSSQL\...\*.mdf"
exclude ?:\...\swapper.dat 
exclude "*:\ea data. sf" 
exclude ?:\io.sys 
exclude ?:\...\spart.par 
exclude c:\*\budget.fin 
exclude c:\devel\* 
exclude.dir c:\home\jodda 
exclude.archive c:\home\*.obj
exclude  c:\test\...\*.jpg
exclude c:\Users\Administrator\Documents\Important\...\*
</code>

==== How to update a TSM client ====

For standard TSM clients, we can directly update the software depending on OS version, it often requires a reboot on Windows, but not on Linux.

TSM clients with specific agent like Exchange, MSSQL … must be validated using product compatibility matrix, for this ask a TSM administrator.

^ ^ windows Level(winver) ^ Windows name ^ TSM client version ^
| |4.0|Windows NT 4 |?|	
| |5.0|Windows 2000 |5.3|
| |5.1|Windows XP |6.2|
| |5.2|Windows XP x64 |6.2|
| |5.2|Windows 2003 / 2003 R2|6.3|
| |6.0|Vista |6.4|
| |6.0|Windows 2008 x86 and x64|7.1|
| |6.1|Windows 2008 R2 / Windows 7 |7.1|
| |6.2|Windows 2012 / Windows 8 |7.1|
| |6.3|Windows 2012 R2/ Windows 8.1 |7.1|

^ ^ Linux version (uname -a)^ TSM client version ^
| |Linux x86 |6.2|	
| |Linux x64 |7.1|

Latest TSM client updates are available on:
  ftp://public.dhe.ibm.com/storage/tivoli-storage-management/patches/client/

I install most time patch instead of base level.


====== version 06-2023 ======

replication improved

<code>
#!/bin/bash
#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
# version 1.1 report send by mail
# version 1.2 suppress unused informations
# version 1.3 add statistics on 5 latest daily backups, and dedup stats
# version 1.4 add expired nodes and filespaces
# version 1.5 check node replication
# version 1.6 centralize alert threshold in cfg file (08/2016)
# version 1.7 add container space (09/2019)

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

os=$(uname -s | tr 'A-Z' 'a-z')

if [[ $os == "aix" ]]
then
  # Needs rpm package coreutil
  DATE_BIN=/opt/freeware/bin/date
  if [ ! -x /opt/freeware/bin/date ]
  then
    echo "Install bash and coreutil packages rpm or bff"
    exit 1
  fi
  DSMADMC1="/usr/tivoli/tsm/client/ba/bin64/dsmadmc -id=${tsm_admin} -pa=${tsm_password}"
  HOSTNAME=$(/usr/bin/hostname -s)
else
  if [[ $os == "linux" ]]
  then
    DATE_BIN=/bin/date
    DSMADMC1="/opt/tivoli/tsm/client/ba/bin/dsmadmc -id=${tsm_admin} -pa=${tsm_password}"
    HOSTNAME=$(/bin/hostname -s)
  else
    echo "ERROR: Unknown OS"
    exit 1
  fi
fi

YESTERDAY=$($DATE_BIN +%Y-%m-%d -d "1 day ago")
TODAY=$($DATE_BIN +%Y-%m-%d)
EXPIRED=$($DATE_BIN +%Y-%m-%d -d "$expired day ago")

OPT_COMMA="-comma"
OPT_TAB="-tab"
OPT_DATAONLY="-dataonly=yes"

tmplog=$logpath/tmp
TMPFILE=$tmplog/tsm_daily_result

#NAGIOSTAG="0=OK, 1=WARNING, 2=CRITICAL, 3=UNKNOWN"
COLOR[0]="#57E964"      # green
COLOR[1]="#F87217"      # orange #F7BE81
COLOR[2]="#FF2400"      # red    #DF013A
COLOR[3]="yellow"       # yellow #F3F781
COLOR[4]="#56A5EC"      # blue
COLOR[5]="#FFFFFF"      # white
#FFA500 : orange

TAG=TAG    # reserved for future
apachehost="root@10.10.10.10"
apachepath="/var/www/htdocs"

#----------------------------
db_log ()
{
echo "# Collect db_log"
# DB
db_info=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select DATABASE_NAME,TOT_FILE_SYSTEM_MB,USED_DB_SPACE_MB from db")
echo "TH;4;TAG;DB/LOG_NAME;TOTAL_FILESYSTEM_SIZE_GB;PERCENT_USED" > $TMPFILE.db_log
filesys_sz=$(echo $db_info | cut -d',' -f2)
filesys_used=$(echo $db_info | cut -d',' -f3)
db_name=$(echo $db_info | cut -d',' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))

if [ "$pct_used" -gt "${alert_db_disk[1]}" ]
then
  NAGIOSTAG=2
else
  if [ "$pct_used" -gt "${alert_db_disk[0]}" ]
  then
    NAGIOSTAG=1
  else
    NAGIOSTAG=0
  fi
fi

# Check latest backup DB date
if [ $($DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from db where LAST_BACKUP_DATE>=current_timestamp-24 hours" | tr ',' '\n' | wc -l) -lt "10" ]
then
  NAGIOSTAG=2
fi
echo "TD;$NAGIOSTAG;$TAG;$db_name;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log

# Logs
log_info=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select TOTAL_SPACE_MB,USED_SPACE_MB,ARCHLOG_TOL_FS_MB,ARCHLOG_USED_FS_MB from log")
filesys_sz=$(echo $log_info | cut -d',' -f1 | cut -d'.' -f1)
filesys_used=$(echo $log_info | cut -d',' -f2 | cut -d'.' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))
NAGIOSTAG=0
echo "TD;$NAGIOSTAG;$TAG;ACTIVE_LOG;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log

filesys_sz=$(echo $log_info | cut -d',' -f3 | cut -d'.' -f1)
filesys_used=$(echo $log_info | cut -d',' -f4 | cut -d'.' -f1)
(( filesys_sz_gb = filesys_sz / 1024 ))
(( pct_used = 100 * filesys_used / filesys_sz ))

if [ "$pct_used" -gt "${alert_log_fs[1]}" ]
then
  NAGIOSTAG=2
else
  if [ "$pct_used" -gt "${alert_log_fs[0]}" ]
  then
    NAGIOSTAG=1
  else
    NAGIOSTAG=0
  fi
fi
echo "TD;$NAGIOSTAG;$TAG;ARCHIVE_LOG;$filesys_sz_gb;${pct_used} %" >> $TMPFILE.db_log
}

#----------------------------
last_backup_db_tsm ()
{
echo "# Check last DB backup date"
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select LAST_BACKUP_DATE,LAST_REORG,TRANSLATE('a bc:de:fg', DIGITS(current_timestamp-LAST_BACKUP_DATE), '_______abcdefgh_____',' ') as ELAPTIME from db" | sed 's/\./,/g' | cut -d',' -f1,3,5 | sed 's/,/;/g' | sed 's/\ /|/g' > $TMPFILE.last_backup_db_tsm1

if [ $(tail -1 $TMPFILE.last_backup_db_tsm1 | egrep -q '^[0-9][0-9]';echo $?) -ne 0 ]
then
  echo "ERROR;ERROR" > $TMPFILE.last_backup_db_tsm1
  NAGIOSTAG=2
else
  if [ $(tail -1 $TMPFILE.last_backup_db_tsm1 | cut -d';' -f3 | cut -d'|' -f1) -gt 0 ]
  then
    NAGIOSTAG=2
  else
    NAGIOSTAG=0
  fi
fi
echo "TH;4;TAG;LAST_BACKUP_TSM_DB_DATE;LAST_REORG_DB" > $TMPFILE.last_backup_db_tsm
echo "TD;$NAGIOSTAG;$TAG;"$(tail -1 $TMPFILE.last_backup_db_tsm1 | cut -d';' -f1-2) >> $TMPFILE.last_backup_db_tsm
rm $TMPFILE.last_backup_db_tsm1
}

#----------------------------
backup_vm ()
{
echo "# Collect backup_vm"
#ACTIVITY_DETAILS: VMware

$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,ENTITY from summary_extended where ACTIVITY_DETAILS='VMware' and ACTIVITY<>'EXPIRATION' and SUB_ENTITY<>'' and START_TIME>=current_timestamp-24 hours" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | grep -v 'Aggregate' > $TMPFILE

echo "TH;4;TAG;START_TIME;END_TIME;NODE_NAME;SCHEDULE_NAME;SUCCESSFUL;ELAPTIME;GB;DM_NAME" > $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
}

#----------------------------
error_vm_backup ()
{
echo "# Collect Error in VM backup"
# Check VM backups, filespace name like \VMFULL

echo "TH;4;TAG;DATACENTER;VM;LAST_BACKUP;DAYS_SINCE_LAST_BACKUP" > $TMPFILE.failed_vm
#$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS,cast(date(current_timestamp)-date(backup_end) as integer) as "Days" from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS from filespaces where node_name<>'' and FILESPACE_NAME like '%\VMFULL%' and BACKUP_END<current_timestamp-24 hours order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" | sed 's/\\VMFULL\-//' > $TMPFILE

CURDATE=$($DATE_BIN +%Y-%m-%d)

cat $TMPFILE | while read line
do
  LASTACC=$(echo $line | cut -d';' -f3)
  nbdays=`echo $(( ($($DATE_BIN -d $CURDATE +%s) - $($DATE_BIN -d $LASTACC +%s) )/(60*60*24) ))`
  if [ "$nbdays" -le "${alert_vm_backup[0]}" ]
  then
    NAGIOSTAG=0
  else
    if [ "$nbdays" -le "${alert_vm_backup[1]}" ]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=2
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$nbdays" >> $TMPFILE.failed_vm
done
}

#----------------------------
read_events ()
{
echo "# Collect 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=$(cat $TMPFILE.activitytotal | sed 's/NAS|//' | grep -i "^$act;" | cut -d';' -f2 | paste -s -d+ - | bc)
  #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' ) and date_time>=current_timestamp-24 hours" | sed 's/\ /|/g' | sed 's/;/\-/g' | sort | egrep -v "ANR0213E|ANR2034E|ANR1701E|ANR2111W|ANR2992W|ANR1695W|ANR0944E|ANR4976W|ANR0482W|ANR0568W|ANR3763E|ANR3755E|ANR3693W|ANR3692W|ANR1794W|ANR1946W|ANR1942W" > $TMPFILE.actlog
echo "TH;4;TAG;DATE_TIME;ORIGINATOR;NODENAME;SCHEDNAME;DOMAINNAME;MESSAGE ID;MESSAGE" > $TMPFILE.error
sed 's/|/\ /g' $TMPFILE.actlog > $TMPFILE.full.error.$TODAY


cat $TMPFILE.actlog | sed 's/^W/3;TAG/g' |  sed 's/^E/2;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' | sed 's/"//g' >> $TMPFILE.error

if [ ${activity_stat} == "yes" ]
then
  cat /dev/null > $TMPFILE.error1
  for i in $(cat $TMPFILE.error | cut -d';' -f1-3,5-8 | sort -u )
  do
    j=$(echo $i | cut -d';' -f4-)
    cnt=$(grep "$j" $TMPFILE.error | wc -l)
    echo "$i;$cnt" >> $TMPFILE.error1
  done
  echo "TH;4;TAG;ORIGINATOR;NODENAME;SCHEDNAME;DOMAINNAME;ERROR/WARNING|COUNT" > $TMPFILE.error
  cat $TMPFILE.error1 | grep -v '^TH;4;TAG;' >> $TMPFILE.error
  rm $TMPFILE.error1 > /dev/null 2>&1
fi
}

#----------------------------
stgpool_stats ()
{
echo "# Collect stgpool_stats"
# Add 02-2016
echo "TH;4;TAG;DEVCLASS_NAME;DIRECTORY;CAPACITY;SPACE_FREE;%USED" > $TMPFILE.dirspace
$DSMADMC $OPT_COMMA $OPT_DATAONLY "q dirspace" | egrep -v "ANR2034E|ANS8001I" | sed 's/\ /|/g' > $TMPFILE.dirspace.tmp
#cat $TMPFILE.dirspace.tmp | while read i j k l m n
for line in $(cat $TMPFILE.dirspace.tmp)
do
  i=$(echo $line | cut -d',' -f1)
  j=$(echo $line | cut -d',' -f2)
  k=$(echo $line | cut -d',' -f3- | cut -d'"' -f2 | sed -e 's/[A-Z]/\ /g' -e 's/|//g' | sed 's/,//g')
  l=$(echo $line | cut -d',' -f3- | cut -d'"' -f2 | sed 's/"//g' | rev | cut -d'|' -f1 | rev)
  m=$(echo $line | cut -d'"' -f3- | sed -e 's/"//g' -e 's/,//g' | cut -d'|' -f1)
  n=$(echo $line | cut -d'"' -f3- | sed -e 's/"//g' -e 's/,//g' | rev | cut -d'|' -f1 | rev)
  if [[ "$l" == "K" || "$l" == "B" || "$k" == "0" ]]
  then
    pct_used=0
  else
    case "$l" in
       "M") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "G") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "T") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
       "P") cap=$(echo $k | sed 's/,//g')
            cap1=$(echo "scale=2;$cap*1024*1024*1024" | bc)
            cap=$(echo $cap1 | cut -d'.' -f1)
            ;;
    esac
    case "$n" in
       "B") used=0
            ;;
       "K") used=0
            ;;
       "M") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "G") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "T") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
       "P") used=$(echo $m | sed 's/,//g')
            used1=$(echo "scale=2;$used*1024*1024*1024" | bc)
            used=$(echo $used1 | cut -d'.' -f1)
            ;;
    esac
    pct_used1=$(echo "scale=2;100*(1-($used/$cap))" | bc)
    pct_used=$(echo $pct_used1 | cut -d'.' -f1)
    if [ "$pct_used" == "" ]
    then
      pct_used=0
    fi
  fi
  if [[ "$pct_used" -gt "${alert_dispace_fs[1]}" ]]
  then
    NAGIOSTAG=2
  else
    if [[ "$pct_used" -gt "${alert_dispace_fs[0]}" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$i;$j;$k|$l;$m|$n;${pct_used}|%" | sed 's/\ //g' >> $TMPFILE.dirspace
done
cp $TMPFILE.dirspace $TMPFILE.dirspace1
rm $TMPFILE.dirspace.tmp

# Add 09-2019
echo "TH;4;TAG;STGPOOL_NAME;DIR_NAME;TOTAL_SPACE_MB;FREE_SPACE_MB;%USED" > $TMPFILE.dirspace
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select * from STGPOOL_DIRS" | egrep -v "ANR2034E|ANS8001I" | awk -F',' '{print $1","$2","$6","$5}' > $TMPFILE.dirspace.tmp
for line in $(cat $TMPFILE.dirspace.tmp)
do
  i=$(echo $line | cut -d',' -f1)
  j=$(echo $line | cut -d',' -f2)
  k=$(echo $line | cut -d',' -f3)
  l=$(echo $line | cut -d',' -f4)
  pct_used=$(echo "scale=2;100*(1-($l/$k))" | bc)
  pct_used1=$(echo ${pct_used} | cut -d'.' -f1)
  if [ "$pct_used1" == "" ]
  then
    pct_used=0
  fi
  if [[ "$pct_used1" -gt "${alert_dispace_fs[1]}" ]]
  then
    NAGIOSTAG=2
  else
    if [[ "$pct_used1" -gt "${alert_dispace_fs[0]}" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$i;$j;$k;$l;${pct_used}|%" >> $TMPFILE.dirspace
done
echo >> $TMPFILE.dirspace1
cat $TMPFILE.dirspace >> $TMPFILE.dirspace1
mv $TMPFILE.dirspace1 $TMPFILE.dirspace
rm $TMPFILE.dirspace.tmp

# Add 10-2015
echo "TH;4;TAG;STGPOOL_NAME;STGPOOL_SIZE_GB" >> $TMPFILE.stgpool_occ
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select stgpool_name,sum(reporting_mb)/1024 from occupancy group by stgpool_name" | sed 's/,/;/g' | sed 's/\ /\|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.stgpool_occ.tmp
cat $TMPFILE.stgpool_occ.tmp | sed 's/^/TD;5;TAG;/' >> $TMPFILE.stgpool_occ
rm  $TMPFILE.stgpool_occ.tmp

echo "TH;4;TAG;STGPOOL_NAME;POOLTYPE;DEVCLASS;NEXTSTGPOOL;DEDUPLICATE;EST_CAPACITY_MB;PCT_UTILIZED;PCT_LOGICAL;MAXSCRATCH;NUMSCRATCHUSED;SPACE_SAVED_MB;DEDUP_RATE(%);DEDUP_RATIO" > $TMPFILE.stgpool_stats
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select STGPOOL_NAME,POOLTYPE,DEVCLASS,NEXTSTGPOOL,DEDUPLICATE,EST_CAPACITY_MB,PCT_UTILIZED,PCT_LOGICAL,MAXSCRATCH,NUMSCRATCHUSED,SPACE_SAVED_MB from stgpools" | sed 's/,/;/g' | sed 's/\ /\|/g' > $TMPFILE.stgpool_stats.tmp

for line in $(cat $TMPFILE.stgpool_stats.tmp)
do
  DEVCLASS=$(echo $line | cut -d';' -f3)
  NEXTSTGPOOL=$(echo $line | cut -d';' -f4)
  PCTUTIL=$(echo $line | cut -d';' -f7)
  MAXSCRATCH=$(echo $line | cut -d';' -f9)
  MAXSCRATCHUSED=$(echo $line | cut -d';' -f10)
  SAVEDMB=$(echo $line | cut -d';' -f11)
  if [[ $MAXSCRATCH -eq "" ]]
  then
    NAGIOSTAG=0
  else
    if [ $MAXSCRATCH = 0 ]
    then
      if [ $MAXSCRATCHUSED = 0 ]
      then
        NAGIOSTAG=0
      else
        NAGIOSTAG=2
      fi
    else
      (( pctused = 100 * MAXSCRATCHUSED / MAXSCRATCH ))
      if [ $pctused -gt "${alert_stg_maxscr[1]}" ]
      then
        NAGIOSTAG=2
      else
        if [ $pctused -gt "${alert_stg_maxscr[0]}" ]
        then
          NAGIOSTAG=1
        else
          NAGIOSTAG=0
        fi
      fi
    fi
  fi
  if [[ "$SAVEDMB" -ne "" ]]
  then
    ESTCAPMB=$(echo $line | cut -d';' -f6)
    STOREDMB=$(echo "scale=2;$ESTCAPMB*$PCTUTIL/100" | bc)
    PCTDEDUP=$(echo "scale=1;100*$SAVEDMB/($SAVEDMB+$STOREDMB)" | bc)
    RATIO=$(echo "scale=1;($SAVEDMB+$STOREDMB)/$STOREDMB" | bc)
    end=$(echo "$PCTDEDUP;$RATIO"":1")
  else
    end=$(echo ";")
  fi
  # Check usage of a stg type DISK or CONTAINER
  if [[ "$DEVCLASS" == "DISK" || "$DEVCLASS" == "" ]]
  then
    if [ "$NEXTSTGPOOL" = "" ]
    then
      pctused=$(echo $PCTUTIL | cut -d'.' -f1)
      if [ "$pctused" -gt "${alert_stg_disk[1]}" ]
      then
        NAGIOSTAG=2
      else
        if [ "$pctused" -gt "${alert_stg_disk[0]}" ]
        then
          NAGIOSTAG=1
        else
          NAGIOSTAG=0
        fi
      fi
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line;$end" >> $TMPFILE.stgpool_stats
done
rm $TMPFILE.stgpool_stats.tmp
}

#----------------------------
check_library ()
{
echo "# Collect check_library"
COUNT='count(*)'
echo "TH;4;TAG;Library_name;scratch" > $TMPFILE.scratch
for libr in $($DSMADMC $OPT_TAB $OPT_DATAONLY "select LIBRARY_NAME from libraries" | egrep -v "^ANR|^ANE|^ANS")
do
  library=$(echo $libr | sed "s/^/\'/" | sed "s/$/\'/")
  scr=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select $COUNT as Scratch  from libvolumes WHERE status='Scratch' and library_name=$library")
  line=$(echo "$libr $scr")
  echo $line | sed 's/\ /\;/g' >> $TMPFILE.scratch1
done

#$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_TYPE;ONLINE;OFFLINE" > $TMPFILE.paths
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select DESTINATION_TYPE,ONLINE from paths" | egrep -v "^ANR|^ANE|^ANS" | 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 LIBRARY_NAME,DRIVE_NAME,DEVICE_TYPE,ONLINE from drives" | egrep -v "^ANR|^ANE|^ANS" | sed 's/,/;/g' | sed 's/\ /|/g' > $TMPFILE.drives1

NAGIOSTAG=0
TAG=TAG
for line in $(cat $TMPFILE.scratch1 2>/dev/null)
do
  if [ $(echo $line | cut -d';' -f2) -le "${alert_libr_maxscr[1]}" ]
  then
    NAGIOSTAG=2
  else
    if [ $(echo $line | cut -d';' -f2) -le "${alert_libr_maxscr[0]}" ]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.scratch
done
rm $TMPFILE.scratch1 2>/dev/null

for type in $(cat $TMPFILE.paths1 2>/dev/null | cut -d';' -f1 | sort | uniq)
do
  online=$(cat $TMPFILE.paths1 | grep "^$type" | cut -d';' -f2 | grep 'YES' | wc -l | awk '{print $1}')
  offline=$(cat $TMPFILE.paths1 | grep "^$type" | cut -d';' -f2 | grep -v 'YES' | wc -l | awk '{print $1}')
  if [ "$offline" -eq "0" ]
  then
    NAGIOSTAG=0
  else
    if [ "$offline" -ge "$online" ]
    then
      NAGIOSTAG=2
    else
      NAGIOSTAG=1
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$type;$online;$offline" >> $TMPFILE.paths
done
rm $TMPFILE.paths1 2>/dev/null

for line in $(cat $TMPFILE.drives1 2>/dev/null)
do
  end=$(echo $line | cut -d';' -f4)
  if [ "$end" != "YES" ]
  then
    NAGIOSTAG=1
    echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.drives
  fi
done
for library in $(cat $TMPFILE.drives1 2>/dev/null | cut -d';' -f1 | sort | uniq)
do
  online=$(cat $TMPFILE.drives1 | grep "^$library" | cut -d';' -f4 | grep 'YES' | wc -l | awk '{print $1}')
  total=$(cat $TMPFILE.drives1 | grep "^$library" | wc -l | awk '{print $1}')
  ((offline = total - online))
  type=$(cat $TMPFILE.drives1 | grep "^$library" | cut -d';' -f3 | tail -1)
  if [ "$offline" -eq "0" ]
  then
    NAGIOSTAG=0
  else
    if [ "$offline" -ge "$online" ]
    then
      NAGIOSTAG=2
    else
      NAGIOSTAG=1
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$library;"'|'";$type;$online"'|/|'"$total" >> $TMPFILE.drives
done
rm $TMPFILE.drives1
}

#----------------------------
check_volume_error ()
{
echo "# Collect check_volume_error"
echo "TH;4;TAG;VOLUME_NAME;STGPOOL_NAME;ACCESS;WRITE_ERRORS;READ_ERRORS" > $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,STGPOOL_NAME,ACCESS,WRITE_ERRORS,READ_ERRORS from volumes where ACCESS<>'READWRITE' and ACCESS<>'OFFSITE'" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -iv "found|return" | grep -v '^/' | sed "s/^/TD;2;$TAG;/" >> $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select VOLUME_NAME,STGPOOL_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" | grep -v '^/' | sed "s/^/TD;1;$TAG;/" >> $TMPFILE.volume_error
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select CONTAINER_NAME,STGPOOL_NAME,STATE from containers where STATE<>'AVAILABLE' and TYPE<>'CLOUD'" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -iv "found|return" | sed "s/^/TD;1;$TAG;/" | sed "s/$/;0;0/" >> $TMPFILE.volume_error
}

#----------------------------
auditocc ()
{
echo "# Collect 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;/" | egrep -v "ANR2034E|ANS8001I" >> $TMPFILE.auditocc
}

#----------------------------
daily_backuparch_per_node ()
{
echo "# Collect daily_backuparch_per_node"
NAGIOSTAG=0
TAG=TAG

HEADER="TH;4;TAG;DOMAIN_NAME;NODE_NAME"

$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT node_name,domain_name FROM nodes"  | sed 's/,/;/g' | sed 's/\ /|/g' | sed 's/$/;/' > $TMPFILE.qnode

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

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

  cat /dev/null > $TMPFILE.daily_backup-arch_per_node.$days
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.tmp)
  do
    node_name=$(echo $line | awk -F'|' '{print $1}' | awk -F';' '{print $1}')
    dom=$(grep "^$node_name;" $TMPFILE.qnode | cut -d';' -f2)
    line2=$(echo $line | cut -d';' -f2)
    echo "${dom};${node_name};${line2}" >> $TMPFILE.daily_backup-arch_per_node.$days
  done

  # Keep only 1 line per node
  cat /dev/null > $TMPFILE.daily_backup-arch_per_node.tmp
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.$days)
  do
    domnode=$(echo $line | cut -d';' -f1,2)
    grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.tmp > /dev/null 2>&1
    if [ $? -ne 0 ]
    then
      numoccurence=$(grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.$days | wc -l | awk '{print $1}')
      if [ "$numoccurence" -gt 1 ]
      then
        final_amount="0.00"
        for amount in $(grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.$days | cut -d';' -f3)
        do
          final_amount=$(echo "${final_amount} + ${amount}" | bc | sed 's/^\./0\./')
        done
        echo "$domnode;$final_amount" >> $TMPFILE.daily_backup-arch_per_node.tmp
      else
        echo "$line" >> $TMPFILE.daily_backup-arch_per_node.tmp
      fi
    fi
  done
  mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node.$days
done

if [ ! -f $TMPFILE.daily_backup-arch_per_node ]
then
  cat $TMPFILE.daily_backup-arch_per_node.* | cut -d';' -f1,2 | sort | uniq > $TMPFILE.daily_backup-arch_per_node
fi

count=0
while [[ $count -le $days ]]
do
  cat $TMPFILE.daily_backup-arch_per_node | sed 's/$/;/' > $TMPFILE.daily_backup-arch_per_node.tmp
  for line in $(cat $TMPFILE.daily_backup-arch_per_node.${count})
  do
    domnode=$(echo $line | cut -d';' -f1,2)
    value=$(echo $line | cut -d';' -f3)
    grep "^$domnode;" $TMPFILE.daily_backup-arch_per_node.tmp > /dev/null 2>&1
    if [ $? -eq 0 ]
    then
        sed -e "/$domnode;/ s/$/$value/" $TMPFILE.daily_backup-arch_per_node.tmp > $TMPFILE.daily_backup-arch_per_node
        mv $TMPFILE.daily_backup-arch_per_node $TMPFILE.daily_backup-arch_per_node.tmp
    else
        echo "ERROR: field not found"
    fi
  done
  mv $TMPFILE.daily_backup-arch_per_node.tmp $TMPFILE.daily_backup-arch_per_node
  count=$(expr $count + 1)
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_schedule_status ()
{
echo "# Collect 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 | sed 's/|//g')
  if [ "$status" == "YES" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=2
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.sum_admin
done

rm $TMPFILE.sum_admin.tmp
}

#----------------------------
check_expired_filespaces ()
{
echo "# Collect expired filespaces"
if [ ${filespace_stat} == "no" ]
then
  $DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_NAME,FILESPACE_ID,FILESPACE_TYPE,CAPACITY,PCT_UTIL,to_char(char(BACKUP_END),'YYYY-MM-DD') as LAST_ACCESS from filespaces where node_name<>'' order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | sed -e 's/"//g' -e "s/'//g" | sed -e 's/\\a/\\A/g' -e 's/\\b/\\B/g' -e 's/\\c/\\C/g' -e 's/\\f/\\F/g' -e 's/\\n/\\N/g' -e 's/\\r/\\R/g' -e 's/\\t/\\T/g' -e 's/\\v/\\V/g' | sed -e 's/^/"/' -e "s/^/'/" | sed -e 's/$/"/' -e "s/$/'/" | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.filespaces_last_access.tmp

  $DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FILESPACE_ID,sum(REPORTING_MB) as MB,sum(NUM_FILES) as nb_files from occupancy group by NODE_NAME,FILESPACE_ID order by NODE_NAME" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.occupancy_per_filespace.tmp

  echo "TH;4;TAG;NODE_NAME;FILESPACE_NAME;FILESPACE_ID;FILESPACE_TYPE;CAPACITY;PCT_UTIL;LAST_ACCESS;REPORTING_SIZE_MB;NB_FILES" > $TMPFILE.filespaces_last_access

  for line in $(cat $TMPFILE.filespaces_last_access.tmp)
  do
    end=$(echo $line | cut -d';' -f7 | sed -e 's/"//g' -e "s/'//g")
    node=$(echo $line | cut -d';' -f1 | sed -e 's/"//g' -e "s/'//g")
    fsid=$(echo $line | cut -d';' -f3)
    grep "^$node;$fsid;" $TMPFILE.occupancy_per_filespace.tmp > /dev/null 2>&1
    if [ $? -eq 0 ]
    then
      occ=$(grep "^$node;$fsid;" $TMPFILE.occupancy_per_filespace.tmp | cut -d';' -f3,4)
    else
      occ="EMPTY;"
    fi
    node_exception=$(echo ${node_expired} | sed -e 's/^/;/' -e 's/,/;\|;/g' -e 's/$/;/')
    if [ $(echo ";$node;" | egrep "${node_exception}" > /dev/null;echo $?) -eq 0 ]
    then
      NAGIOSTAG=4
    else
      if [[ "$end" < "$EXPIRED" ]]
      then
        NAGIOSTAG=1
      else
        NAGIOSTAG=0
      fi
    fi
    echo "TD;$NAGIOSTAG;$TAG;$line;$occ" | sed -e 's/"//g' -e "s/'//g" >> $TMPFILE.filespaces_last_access
  done

  rm $TMPFILE.filespaces_last_access.tmp $TMPFILE.occupancy_per_filespace.tmp
fi
}

#----------------------------
check_expired_nodes ()
{
echo "# Collect expired nodes"
$DSMADMC $OPT_COMMA $OPT_DATAONLY "SELECT node_name,domain_name,platform_name,client_os_level,TRIM(CHAR(client_version))||'.'||TRIM(CHAR(client_release))||'.'||TRIM(CHAR(client_level))||'-'||TRIM(CHAR(client_sublevel)) as TSM_Version,DATE(lastacc_time) AS LASTACC_TIME,TCP_NAME,TCP_ADDRESS FROM nodes order by node_name" | sed 's/,/;/g' | sed 's/\ /|/g' | egrep -v "ANR2034E|ANS8001I" > $TMPFILE.nodes_last_access.tmp

for line in $(cat $TMPFILE.nodes_last_access.tmp)
do
  node=$(echo $line | cut -d';' -f1 | sed -e "s/^/'/" -e "s/$/'/")
  nb_sched=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "select count(*) from associations where node_name=$node")
  echo "$line;${nb_sched}" >> $TMPFILE.nodes_last_access.tmp1
done
mv $TMPFILE.nodes_last_access.tmp1 $TMPFILE.nodes_last_access.tmp

echo "TH;4;TAG;NODE_NAME;DOMAIN_NAME;PLATFORM_NAME;CLIENT_OS_LEVEL;TSM_LEVEL;LAST_ACCESS;TCP_NAME;TCP_ADDRESS;NB_SCHEDULE" > $TMPFILE.nodes_last_access

for line in $(cat $TMPFILE.nodes_last_access.tmp)
do
  end=$(echo $line | cut -d';' -f6)
  node=$(echo $line | cut -d';' -f1)
  node_exception=$(echo ${node_expired} | sed -e 's/^/;/' -e 's/,/;\|;/g' -e 's/$/;/')
  if [ $(echo ";$node;" | egrep "${node_exception}" > /dev/null;echo $?) -eq 0 ]
  then
    NAGIOSTAG=4
  else
    if [[ "$end" < "$EXPIRED" ]]
    then
      NAGIOSTAG=1
    else
      NAGIOSTAG=0
    fi
  fi
  echo "TD;$NAGIOSTAG;$TAG;$line" >> $TMPFILE.nodes_last_access
done

rm $TMPFILE.nodes_last_access.tmp
}

#----------------------------
repl_status ()
{
echo "# Collect replication status"
# count the number of filespaces for node where replication is enabled, and compare to latest 2 days success replicated filespaces
echo "TH;4;TAG;Node_name;Num_filespace;Num_replicated_filespaces" > $TMPFILE.replication
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select node_name from nodes where REPL_STATE='ENABLED' and REPL_MODE='SEND'" | egrep -v "ANR2034E|ANS8001I" | sed '/^$/d' > $TMPFILE.repl1
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select node_name,count(*) from filespaces group by node_name" > $TMPFILE.repl2
$DSMADMC $OPT_COMMA $OPT_DATAONLY "select NODE_NAME,FSID,START_TIME,COMP_STATE from REPLICATIONVIEW where COMP_STATE='COMPLETE'" | egrep "$YESTERDAY|$TODAY" > $TMPFILE.repl3

for node in $(cat $TMPFILE.repl1)
do
  node1=$(echo "'"$node"'")
  nb=$(grep "^$node," $TMPFILE.repl2 | cut -d',' -f2)
  if [ "$nb" == "" ]
  then
    nb=0
  fi
  nb1=$(grep "^$node," $TMPFILE.repl3 | cut -d',' -f1,2 | sort -u | wc -l)
  if [ "$nb1" == "" ]
  then
    nb1=0
  fi
  if [ "$nb" = "$nb1" ]
  then
    NAGIOSTAG=0
  else
    NAGIOSTAG=1
  fi
  echo "TD;$NAGIOSTAG;TAG;$node;$nb;$nb1" >> $TMPFILE.replication
done

rm $TMPFILE.repl1 $TMPFILE.repl2 $TMPFILE.repl3 2>/dev/null
}

#----------------------------
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>
server=$1
tsmversion=$($DSMADMC $OPT_COMMA $OPT_DATAONLY "show versionhistory" | tail -1 | cut -d',' -f2  | grep -v "ANS8001I")

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

for i in db_log last_backup_db_tsm admin volume_error scratch dirspace stgpool_stats paths drives client failed_vm backup_vm replication error total sum_admin stgpool_occ auditocc daily_backup-arch_per_node filespaces_last_access nodes_last_access
do
  nb_line=$(cat $TMPFILE.$i* 2>/dev/null | wc -l)
  if [ "$nb_line" -gt "1" ]
  then
    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>'
  fi
done


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

#----------------------------
copy_to_apache ()
{
echo "# Copy to report folder"
if [ -s $htmlfile ]
then
  #scp $htmlfile $apachehost:$apachepath
  newname=$(echo $htmlfile | sed "s/_${TODAY}//" | rev | cut -d'/' -f1 | rev)
  #cp $htmlfile /home/lpar2rrd/lpar2rrd/www/$newname
  #mv $htmlfile /root/scripts/reports
  mv $htmlfile /data/reports
fi
}


#----------------------------
send_email ()
{
echo "# Sending by email"
gzip -c $TMPFILE.full.error.$TODAY > /root/scripts/reports/q_act.$TODAY.gz
newname=$(echo $htmlfile | rev | cut -d'/' -f1 | rev)
if [[ $os == "aix" ]]
then
  uuencode $htmlfile $newname | mailx -r "$tsmemailsender" -s "$subject" $tsmemailreceiver
else
  if [[ $os == "linux" ]]
  then
    echo | mailx -a "$htmlfile" -r "$tsmemailsender" -s "$subject" $tsmemailreceiver
    echo | mailx -a /root/scripts/reports/q_act.$TODAY.gz -r "$tsmemailsender" -s "$subject logs" myemail@pt.lu
  fi
fi
}

#############################
# Main
#############################
main ()
{
date
durb=$(date +%s)

for server in $(echo $tsmservers)
do
  echo "********Collecting info on TSM server: $server"
  mkdir -p $tmplog
  DSMADMC=$(echo "$DSMADMC1 -se=$server")
  rm  $TMPFILE* 2> /dev/null
  htmlfile=$logpath/tsm_daily_report_${TODAY}_${server}.html
  if [ $(timeout 5 $DSMADMC "q db" > /dev/null 2>&1; echo $?) -ne 0 ]
  then
    subject=$(echo "TSM daily report $TODAY from location $server DOWN !!!")
    echo "TH;2;TAG;Server|Down!!!!" > $TMPFILE.db_log
    echo "TD;2;TAG;Unable|to|contact|the|server." >> $TMPFILE.db_log
  else
    subject=$(echo "TSM daily report $TODAY from location $server")
    d1=$(date +%s)
    db_log
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    last_backup_db_tsm
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    backup_vm
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    error_vm_backup
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    read_events
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    check_library
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    check_volume_error
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    auditocc
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    stgpool_stats
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    admin_schedule_status
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    daily_backuparch_per_node
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    check_expired_nodes
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    check_expired_filespaces
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
    repl_status
    d2=$(date +%s); echo "$(( (d2 - d1) )) seconds";d1=$(date +%s)
  fi
  convert_html $server 1> $htmlfile
  d2=$(date +%s); echo "$(( (d2 - d1) )) seconds"
  send_email $server
  #copy_to_apache
  rm $TMPFILE* 2>/dev/null
done

dure=$(date +%s)
echo "Full duration: $(( (dure - durb) / 60 )) minutes"
date
}

mv -f $logname ${logname}.1
main > $logname 2>&1
</code>


|	                       STGPOOL_NAME	|	 CONTAINER1	|	 S3	|	 DISK01	|
|	                           POOLTYPE	|	 PRIMARY	|	 PRIMARY	|	 PRIMARY	|
|	                           DEVCLASS	|		|		|	 DISK	|
|	                           STG_TYPE	|	 DIRECTORY	|	 CLOUD	|	 DEVCLASS	|
|	                    EST_CAPACITY_MB	|	 25126572.0	|		|	 12288000.0	|
|	                       PCT_UTILIZED	|	 40.6	|		|	 52.3	|
|	                        PCT_LOGICAL	|		|		|	 100.0	|
|	                         MAXSCRATCH	|		|		|		|
|	                     NUMSCRATCHUSED	|		|		|		|
|	                         COMPRESSED	|	 YES	|	 YES	|		|
|	                        DEDUPLICATE	|	 YES	|	 YES	|	 NO	|
|	            TOTAL_UNUSED_PENDING_MB	|	 482 G	|	 7,078 M	|		|
|	               DEDUP_SPACE_SAVED_MB	|	7254348	|	14192097	|		|
|	                COMP_SPACE_SAVED_MB	|	21609587	|	1689699	|		|
|	                     SPACE_SAVED_MB	|	28863934	|	15881795	|		|
|	                          ENCRYPTED	|	 Yes	|	 Yes	|		|
|	                      PCT_ENCRYPTED	|	 100.00	|	 100.00	|		|
|	               TOTAL_CLOUD_SPACE_MB	|		|	11025099	|		|
|	                USED_CLOUD_SPACE_MB	|		|	10391137	|		|
|	              LOCAL_EST_CAPACITY_MB	|		|	0.0|		|
|	                 LOCAL_PCT_UTILIZED	|		|	0.0|		|