--  category:  IBM i Services for BRMS
--  description:  Application - BRMS Backup Status Views
--  minvrm: V7R3M0
--  last updated: 3/14/2023

--
-- View ALL Backup Statuses, most recent first
--
SELECT *
    FROM QUSRBRM.CG_STATUS
    ORDER BY SAVE_START DESC;

--
-- View ALL currently running backups
--
SELECT *
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'ACTIVE'
    ORDER BY SAVE_START DESC;

--
-- View the most recent backup
--
SELECT *
    FROM QUSRBRM.CG_STATUS
    ORDER BY SAVE_START DESC
    LIMIT 1;

--
-- View ALL Backup Statuses from yesterday
--
SELECT *
    FROM QUSRBRM.CG_STATUS
    WHERE DATE(SAVE_START) = DATE(NOW()) - 1 DAY;

--
-- View ALL Backup Statuses for a specific control group
--
SELECT *
    FROM QUSRBRM.CG_STATUS
    WHERE CONTROL_GROUP = 'QCLDCIPL01'
    ORDER BY SAVE_START DESC;

--
-- View ALL Backups that used a specific volume
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_SIZE,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS,
       VOLUMES
    FROM QUSRBRM.CG_STATUS
    WHERE VOLUMES LIKE '%003036%'
    ORDER BY SAVE_START DESC;

--
-- Report all failed backups, most recent first
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'FAILED'
    ORDER BY SAVE_START DESC;

--
-- Report the 5 biggest completed saves
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_SIZE,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'COMPLETE'
    ORDER BY SAVE_SIZE DESC
    LIMIT 5;

--
-- Report completed saves in the last 30 days
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_SIZE,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'COMPLETE'
          AND DATE(SAVE_START) >= DATE(NOW()) - 30 DAY
    ORDER BY SAVE_START DESC;

--
-- Report completed saves in the current calendar month
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_SIZE,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'COMPLETE'
          AND YEAR(DATE(SAVE_START)) = YEAR(DATE(NOW()))
          AND MONTH(DATE(SAVE_START)) = MONTH(DATE(NOW()))
    ORDER BY SAVE_START DESC;

--
-- Report completed saves in the current calendar year
--
SELECT CONTROL_GROUP,
       QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_SIZE,
       BACKUP_MINUTES,
       OBJECTS_SAVED,
       OBJECTS_NOT_SAVED,
       SAVE_STATUS
    FROM QUSRBRM.CG_STATUS
    WHERE SAVE_STATUS = 'COMPLETE'
          AND YEAR(DATE(SAVE_START)) = YEAR(DATE(NOW()))
    ORDER BY SAVE_START DESC;

--
-- Report all messages for a specific backup
--
SELECT CG_STATUS.CONTROL_GROUP,
       CG_STATUS.QUALIFIED_JOB_NAME,
       SAVE_START,
       SAVE_END,
       SAVE_STATUS,
       MSG_TIME,
       MSGID,
       SEVERITY,
       MSG_TEXT
    FROM QUSRBRM/CG_STATUS
         LEFT JOIN QUSRBRM/LOG_INFO
             ON LOG_INFO.QUALIFIED_JOB_NAME = CG_STATUS.QUALIFIED_JOB_NAME
                 AND LOG_INFO.MESSAGE_TIMESTAMP >= CG_STATUS.SAVE_START
                 AND LOG_INFO.MESSAGE_TIMESTAMP <= CG_STATUS.SAVE_END
    WHERE CG_STATUS.CONTROL_GROUP = 'QCLDBGRP01'
          AND CG_STATUS.QUALIFIED_JOB_NAME = '917135/FISTLER/DSP01'
          AND SAVE_START = '2022-11-15 17:23:02'
    ORDER BY MSG_TIME;