Re: [ovirt-users] DWH Question
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 <mailto:sra...@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" mailto:vanoppen.k...@gmail.com>> > To: users@ovirt.org <mailto:users@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@ovirt.org <mailto:Users@ovirt.org> > http://lists.ovirt.org/mailman/listinfo/users > ___ Users mailing list Users@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: yd...@redhat.com IRC : ydary ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
Re: [ovirt-users] DWH Question
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... :-) 2015-03-16 13:29 GMT+01:00 Shirly Radco : > 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" > > To: users@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@ovirt.org > > http://lists.ovirt.org/mailman/listinfo/users > > > ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
Re: [ovirt-users] DWH Question
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" > To: users@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@ovirt.org > http://lists.ovirt.org/mailman/listinfo/users > ___ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
[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@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
