Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Opscenter SQL query for unassigned Tapes

Created: 26 Jun 2013 • Updated: 15 Jul 2013 | 13 comments
Suganthi's picture
This issue has been solved. See solution.

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.

Operating Systems:

Comments 13 CommentsJump to latest comment

tom_sprouse's picture

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

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

Suganthi's picture

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.

tom_sprouse's picture

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

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

SOLUTION
Suganthi's picture

Tom this also doesnt seem to help me....

tom_sprouse's picture

Suganthi,

What are you looking for exactly?

--Tom

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

Suganthi's picture

I look for unassigned media count in each voulme pool available on the libraries TLD 0 and 1 seperately for better understanding.The second script you gave is giving some different tape count than its actual.

Suganthi's picture

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

SOLUTION
tom_sprouse's picture

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
 

Note: If this post provides you with a solution don't forget to mark the discussion as solved...

Suganthi's picture

This script also works for me. Thanks much :-)

lisareinhart's picture

How can I add the tape library alias name into this?  My tape library id just comes up as "0".

tom_sprouse's picture

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

Note: If this post provides you with a solution don't forget to mark the discussion as solved...