Bug 1465107

The requirement makes perfect sense to me, but the proposed solution does not. Templates are virt entities, and they may have disks on several storage domains, or even several copies of the the same disk on different domains. Looking for the space consumers from the template main tab is the wrong idea, IMHO.

If the concern is the consumed space, the right place to look for the chief offenders should be the disks. There are currently two entry points. Unfortunately, both have some functionality missing

1. The Disks main tab. 
 - allows searching according to the storage domain's parameters (e.g., name)
 - does not display the actual size (seems like an easy win to add it)
 - displays the vm/template the disk is attached to and its type, but does not allow searching/sorting by it.

2. The Disks subtab under a storage domain.
 - The storage domain is implied (by selecting it in the maintab)
 - displays the actual size and allows sorting by it
 - displays the vm/template the disk is attached to and its type, but does not allow searching/sorting by it. 
 - subtabs, unfortunately, don't have a search function, nor a multi-teired sorting. However, if most of the VMs are thinly provisioned on top of templates, chances are that the largest disks on the storage domain would be the base templates anyway.

Mauro - if using either of the aforementioned options makes sense to you, we'll go ahead and analyze the amount of work to close the gaps mentioned there, but offhand, with the exception of the multi-tiered sorting which is a system-wide effort, nothing here looks too complicated to implement.

Mauro - did you try and look at the dashboard (see attachment) ?

Created attachment 1292585 [details]
top VMs in RHEV.TLV internal setup

User can use the following query against the engine db to get the top utilized templates.

SELECT a.vm_name AS template_name, a.vm_guid AS template_id, a.template_version_number,
    a.template_version_name,
    SUM(disk_image_dynamic.actual_size / 1048576) AS vm_disk_actual_size_mb,
    a._create_date AS create_date
FROM vm_static a
  JOIN vm_device ON a.vm_guid = vm_device.vm_id
  JOIN images i ON i.image_group_id = vm_device.device_id
  JOIN disk_image_dynamic ON disk_image_dynamic.image_id = i.image_guid
WHERE a.entity_type = 'TEMPLATE'
GROUP BY template_name, template_id, create_date
ORDER BY vm_disk_actual_size_mb desc, template_name, template_id;

He can also send the query to a file:

Copy (
SELECT a.vm_name AS template_name, a.vm_guid AS template_id, a.template_version_number,
    a.template_version_name,
    SUM(disk_image_dynamic.actual_size / 1048576) AS vm_disk_actual_size_mb,
    a._create_date AS create_date
FROM vm_static a
  JOIN vm_device ON a.vm_guid = vm_device.vm_id
  JOIN images i ON i.image_group_id = vm_device.device_id
  JOIN disk_image_dynamic ON disk_image_dynamic.image_id = i.image_guid
WHERE a.entity_type = 'TEMPLATE'
GROUP BY template_name, template_id, create_date
ORDER BY vm_disk_actual_size_mb desc, template_name, template_id
) To '/tmp/test.csv' With CSV DELIMITER ',';

Can you please add this to KB?

As per Comment 11, this information is not useful in the dashboard.

The SQL query from Comment 15 could be exposed as an additional field in the templates view, but would be quite challenging to make it sortable, because in order to make the sort useful, it must be using the SearchQuery.

In order to make this available in a SearchQuery, the result of the SQL query would have to be added to the "vm_templates_base_view" or similar which might be a performance hit.

Leaving this to virt to decide.

I believe the customer should create a report in the new DWH solution shipped with RHV 4.2.

As I understand this is already possible. I don't think it is sensible having this information in the dashboard, but having it as a kibana report sounds sensible.

So if this is not yet possible in 4.2 please reopen this BZ against the new reporting engine.

Thanks!
Martin

(In reply to Martin Tessun from comment #30)
> I believe the customer should create a report in the new DWH solution
> shipped with RHV 4.2.
> 
> As I understand this is already possible. I don't think it is sensible
> having this information in the dashboard, but having it as a kibana report
> sounds sensible.
> 
> So if this is not yet possible in 4.2 please reopen this BZ against the new
> reporting engine.
> 
> Thanks!
> Martin

This is not what the metrics store is for and we are not planning to add this info to that.
If you want a current state sorting, this is something the WebAdmin should provide.
The query we provided was against the engine database, not DWH.

Ok. I thought metrics store would cover vDisk sizes (realsize).

@Michal: Adding "Actual Size" to the overview (as it already is stored in the DB) should be easily possible then.

BZ<2>Jira Resync