Shirly Radco has uploaded a new change for review. Change subject: reports: dr20 report - added comments to queries ......................................................................
reports: dr20 report - added comments to queries Changed the code structure so it will be more readable and added commets. Change-Id: I97c21c05180f5cba93d54bd8b18d9c32ba5ef186 Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml.data 1 file changed, 60 insertions(+), 14 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/81/27881/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml.data b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml.data index 2bb69ca..cd29335 100644 --- a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml.data +++ b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml.data @@ -1,22 +1,68 @@ <?xml version="1.0" encoding="UTF-8"?> +<!-- Created with Jaspersoft Studio version 5.5.0--> <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"; name="top_five_ha_virtual_servers_downtime_dr20" language="groovy" pageWidth="445" pageHeight="260" whenNoDataType="AllSectionsNoDetail" columnWidth="445" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="e0eb81f3-d737-4129-9276-1d8f22d88873"> <property name="ireport.zoom" value="1.0"/> <property name="ireport.x" value="0"/> <property name="ireport.y" value="0"/> - <property name="ireport.jasperserver.reportUnit" value="/reports_resources/webadmin_dashboards/ce/cluster_dashboard"/> - <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/services/repository"/> - <parameter name="P_Cluster_ID" class="java.lang.String"> - <defaultValueExpression><![CDATA[]]></defaultValueExpression> - </parameter> - <queryString> - <![CDATA[SELECT v3_5_latest_configuration_vms.vm_id, v3_5_latest_configuration_vms.vm_name, - (SUM(CASE WHEN v3_5_statistics_vms_resources_usage_samples.vm_status = 1 THEN coalesce(v3_5_statistics_vms_resources_usage_samples.minutes_in_status,0) ELSE 0 END) / SUM(v3_5_statistics_vms_resources_usage_samples.minutes_in_status)) * 100 as uptime_percent, - (SUM(CASE WHEN v3_5_statistics_vms_resources_usage_samples.vm_status in (0,2) THEN coalesce(v3_5_statistics_vms_resources_usage_samples.minutes_in_status,0) ELSE 0 END) / SUM(v3_5_statistics_vms_resources_usage_samples.minutes_in_status)) * 100 AS planned_downtime_percent + <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/> + <property name="ireport.jasperserver.report.resource" value="/reports_resources/embedded_reports/webadmin_dashboards/cluster_dashboard/jrxmls/top_five_ha_virtual_servers_downtime_dr20_jrxml"/> + <parameter name="P_Cluster_ID" class="java.lang.String"/> + <queryString language="SQL"> + <![CDATA[-- DR20 - This query returns for 5 high availability vms, +-- that have the least uptime percent, +-- the total uptime and planned downtime percent. + +SELECT + v3_5_latest_configuration_vms.vm_id, v3_5_latest_configuration_vms.vm_name, + ( + SUM ( + CASE + WHEN v3_5_statistics_vms_resources_usage_samples.vm_status = 1 + THEN + COALESCE ( + v3_5_statistics_vms_resources_usage_samples.minutes_in_status, + 0 + ) + ELSE 0 + END + ) / + SUM ( + v3_5_statistics_vms_resources_usage_samples.minutes_in_status + ) + ) * 100 + AS uptime_percent, + ( + SUM ( + CASE + WHEN v3_5_statistics_vms_resources_usage_samples.vm_status IN ( 0, 2 ) + THEN + COALESCE ( + v3_5_statistics_vms_resources_usage_samples.minutes_in_status, + 0 + ) + ELSE 0 + END + ) / + SUM ( + v3_5_statistics_vms_resources_usage_samples.minutes_in_status + ) + ) * 100 + AS planned_downtime_percent FROM v3_5_statistics_vms_resources_usage_samples - INNER JOIN v3_5_latest_configuration_vms ON (v3_5_latest_configuration_vms.vm_id = v3_5_statistics_vms_resources_usage_samples.vm_id) -WHERE v3_5_latest_configuration_vms.cluster_id = cast($P{P_Cluster_ID} as UUID) - AND v3_5_latest_configuration_vms.high_availability = TRUE -GROUP BY v3_5_latest_configuration_vms.vm_id, v3_5_latest_configuration_vms.vm_name + INNER JOIN v3_5_latest_configuration_vms + ON ( + v3_5_latest_configuration_vms.vm_id = + v3_5_statistics_vms_resources_usage_samples.vm_id + ) +WHERE + -- Here we filter by the cluster chosen by the user + v3_5_latest_configuration_vms.cluster_id = + CAST ( $P{P_Cluster_ID} as UUID ) + -- Here we get only high availability vms + AND v3_5_latest_configuration_vms.high_availability = TRUE +GROUP BY + v3_5_latest_configuration_vms.vm_id, + v3_5_latest_configuration_vms.vm_name ORDER BY uptime_percent ASC LIMIT 5]]> </queryString> @@ -28,7 +74,7 @@ <band height="260" splitType="Stretch"> <stackedBarChart> <chart evaluationTime="Report" customizerClass="com.ovirt.reports.jasper.DrStackedBarChartCustomizer" theme="WebadminLineBarChartTheme"> - <reportElement uuid="2d1bfb2e-f032-43dd-b852-e8b3e15c0a41" mode="Transparent" x="0" y="0" width="445" height="260"/> + <reportElement mode="Transparent" x="0" y="0" width="445" height="260" uuid="2d1bfb2e-f032-43dd-b852-e8b3e15c0a41"/> <chartTitle> <font fontName="SansSerif" size="10"/> <titleExpression><![CDATA[$R{dr20.title}]]></titleExpression> -- To view, visit http://gerrit.ovirt.org/27881 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I97c21c05180f5cba93d54bd8b18d9c32ba5ef186 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
