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'

List the number of clients by platform:<\/p>\n

SELECT platform_name,count(*) AS \"Number of Nodes\" FROM nodes GROUP BY platform_name ORDER BY \"Number of Nodes\"<\/code><\/pre>\n
List all of the tapes used by a node:<\/p>\n
SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE node_name='xxx'<\/code><\/pre>\n
List all of the nodes using a tape:<\/p>\n
SELECT DISTINCT node_name,volume_name,stgpool_name FROM volumeusage WHERE volume_name='xxx'<\/code><\/pre>\n
List tapes used today:<\/p>\n
SELECT volume_name,last_write_date FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)<\/code><\/pre>\n
Or to list the last write date for all the tapes in order:<\/p>\n
SELECT volume_name,last_write_date FROM volumes ORDER BY last_write_date<\/code><\/pre>\n
Or to see just the number of tapes written to in the last 24 hours:<\/p>\n
SELECT count(*) AS \"Tapes Used\" FROM volumes WHERE (last_write_date >=current_timestamp - 24 hours)<\/code><\/pre>\n
List tapes with errors:<\/p>\n
SELECT volume_name, read_errors,write_errors FROM volumes WHERE (read_errors >= 1 OR write_errors >= 1)<\/code><\/pre>\n
List the number of tapes to be reclaimed at a certain level:<\/p>\n
SELECT count(*) FROM volumes WHERE (stgpool_name='TAPEPOOL' AND upper(status)='FULL' AND pct_utilized < 70)<\/code><\/pre>\n
List the MB backed up last night per node:<\/p>\n
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\"<\/code><\/pre>\n
List how much data was backed up yesterday:<\/p>\n
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<\/code><\/pre>\n
Or to just see how much was backed up system wide:<\/p>\n
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)<\/code><\/pre>\n
List what nodes did restores yesterday:<\/p>\n
SELECT entity, bytes, start_time, successful FROM summary WHERE (activity='RESTORE' AND start_time>=current_timestamp - 24 hours)<\/code><\/pre>\n
List the tapes in use and max scratch by storage pool:<\/p>\n
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<\/code><\/pre>\n
List the number of tapes filling and how full by storage pool:<\/p>\n
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:<\/p>\n
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<\/code><\/pre>\n
Check database performance:<\/p>\n
List the DB Pages backed up per hour (should be 5M or above).<\/p>\n
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<\/code><\/pre>\n
List the DB Pages expired per hour (should be 3.8M or above).<\/p>\n
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'<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":null,"protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,1],"tags":[],"class_list":["post-281","post","type-post","status-publish","format-standard","hentry","category-tsm-notes","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/posts\/281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/comments?post=281"}],"version-history":[{"count":0,"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/posts\/281\/revisions"}],"wp:attachment":[{"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/media?parent=281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/categories?post=281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rootuser.ninja\/index.php\/wp-json\/wp\/v2\/tags?post=281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}