cancel
Showing results for 
Search instead for 
Did you mean: 

Custom OpsCenter SQL Queries needed.

S_R
Level 3

Hi everyone,

We are using Netbackup 7.6.1.2 and OpsCenter Analytics 7.6.1.2
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.


Regards,

Sezgin

5 REPLIES 5

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

S_R
Level 3

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

S_R
Level 3

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

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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.