We are using Netbackup 18.104.22.168 and OpsCenter Analytics 22.214.171.124
In NBU Admin Console, under Reports there are 2 reports that I would like to customize if the underlying data is available in the OpsCenter DB and I can get the SQL Queries for those reports.
Report1: Images on Tape (under Tape Reports)
Report2: Images on Disk (under Disk Reports, can be filtered by disk type)
If the reports are fetching the data from the OpsCenter DB would it be possible to obtain the executing sql statements by Tracing ?
Any help will be appreciated.
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.
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.
Ok, you can start with this
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
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
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.