cancel
Showing results for 
Search instead for 
Did you mean: 

Opscenter SQL query for unassigned Tapes

Sughi
Level 4
Certified

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

tom_sprouse
Level 6
Employee Accredited Certified

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

 

View solution in original post

Sughi
Level 4
Certified

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

View solution in original post

13 REPLIES 13

tom_sprouse
Level 6
Employee Accredited Certified

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

Sughi
Level 4
Certified

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
Level 6
Employee Accredited Certified

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

 

Sughi
Level 4
Certified

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

tom_sprouse
Level 6
Employee Accredited Certified

Suganthi,

What are you looking for exactly?

--Tom

 

Sughi
Level 4
Certified
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.

Sughi
Level 4
Certified

Tom , can you please help me out.

Sughi
Level 4
Certified

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

tom_sprouse
Level 6
Employee Accredited Certified

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
 

Sughi
Level 4
Certified

This script also works for me. Thanks much :)

lisareinhart
Level 4

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

tom_sprouse
Level 6
Employee Accredited Certified

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

lisareinhart
Level 4

Great!  Thanks!