Dear all, Is it possible to pull a list of all VMS who are in vlanX? Kind regards, Koen
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@gmail.com> 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
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 <sradco@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@gmail.com> 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
This is a multi-part message in MIME format. --------------080603080902070300070309 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: 7bit 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@redhat.com <mailto:sradco@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@gmail.com <mailto:vanoppen.koen@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: ydary@redhat.com IRC : ydary --------------080603080902070300070309 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: 8bit <html> <head> <meta content="text/html; charset=windows-1252" http-equiv="Content-Type"> </head> <body bgcolor="#FFFFFF" text="#000000"> <br> <br> <div class="moz-cite-prefix">On 03/18/2015 02:06 PM, Koen Vanoppen wrote:<br> </div> <blockquote cite="mid:CACfY+MY9DaiULwGdaVdRL_Y9gucskiO33VTMW4GM+CncJ8dYHw@mail.gmail.com" type="cite"> <div dir="ltr"> <div>Thanks!!<br> </div> 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... :-)<br> </div> </blockquote> <br> You need to run this against the db. Use PSQL or pgAdmin.<br> <br> <blockquote cite="mid:CACfY+MY9DaiULwGdaVdRL_Y9gucskiO33VTMW4GM+CncJ8dYHw@mail.gmail.com" type="cite"> <div class="gmail_extra"><br> <div class="gmail_quote">2015-03-16 13:29 GMT+01:00 Shirly Radco <span dir="ltr"><<a moz-do-not-send="true" href="mailto:sradco@redhat.com" target="_blank">sradco@redhat.com</a>></span>:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Koen,<br> <br> I believe you can use this query:<br> <br> SELECT v3_5_latest_configuration_hosts_interfaces.vlan_id,<br> v3_5_latest_configuration_hosts.host_id,<br> v3_5_statistics_vms_resources_usage_samples.vm_id<br> FROM v3_5_latest_configuration_hosts_interfaces<br> LEFT JOIN v3_5_latest_configuration_hosts ON<br> v3_5_latest_configuration_hosts_interfaces.host_id =<br> v3_5_latest_configuration_hosts.host_id<br> LEFT JOIN v3_5_statistics_vms_resources_usage_samples ON<br> v3_5_latest_configuration_hosts.history_id =<br> v3_5_statistics_vms_resources_usage_samples.current_host_configuration_version<br> LEFT JOIN v3_5_latest_configuration_vms ON<br> v3_5_latest_configuration_vms.history_id =<br> v3_5_statistics_vms_resources_usage_samples.vm_configuration_version<br> LEFT JOIN v3_5_latest_configuration_vms_interfaces ON<br> v3_5_latest_configuration_vms.history_id =<br> v3_5_latest_configuration_vms_interfaces.vm_configuration_version<br> WHERE v3_5_statistics_vms_resources_usage_samples.vm_status = 1<br> AND v3_5_latest_configuration_hosts_interfaces.vlan_id IS NOT NULL<br> AND v3_5_latest_configuration_vms_interfaces.logical_network_name =<br> v3_5_latest_configuration_hosts_interfaces.logical_network_name<br> GROUP BY v3_5_latest_configuration_hosts_interfaces.vlan_id,<br> v3_5_latest_configuration_hosts.host_id,<br> v3_5_statistics_vms_resources_usage_samples.vm_id<br> ORDER BY v3_5_latest_configuration_hosts_interfaces.vlan_id,<br> v3_5_latest_configuration_hosts.host_id,<br> v3_5_statistics_vms_resources_usage_samples.vm_id<br> <br> If you need more details please let me know.<br> <br> Best regards,<br> ---<br> Shirly Radco<br> BI Software Engineer<br> Red Hat Israel Ltd.<br> <div> <div class="h5"><br> <br> ----- Original Message -----<br> > From: "Koen Vanoppen" <<a moz-do-not-send="true" href="mailto:vanoppen.koen@gmail.com">vanoppen.koen@gmail.com</a>><br> > To: <a moz-do-not-send="true" href="mailto:users@ovirt.org">users@ovirt.org</a><br> > Sent: Friday, March 13, 2015 9:17:29 AM<br> > Subject: [ovirt-users] DWH Question<br> ><br> > Dear all,<br> ><br> > Is it possible to pull a list of all VMS who are in vlanX?<br> ><br> > Kind regards,<br> ><br> > Koen<br> ><br> </div> </div> > _______________________________________________<br> > Users mailing list<br> > <a moz-do-not-send="true" href="mailto:Users@ovirt.org">Users@ovirt.org</a><br> > <a moz-do-not-send="true" href="http://lists.ovirt.org/mailman/listinfo/users" target="_blank">http://lists.ovirt.org/mailman/listinfo/users</a><br> ><br> </blockquote> </div> <br> </div> <br> <fieldset class="mimeAttachmentHeader"></fieldset> <br> <pre wrap="">_______________________________________________ Users mailing list <a class="moz-txt-link-abbreviated" href="mailto:Users@ovirt.org">Users@ovirt.org</a> <a class="moz-txt-link-freetext" href="http://lists.ovirt.org/mailman/listinfo/users">http://lists.ovirt.org/mailman/listinfo/users</a> </pre> </blockquote> <br> <pre class="moz-signature" cols="72">-- Yaniv Dary Red Hat Israel Ltd. 34 Jerusalem Road Building A, 4th floor Ra'anana, Israel 4350109 Tel : +972 (9) 7692306 8272306 Email: <a class="moz-txt-link-abbreviated" href="mailto:ydary@redhat.com">ydary@redhat.com</a> IRC : ydary</pre> </body> </html> --------------080603080902070300070309--
participants (3)
-
Koen Vanoppen -
Shirly Radco -
Yaniv Dary