Tabular Custom Report OpsCenter from Image Information

[LeonardoT]

Hi Everyone!

We are using Netbackup 7.6.0.4 and OpsCenter 7.7.
We are working with custom report but we are having problems to create report. We need a report like BPIMAGELIST but using VIEWS, because there are many groups of clients that we need create these reports. I need client name and Total Job Protected Size or sizeInBytes from images.
We had to create a new OpsCenter so we don´t have history jobs only information from Images from catalog. I´m trying to create a report from images but OpsCenter does not show what I want from tabular custom report.
I am trying to create:
Custom Report > Tabular > No Time Basis / Image Copy Expiration Time or Image Expiration Time > Select Client Name and Job Protected Size.

Any help will be appreciated!
Thanks!

Regards,
Tominaga

[RiaanBadenhorst]

Hi,

This will give you all images per client

select
domain_jobimage.clientname "Client Name",
sum(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
GROUP BY "Client Name"

[LeonardoT]

Hi Riaan!!!

Thanks for reply!!!

The select works but it brings all images for each client. Is there way to bring only valid images?

The result sum expired images too. I need only images that there were not expired.

I appriciate your help!

att.

Leonardo Tominaga

[RiaanBadenhorst]

Hi,

Try by adding

AND domain_Image.isValid = 1

select
domain_jobimage.clientname "Client Name",
sum(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

and

domain_Image.isValid = 1
GROUP BY "Client Name"

[LeonardoT]

Hi Riann,

I  added the line like you wrote but the value is still the same.

I realized there is something weird, as you can in attachment, there are expired images that are marked as valid.

att.

Leonardo Tominaga

[RiaanBadenhorst]

That's really odd. I just checked mine and the domain_image match the bpimagelist output exactly (but it is a test domain). Is your master server connected and data collection is happening regularly?

You can maybe try and add

AND NOT DATEDIFF(day,UTCBigIntToNomTime(domain_image.expirationTime), GETDATE()) <= 0

Couldn't test this so not sure if my logic with NOT and the <=0 is correct.

Let me know.

[LeonardoT]

Hi Riaan!!!

Thanks again for your help!!!

My Master Server is connected and data collection is ok!

I changed a little bit your idea I ran:

(UTCBigIntToNomTime(domain_imagefragment.expirationTime)) >= now()

It seems worked for me, I´m testing.

Well so far so good... Like I wrote before we used VIEWS to list specifics clients so I´ll be very appreciate if you help me to include in this select an way to list this clients that are included in VIEWS.

There is another request from the client to "by pass" 1 month. In other words, in some VIEWS, instead of start from now on I need start 1 month after and sum total size of these clients.

Thanks!!!!

att.

Tominaga

[RiaanBadenhorst]

Here you go (minus the expiration date check)

select
domain_jobimage.clientname as "Client",
sum(domain_imagefragment.size) as "Size"
from view_node, view_tree, domain_imagefragment, domain_jobimage, domain_image
where
view_tree.name like '%test%' and view_tree.type in (8)
and
domain_jobimage.clientid=view_node.entityid
and
domain_jobimage.imageid=domain_imagefragment.imageid
and
domain_image.id=domain_jobimage.imageid
group by "Client"

[LeonardoT]

Riaan,

I don´t know why but the output shows all client instead of only the clients that belongs in specific view.

Regards

Tominaga

[RiaanBadenhorst · Accepted solution]

Try this one. I added another VIEW (only had 1) and tested again. Each view has different amount of clients. Seems ok now.

select
domain_jobimage.clientname as "Client",
sum(domain_imagefragment.size) as "Size"
from view_node, view_tree, domain_jobimage, domain_imagefragment
where
view_node.treeid=view_tree.id
and
domain_jobimage.clientid=view_node.entityid
and
domain_jobimage.imageid=domain_imagefragment.imageid
and
view_tree.name like '%riaan%' and view_tree.type in (8)
group by
"Client"

[LeonardoT]

Hi Riaan,

I´m thakful for you help!!!!
All of your suggestions have been exploited!!!

It help a create the SELECT below:

SELECT     
        domain_Client.name AS "Client Name",
        (CONVERT(DECIMAL(10,2),SUM(CAST(domain_imageFragment.size AS DECIMAL (10,2))/1024/1024))) AS "Size in GB"
    FROM domain_client
        INNER JOIN domain_jobImage         ON domain_client.name = domain_jobImage.clientname
        INNER JOIN domain_imagefragment ON domain_jobImage.imageid=domain_imageFragment.imageId
WHERE (UTCBigIntToNomTime(domain_imagefragment.expirationTime)) >= DATEADD(month, 1, getdate()) AND
      domain_client.id in (SELECT entityId FROM view_node WHERE treeId = ID NUMBER)
GROUP by "Client Name"
WITH ROLLUP

[RiaanBadenhorst]

Glad to help!