Custom OpsCenter SQL Queries needed.

Hi,

NetBackup reports use netbackup cli to provide information (https://support.symantec.com/en_US/article.TECH20462.html).

You could probably get the information from OpsCenter too. Let me know if you want to check opscenter or just use the CLi i supplied.

Hi Riaan,

Thanks for the reply. Yes, I would like to query the OpsCenter DB with SQL.
I have mappings(grouping) of clients according to some criteria and I would like to get reports grouped by those criteria. If I can get the base query I can modify it as required.

Regards,

Sezgin

Hello

Ok, you can start with this

select
domain_jobimage.clientname "Client Name",
domain_imagefragment.imageid as "ImageID",
domain_imagefragment.mediaid as "Media ID",
domain_imagefragment.copyid as "Copy Number",
domain_image.primarycopy as "Primary",
domain_imagefragment.size as "Size"
from domain_imagefragment, domain_jobimage, domain_image
where
domain_jobimage.imageid=domain_imagefragment.imageid
and
domain_image.id=domain_jobimage.imageid

Thanks Riaan.

I need to add the client name to the query as well. I came up with  the query below. The total size it brings is different from the Images on Tape Netbackup Report (something like 1.73E+12 vs 1.80E+12).
Any comments about the cause of  difference can help. When I find the time I intend to modify the query to get the results in similar format to the Netbackup results and compare them in Excel with VLOOKUP (by backup id for example).

select  domain_ImageClient.clientName, SUM(domain_ImageFragment.size) AS "Size"
from domain_Image JOIN  domain_ImageCopy ON domain_Image.masterServerId = domain_ImageCopy.masterServerId AND domain_Image.id = domain_ImageCopy.imageId
AND domain_ImageCopy.onHold=0 AND domain_ImageCopy.storageUnitType = 2 AND domain_Image.isValid = 1
JOIN domain_ImageFragment ON domain_ImageCopy.masterServerId = domain_ImageFragment.masterServerId  and domain_ImageCopy.imageId = domain_ImageFragment.imageId AND CAST(UTCBigintToUTCTime(domain_imagefragment.expirationTime) as varchar(19)) > dateadd(day,-1, getdate())
AND domain_ImageCopy.id = domain_ImageFragment.copyId
JOIN  domain_ImageClient ON  domain_ImageClient.masterServerId = domain_Image.masterServerId and domain_ImageClient.imageId = domain_Image.id
JOIN domain_MasterServer ON  domain_Image.masterServerId = domain_ImageCopy.masterServerId AND domain_MasterServer.id = domain_Image.masterServerId
group by domain_ImageClient.clientName order by domain_ImageClient.clientName

Hi,

Yeah its a bit hard to say. What I usually do if something seems a bit off is to "select * from" and then make sure I'm looking at the correct columns. Then verify it matches the catalog as you said with Excel.