I have this code, but I want to adjust some things. I would think each path per media server would be up/down or disabled, I wouldn’t expect to see mixed for the drive state for an individual path.
Also for the report we were looking to see the % of drive paths available for a given media server. Meaning, if a server has 12 drive paths and all of them are down then it would report 0% and investigation would be required immediately. If 75% of the paths to that media server are up, immediate action may not be required.
select M.friendlyName as "Master Server", TD.name as "Tape Drive", TDP.mediaServerName as "Media Server", TDP.devicePath as "Drive Path", DS.name as "Drive Status"
from nb_tapedrive TD, domain_MasterServer M, nb_TapeDrivePath TDP, lookup_DriveStatus DS
where TD.driveStatus = DS.id
and (M.id = TD.masterServerId)
and (TD.masterServerId = TDP.masterServerId and TDP.driveName = TD.name)
order by 1,3
