SQL query to list DRM tapes ignores database volumes

[sandragon]

Wasn't sure if this fits in the DRM sub-forum or not, so my apologies if its in the wrong place.

Using the Operations Center, I have been sending our NOC a report of tapes daily to be handled for off-siting. However, I've discovered a bug and I am not sure how to correct it and have been unable to find a similar thread here or help online. All guides point me to the same SQL query, the one below:

SELECT drmedia.volume_name as "Volume", drmedia.state as "State" FROM drmedia, volumes WHERE drmedia.volume_name=volumes.volume_name AND drmedia.state='COURIER' ORDER BY drmedia.state

This query will tell me DRM tapes that are also Storage Pool volumes. However, it ignores database backup volumes. I've attempted to modify it but I end up with a list containing many duplicate entries for each tape.
Has anyone here modified this script or created a secondary script that the database volumes?

 

[RecoveryOne]

This is what we were using(?) on our 6.3 server when we were sending tapes offsite: Not 100% if it fits your needs but hopefully provides some insight:

SELECT cast ((volume_name) as char (8)) as VolSer, cast ((state) as char (15)) as State, upd_date, cast ((voltype) as char (10)) as VolType FROM drmedia WHERE upper(voltype) like upper('dbs%')

Replace dbs% with dbb% for regular database backups.

 

[sandragon]

This is what we were using(?) on our 6.3 server when we were sending tapes offsite: Not 100% if it fits your needs but hopefully provides some insight:

SELECT cast ((volume_name) as char (8)) as VolSer, cast ((state) as char (15)) as State, upd_date, cast ((voltype) as char (10)) as VolType FROM drmedia WHERE upper(voltype) like upper('dbs%')

Replace dbs% with dbb% for regular database backups.

I gave this a try, making a few edits, and it did the same thing, ignoring actual database backup volumes.

TS150> SELECT cast ((volume_name) as char (8)) as Volume, cast ((state) as char (15)) as State, upd_date as Updated, cast ((voltype) as char (10)) as VolType FROM drmedia WHERE state like 'COURIER' and upper(voltype) like upper('dbb%')
VOLUME    STATE    UPDATED    VOLTYPE
000016L5    COURIER    2017-09-18, 11:51:39    DBBackup
000195L5    COURIER    2017-09-18, 11:51:39    DBBackup
000139L5    COURIER    2017-09-18, 11:51:39    DBBackup
000194L5    COURIER    2017-09-18, 11:51:39    DBBackup
000134L5    COURIER    2017-09-18, 11:51:39    DBBackup
000121L5    COURIER    2017-09-18, 11:51:39    DBBackup
000166L5    COURIER    2017-09-18, 11:51:39    DBBackup
000145L5    COURIER    2017-09-18, 11:51:39    DBBackup
000124L5    COURIER    2017-09-18, 11:51:39    DBBackup

TS150> q drm wherest=courier
Volume Name    State    Last Update Date/Time    Automated LibName
000115L5    Courier    2017-09-18, 11:43:04   
000117L5    Courier    2017-09-18, 11:43:04   
000136L5    Courier    2017-09-18, 11:43:04   
000147L5    Courier    2017-09-18, 11:43:04   
000190L5    Courier    2017-09-18, 11:43:04   
000016L5    Courier    2017-09-18, 11:51:39   
000195L5    Courier    2017-09-18, 11:51:39   
000139L5    Courier    2017-09-18, 11:51:39   
000194L5    Courier    2017-09-18, 11:51:39   
000134L5    Courier    2017-09-18, 11:51:39   
000121L5    Courier    2017-09-18, 11:51:39   
000166L5    Courier    2017-09-18, 11:51:39   
000145L5    Courier    2017-09-18, 11:51:39   
000124L5    Courier    2017-09-18, 11:51:39