[ovirt-users] DWH Question

[Yaniv Dary]

On 03/18/2015 02:06 PM, Koen Vanoppen wrote:
> Thanks!!
> Only, I can't execute the query.. I added it to the reports as a SQL 
> query, but I can't execute it... I never added a new one before... So 
> maybe that will be the problem... :-)

You need to run this against the db. Use PSQL or pgAdmin.

>
> 2015-03-16 13:29 GMT+01:00 Shirly Radco <sradco at redhat.com 
> <mailto:sradco at redhat.com>>:
>
>     Hi Koen,
>
>     I believe you can use this query:
>
>     SELECT v3_5_latest_configuration_hosts_interfaces.vlan_id,
>         v3_5_latest_configuration_hosts.host_id,
>         v3_5_statistics_vms_resources_usage_samples.vm_id
>     FROM v3_5_latest_configuration_hosts_interfaces
>     LEFT JOIN v3_5_latest_configuration_hosts ON
>         v3_5_latest_configuration_hosts_interfaces.host_id =
>         v3_5_latest_configuration_hosts.host_id
>     LEFT JOIN v3_5_statistics_vms_resources_usage_samples ON
>         v3_5_latest_configuration_hosts.history_id =
>     v3_5_statistics_vms_resources_usage_samples.current_host_configuration_version
>     LEFT JOIN v3_5_latest_configuration_vms ON
>         v3_5_latest_configuration_vms.history_id =
>     v3_5_statistics_vms_resources_usage_samples.vm_configuration_version
>     LEFT JOIN v3_5_latest_configuration_vms_interfaces ON
>         v3_5_latest_configuration_vms.history_id =
>     v3_5_latest_configuration_vms_interfaces.vm_configuration_version
>     WHERE v3_5_statistics_vms_resources_usage_samples.vm_status = 1
>             AND v3_5_latest_configuration_hosts_interfaces.vlan_id IS
>     NOT NULL
>             AND
>     v3_5_latest_configuration_vms_interfaces.logical_network_name =
>     v3_5_latest_configuration_hosts_interfaces.logical_network_name
>     GROUP BY v3_5_latest_configuration_hosts_interfaces.vlan_id,
>         v3_5_latest_configuration_hosts.host_id,
>         v3_5_statistics_vms_resources_usage_samples.vm_id
>     ORDER BY v3_5_latest_configuration_hosts_interfaces.vlan_id,
>         v3_5_latest_configuration_hosts.host_id,
>         v3_5_statistics_vms_resources_usage_samples.vm_id
>
>     If you need more details please let me know.
>
>     Best regards,
>     ---
>     Shirly Radco
>     BI Software Engineer
>     Red Hat Israel Ltd.
>
>
>     ----- Original Message -----
>     > From: "Koen Vanoppen" <vanoppen.koen at gmail.com
>     <mailto:vanoppen.koen at gmail.com>>
>     > To: users at ovirt.org <mailto:users at ovirt.org>
>     > Sent: Friday, March 13, 2015 9:17:29 AM
>     > Subject: [ovirt-users] DWH Question
>     >
>     > Dear all,
>     >
>     > Is it possible to pull a list of all VMS who are in vlanX?
>     >
>     > Kind regards,
>     >
>     > Koen
>     >
>     > _______________________________________________
>     > Users mailing list
>     > Users at ovirt.org <mailto:Users at ovirt.org>
>     > http://lists.ovirt.org/mailman/listinfo/users
>     >
>
>
>
>
> _______________________________________________
> Users mailing list
> Users at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/users

-- 
Yaniv Dary
Red Hat Israel Ltd.
34 Jerusalem Road
Building A, 4th floor
Ra'anana, Israel 4350109

Tel : +972 (9) 7692306
8272306
Email: ydary at redhat.com
IRC : ydary

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/users/attachments/20150322/37ecb295/attachment-0001.html>