check-sql-backups

#!/usr/bin/env bash
set -eu

projects=$(gcloud projects list --format='value(projectId)')
projectsCount=0
instancesCount=0
backupsCount=0

projectWithNoInstancesCount=0
instancesWithNobackupsCount=0

backupsFailedCount=0
backupsFailedCount=0
backupsSuccessCount=0

now="$(date +%s)"

for project in $projects
do
  projectsCount=$(( projectsCount + 1 ))
  echo "$projectsCount PROJECT $project"
  instances=$(gcloud sql instances list --project $project --format='value(name)')
  if [ "$instances" != "" ]
  then
    instanceNumber=0
    for instance in $instances
    do
      instanceNumber=$(( instanceNumber + 1 ))
      instancesCount=$(( instancesCount + 1 ))
      echo "    $projectsCount-$instanceNumber INSTANCE $instance"
      backups=$(gcloud sql backups list --project $project --instance $instance --format='value(id)')

      if [ "$backups" != "" ]
      then
        backupNumber=0
        minHoursAgo=1000
        hoursAgo=-1
        for backup in $backups
        do
          backupNumber=$(( backupNumber + 1 ))
          backupsCount=$(( backupsCount + 1 ))
          details=$(gcloud sql backups describe $backup --project $project --instance $instance --format json)
          status="$(echo $details | jq -r .status)"
          endTime="$(echo $details | jq -r .endTime)"

          timeStamp="$(date -j -f '%Y-%m-%dT%H:%M:%S' ${endTime:0:19} '+%s')"
          hoursAgo="$(( ($now - $timeStamp) / 3600 ))"

          if [ "$status" != "SUCCESSFUL" ]
          then
            backupsFailedCount=$(( backupsFailedCount + 1 ))
            echo -e "        $projectsCount-$instanceNumber-$backupNumber BackupId $backup \033[31;1m$status\033[0m"
            echo -e "        ==> FAILURE $project:$instance:$backup <=="
            exit 1
            continue
          fi

          if [ $hoursAgo -lt $minHoursAgo ]
          then
            minHoursAgo=$hoursAgo
          fi

          echo -e "        $projectsCount-$instanceNumber-$backupNumber BackupId $backup \033[32;1m$status\033[0m ($hoursAgo hr ago)"
          backupsSuccessCount=$(( backupsSuccessCount + 1 ))
        done

        if [ $minHoursAgo -gt 48 ]
        then
          echo -e "        \033[31;1mINSTANCE $instance has not backuped for at least $minHoursAgo\033[0m"
          exit 2
        else
          echo -e "        \033[32;1m✔\033[0m  Last backup: $minHoursAgo hrs"
        fi
      else
        echo "        ==> INSTANCE $instance has not backups"
        instancesWithNobackupsCount=$(( instancesWithNobackupsCount + 1 ))
      fi
      echo
    done
  else
    echo "    ==> PROJECT $project has not SQL instances"
    projectWithNoInstancesCount=$(( projectWithNoInstancesCount + 1 ))
  fi
  echo
done

echo "Projects   : $projectsCount"
echo "Instances  : $instancesCount"
echo "Backups    : $backupsCount"
echo "  Success  : $backupsSuccessCount"
echo "  Failed   : $backupsFailedCount"
echo
echo "projectWithNoInstancesCount: $projectWithNoInstancesCount"
echo "instancesWithNobackupsCount: $instancesWithNobackupsCount"