06-26-2013 04:43 AM
Hi,
I have windows 2008 R2 NetBackup Master running 7.5.0.4 version.Opscenter also has the same version.
I have 2 SL500 library TLD 0 and TLD 1.....And I dont use scratch volume pool instead directly assign tapes to required volume pools.
I would like to get an Opscenter SQL query for getting unassigned tapes and its volume pools so that i can keep a track of the available tapes to avoid backup failures.
Solved! Go to Solution.
06-28-2013 05:29 AM
Suganthi,
Here is the script again, I added an additional clause (where volumeGroupName does not equal standalone)
SELECT COUNT(*) as "Unassigned Media", volumePoolName as "Volume Pool" FROM domain_media WHERE isValid = 1 AND imagecount IS NULL AND volumeGroupName <> '---' GROUP BY volumePoolName
Let me know if that helps... if so, please mark this as a solution.
-Tom
07-10-2013 12:47 AM
Tom, I have just added up libraryType = 8 that corresponds to my TLD library
SELECT COUNT(*) as "Unassigned Media",
volumePoolName as "Volume Pool"
FROM domain_media
WHERE isValid = 1
AND imagecount IS NULL
AND volumeGroupName <> '---' AND libraryType = 8
GROUP BY volumePoolName
And now i got what i needed. Thanks Tom for your help on this
06-27-2013 09:07 AM
Suganthi,
Let me know if this works for you...
SELECT COUNT(*) as "Unassigned Media", volumePoolName as "Volume Pool" FROM domain_media WHERE isValid = 1 AND imagecount IS NULL GROUP BY volumePoolName
06-28-2013 04:17 AM
Thanks Tom. This gives the count of all unassigned media including thats not in library.
Can you pelase help me out with the count of the unassigned tapes available in TLD 0 and TLD 1 library.
06-28-2013 05:29 AM
Suganthi,
Here is the script again, I added an additional clause (where volumeGroupName does not equal standalone)
SELECT COUNT(*) as "Unassigned Media", volumePoolName as "Volume Pool" FROM domain_media WHERE isValid = 1 AND imagecount IS NULL AND volumeGroupName <> '---' GROUP BY volumePoolName
Let me know if that helps... if so, please mark this as a solution.
-Tom
06-30-2013 09:00 PM
Tom this also doesnt seem to help me....
07-01-2013 04:53 AM
Suganthi,
What are you looking for exactly?
--Tom
07-01-2013 11:23 PM
07-09-2013 05:24 AM
Tom , can you please help me out.
07-10-2013 12:47 AM
Tom, I have just added up libraryType = 8 that corresponds to my TLD library
SELECT COUNT(*) as "Unassigned Media",
volumePoolName as "Volume Pool"
FROM domain_media
WHERE isValid = 1
AND imagecount IS NULL
AND volumeGroupName <> '---' AND libraryType = 8
GROUP BY volumePoolName
And now i got what i needed. Thanks Tom for your help on this
07-10-2013 05:37 AM
Suganthi,
I was working on getting this working... however, I appear to have an issue with the Scratch Pool in my environment....
SELECT friendlyName AS "Master Server", libraryId AS "Library ID", volumePoolName AS "Volume Pool", COUNT(*) as "Unassigned Media Count" FROM domain_media JOIN domain_masterserver ON domain_media.masterServerId = domain_masterserver.id WHERE isValid = 1 AND imagecount IS NULL AND volumeGroupName <> '---' AND isCleaning <> 1 GROUP BY friendlyname, libraryId, volumePoolName ORDER BY libraryId, volumePoolName
07-16-2013 08:24 PM
This script also works for me. Thanks much :)
07-29-2013 02:01 PM
How can I add the tape library alias name into this? My tape library id just comes up as "0".
07-29-2013 05:53 PM
lisa,
Please try the following:
SELECT friendlyName AS "Master Server", libraryId AS "Library ID", volumePoolName AS "Volume Pool", COUNT(*) AS "Unassigned Media Count", libraryalias AS "Alias" FROM domain_media JOIN domain_masterserver ON domain_media.masterServerId = domain_masterserver.id JOIN domain_tapelibrary ON domain_tapelibrary.id = domain_media.libraryid WHERE domain_media.isValid = 1 AND imagecount IS NULL AND volumeGroupName <> '---' AND isCleaning <> 1 GROUP BY friendlyname, libraryalias, libraryId, volumePoolName ORDER BY libraryalias, libraryId, volumePoolName
07-30-2013 05:47 AM
Great! Thanks!