List the number of clients by platform:
SELECT platform_name,count(*) AS "Number of Nodes" FROM nodes GROUP BY platform_name ORDER BY "Number of Nodes"
List all of the tapes used by a node:
SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE node_name='xxx'
List all of the nodes using a tape:
SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE volume_name='xxx'
List tapes used today:
SELECT volume_name,last_write_date FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)
Or to list the last write date for all the tapes in order:
SELECT volume_name,last_write_date FROM volumes ORDER BY last_write_date
Or to see just the number of tapes written to in the last 24 hours:
SELECT count(*) AS "Tapes Used" FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)
List tapes with errors:
SELECT volume_name, read_errors,write_errors FROM volumes WHERE (read_errors >= 1 OR write_errors >= 1)
List the number of tapes to be reclaimed at a certain level:
SELECT count(*) FROM volumes WHERE (stgpool_name='TAPEPOOL' AND upper(status)='FULL' AND pct_utilized < 70)
List the MB backed up last night per node:
SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR(CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY ENTITY order by "MB xfer"
List how much data was backed up yesterday:
SELECT entity AS "Node name", CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer", SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "start date", SUBSTR (CAST(min (start_time) AS char(29)),12,8) AS "start time", SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "end date", SUBSTR (CAST(max (end_time) AS char(29)),12,8) AS "end time" FROM summary WHERE (activity='BACKUP' AND start_time >= current_timestamp - 24 hours) GROUP BY entity
Or to just see how much was backed up system wide:
SELECT CAST(sum(bytes/1024/1024) AS decimal(8,2)) AS "MB xfer" FROM summary WHERE (activity='BACKUP' AND start_time>=current_timestamp - 24 hours)
List what nodes did restores yesterday:
SELECT entity, bytes, start_time, successful FROM summary WHERE (activity='RESTORE' AND start_time>=current_timestamp - 24 hours)
List the tapes in use and max scratch by storage pool:
SELECT a.stgpool_name,a.maxscratch,count(*) AS Volumes FROM stgpools a, volumes b WHERE a.stgpool_name = b.stgpool_name GROUP BY a.stgpool_name,a.maxscratch
List the number of tapes filling and how full by storage pool:
This lists the number of tapes that are "filling" and an average of how full they are per storage pool. This is a query that I wrote for the BigBrother client as a replacement for q stg:
SELECT a.stgpool_name AS "STORAGE POOL", CAST(a.EST_CAPACITY_MB AS DECIMAL(12,0)) AS "SIZE MB", CAST(a.PCT_UTILIZED AS DECIMAL(2,0)) AS "PCT", count(*) AS Filling, CAST(AVG(b.PCT_UTILIZED) AS DECIMAL(2,0)) AS "PCT Full" FROM stgpools a, volumes b WHERE ((a.stgpool_name = b.stgpool_name AND upper(status)='FILLING') OR (a.stgpool_name = b.stgpool_name AND devclass_name='DISK')) GROUP BY a.stgpool_name, a.EST_CAPACITY_MB, a.PCT_UTILIZED
Check database performance:
List the DB Pages backed up per hour (should be 5M or above).
SELECT activity, CAST((end_time) AS date) AS "Date", (examined/cast ((end_time-start_time) seconds AS DECIMAL(18,13))*3600) "Pages backed up/Hr" FROM summary WHERE activity='FULL_DBBACKUP' AND days(end_time) - days(start_time)=0M
List the DB Pages expired per hour (should be 3.8M or above).
SELECT activity, CAST((end_time) AS date) AS "Date", (examined/CAST((end_time-start_time) seconds AS DECIMAL(24,2))*3600) "Objects Examined Up/Hr" FROM summary WHERE activity='EXPIRATION'