Shirly Radco has uploaded a new change for review. Change subject: reports: br17b report - added comments to queries ......................................................................
reports: br17b report - added comments to queries Changed the code structure so it will be more readable and added commets. Change-Id: Id07027702d0751d12e82a9c46b82616008d831de Signed-off-by: Shirly Radco <sra...@redhat.com> --- M packaging/ovirt-reports/resources/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml.data 1 file changed, 223 insertions(+), 170 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/35/27835/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml.data b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml.data index 9fe6a65..575ec57 100644 --- a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml.data +++ b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml.data @@ -1,176 +1,229 @@ <?xml version="1.0" encoding="UTF-8"?> -<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="BR17B" language="groovy" pageWidth="358" pageHeight="358" whenNoDataType="AllSectionsNoDetail" columnWidth="358" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error"> - <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/summary_of_host_usage_resources_br17"/> - <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/services/repository"/> - <property name="ireport.zoom" value="1.0"/> - <property name="ireport.x" value="0"/> - <property name="ireport.y" value="0"/> - <parameter name="is_deleted" class="java.lang.String"> - <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression> - </parameter> - <parameter name="P_Period" class="java.lang.Short"> - <defaultValueExpression><![CDATA[0]]></defaultValueExpression> - </parameter> - <parameter name="P_Start_Date" class="java.util.Date"> - <defaultValueExpression><![CDATA["01/01/2000"]]></defaultValueExpression> - </parameter> - <parameter name="P_DataCenter_ID" class="java.lang.String"> - <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> - </parameter> - <parameter name="P_Cluster_ID" class="java.lang.String"> - <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> - </parameter> - <parameter name="table_name" class="java.lang.String"> - <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "hourly" : "daily"]]></defaultValueExpression> - </parameter> - <parameter name="calendar_column" class="java.lang.String"> - <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "the_datetime" : "the_date"]]></defaultValueExpression> - </parameter> - <queryString> - <![CDATA[SELECT - TBL_$P!{table_name}_PEAKS.host_id - ,host_name - ,delete_date - ,cast(AVG(TBL_$P!{table_name}_PEAKS.cpu_peak) as int) AS avg_cpu_peak - ,cast(AVG(TBL_$P!{table_name}_PEAKS.mem_peak) as int) AS avg_mem_peak -FROM ( - -- Calculation of daily cpu and memory usage peaks - SELECT v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, - host_name, - history_datetime, - delete_date, - MAX(coalesce(max_cpu_usage,0)) as cpu_peak, - MAX(coalesce(max_memory_usage,0)) as mem_peak +<!-- 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="BR17B" language="groovy" pageWidth="358" pageHeight="358" whenNoDataType="AllSectionsNoDetail" columnWidth="358" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="b9a7c7e6-1019-4f0a-8046-ebdd9e8564b5"> + <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17"/> + <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/> + <property name="ireport.zoom" value="1.0"/> + <property name="ireport.x" value="0"/> + <property name="ireport.y" value="0"/> + <property name="ireport.jasperserver.report.resource" value="/reports_resources/embedded_reports/System_Overview_Dashboard/summary_of_host_usage_resources_br17_files/summary_of_host_usage_resources_br17_jrxml"/> + <parameter name="is_deleted" class="java.lang.String"> + <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression> + </parameter> + <parameter name="P_Period" class="java.lang.Short"> + <defaultValueExpression><![CDATA[0]]></defaultValueExpression> + </parameter> + <parameter name="P_Start_Date" class="java.util.Date"> + <defaultValueExpression><![CDATA["01/01/2000"]]></defaultValueExpression> + </parameter> + <parameter name="P_DataCenter_ID" class="java.lang.String"> + <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> + </parameter> + <parameter name="P_Cluster_ID" class="java.lang.String"> + <defaultValueExpression><![CDATA["00000000-0000-0000-0000-000000000000"]]></defaultValueExpression> + </parameter> + <parameter name="table_name" class="java.lang.String"> + <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "hourly" : "daily"]]></defaultValueExpression> + </parameter> + <parameter name="calendar_column" class="java.lang.String"> + <defaultValueExpression><![CDATA[$P{P_Period} == 0 ? "the_datetime" : "the_date"]]></defaultValueExpression> + </parameter> + <queryString language="SQL"> + <![CDATA[-- BR17B - This report returns the calculation +-- of daily average cpu and memory usage peaks +-- per host. + +SELECT + TBL_$P!{table_name}_PEAKS.host_id, + host_name, + delete_date, + CAST ( + AVG ( + TBL_$P!{table_name}_PEAKS.cpu_peak + ) AS int + ) AS avg_cpu_peak, + CAST ( + AVG ( + TBL_$P!{table_name}_PEAKS.mem_peak + ) AS int + ) AS avg_mem_peak +FROM ( + SELECT + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, + host_name, + history_datetime, + delete_date, + MAX ( + COALESCE ( + max_cpu_usage, + 0 + ) + ) AS cpu_peak, + MAX ( + COALESCE ( + max_memory_usage, + 0 + ) + ) AS mem_peak + -- If "Period" equals to "Daily" then "table_name" parameter equals to "hourly" else "daily" FROM v3_5_statistics_hosts_resources_usage_$P!{table_name} - INNER JOIN v3_5_configuration_history_hosts - ON (v3_5_configuration_history_hosts.host_id = v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id) + INNER JOIN v3_5_configuration_history_hosts + ON ( + v3_5_configuration_history_hosts.host_id = + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id + ) WHERE - v3_5_configuration_history_hosts.cluster_id in (SELECT cluster_id - FROM v3_5_configuration_history_clusters - Where datacenter_id = cast($P{P_DataCenter_ID} as uuid)) - AND v3_5_configuration_history_hosts.cluster_id = CASE $P{P_Cluster_ID} - WHEN NULL THEN v3_5_configuration_history_hosts.cluster_id - WHEN '11111111-1111-1111-1111-111111111111' THEN v3_5_configuration_history_hosts.cluster_id - ELSE cast($P{P_Cluster_ID} as uuid) - END - AND history_datetime >= cast($P{P_Start_Date} as date) - AND history_datetime <= CASE - WHEN $P{P_Period} = 0 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 day' - WHEN $P{P_Period} = 1 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 month' - WHEN $P{P_Period} = 2 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '3 month' - WHEN $P{P_Period} = 3 THEN CAST($P{P_Start_Date} as TIMESTAMP) + interval '1 year' - END - AND v3_5_configuration_history_hosts.history_id in (SELECT max(a.history_id) - FROM v3_5_configuration_history_hosts a - GROUP BY a.host_id) + -- Here we filter by the datacenter chosen by the user + v3_5_configuration_history_hosts.cluster_id IN ( + SELECT cluster_id + FROM v3_5_configuration_history_clusters + WHERE datacenter_id = CAST ( $P{P_DataCenter_ID} AS uuid ) + ) + -- Here we filter by the cluster chosen by the user + AND v3_5_configuration_history_hosts.cluster_id = + CASE $P{P_Cluster_ID} + WHEN NULL + THEN v3_5_configuration_history_hosts.cluster_id + WHEN '11111111-1111-1111-1111-111111111111' + THEN v3_5_configuration_history_hosts.cluster_id + ELSE CAST ( $P{P_Cluster_ID} AS uuid ) + END + AND history_datetime >= CAST ( $P{P_Start_Date} AS date ) + AND history_datetime < + CASE + WHEN $P{P_Period} = 0 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 day' + WHEN $P{P_Period} = 1 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 month' + WHEN $P{P_Period} = 2 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '3 month' + WHEN $P{P_Period} = 3 + THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 year' + END + -- Here we get the latest hosts configuration + AND v3_5_configuration_history_hosts.history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_5_configuration_history_hosts AS a + GROUP BY a.host_id + ) + -- This will determine if deleted hosts will be included in the report, + -- according to the user selection for "is_deleted" parameter $P!{is_deleted} - GROUP BY v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, host_name, delete_date, history_datetime - ) AS TBL_$P!{table_name}_PEAKS + GROUP BY + v3_5_statistics_hosts_resources_usage_$P!{table_name}.host_id, + host_name, + delete_date, + history_datetime +) AS TBL_$P!{table_name}_PEAKS GROUP BY - TBL_$P!{table_name}_PEAKS.host_id, host_name, delete_date -Order by delete_date DESC, host_id]]> - </queryString> - <field name="host_id" class="java.lang.Object"/> - <field name="host_name" class="java.lang.String"/> - <field name="delete_date" class="java.sql.Timestamp"/> - <field name="avg_cpu_peak" class="java.lang.Integer"/> - <field name="avg_mem_peak" class="java.lang.Integer"/> - <variable name="avg_cpu_peak_1" class="java.lang.Integer" calculation="Sum"> - <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression> - </variable> - <variable name="avg_mem_peak_1" class="java.lang.Integer" calculation="Sum"> - <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> - </variable> - <variable name="avg_mem_peak_2" class="java.lang.Integer" calculation="Sum"> - <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> - </variable> - <variable name="avg_cpu_peak_2" class="java.lang.Integer" calculation="Sum"> - <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression> - </variable> - <variable name="avg_mem_peak_3" class="java.lang.Integer" calculation="Sum"> - <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> - </variable> - <summary> - <band height="358" splitType="Stretch"> - <scatterChart> - <chart isShowLegend="true" evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.ScatterChartCustomizer" renderType="draw" theme="ReportsLineBarChartTheme"> - <reportElement x="0" y="0" width="358" height="358"/> - <box> - <pen lineWidth="1.0" lineColor="#3C617F"/> - <topPen lineWidth="1.0" lineColor="#3C617F"/> - <leftPen lineWidth="1.0" lineColor="#3C617F"/> - <bottomPen lineWidth="1.0" lineColor="#3C617F"/> - <rightPen lineWidth="1.0" lineColor="#3C617F"/> - </box> - <chartTitle color="#3A5E75"> - <font fontName="SansSerif" isBold="true"/> - <titleExpression><![CDATA[$R{br17b.title}]]></titleExpression> - </chartTitle> - <chartSubtitle> - <font fontName="SansSerif"/> - </chartSubtitle> - <chartLegend> - <font fontName="SansSerif"/> - </chartLegend> - <hyperlinkParameter name="_report"> - <hyperlinkParameterExpression><![CDATA["/Reports/Executive/summary_of_host_usage_resources_br17"]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_DataCenter_ID"> - <hyperlinkParameterExpression><![CDATA[$P{P_DataCenter_ID}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Cluster_ID"> - <hyperlinkParameterExpression><![CDATA[$P{P_Cluster_ID}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Period"> - <hyperlinkParameterExpression><![CDATA[$P{P_Period}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="P_Start_Date"> - <hyperlinkParameterExpression><![CDATA[$P{P_Start_Date}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="is_deleted"> - <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></hyperlinkParameterExpression> - </hyperlinkParameter> - </chart> - <xyDataset> - <xySeries> - <seriesExpression><![CDATA[$F{delete_date} == null ? $R{br17.chart.active.hosts} : $R{br17.chart.deleted.hosts}]]></seriesExpression> - <xValueExpression><![CDATA[$F{avg_cpu_peak}]]></xValueExpression> - <yValueExpression><![CDATA[$F{avg_mem_peak}]]></yValueExpression> - <itemHyperlink> - <hyperlinkTooltipExpression><![CDATA[$F{host_name} + " (" + $R{br17.chart.cpu} + ": " + $F{avg_cpu_peak}.toString() + "%, " + $R{br17.chart.memory} + ": " + $F{avg_mem_peak}.toString() + "%)"]]></hyperlinkTooltipExpression> - </itemHyperlink> - </xySeries> - </xyDataset> - <scatterPlot isShowLines="false" isShowShapes="true"> - <plot/> - <xAxisLabelExpression><![CDATA[$R{axis.cpu.usage.percent}]]></xAxisLabelExpression> - <xAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font fontName="SansSerif"/> - </labelFont> - <tickLabelFont> - <font fontName="SansSerif"/> - </tickLabelFont> - </axisFormat> - </xAxisFormat> - <yAxisLabelExpression><![CDATA[$R{axis.memory.usage.percent}]]></yAxisLabelExpression> - <yAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font fontName="SansSerif"/> - </labelFont> - <tickLabelFont> - <font fontName="SansSerif"/> - </tickLabelFont> - </axisFormat> - </yAxisFormat> - <domainAxisMinValueExpression><![CDATA[0]]></domainAxisMinValueExpression> - <domainAxisMaxValueExpression><![CDATA[100]]></domainAxisMaxValueExpression> - <rangeAxisMinValueExpression><![CDATA[0]]></rangeAxisMinValueExpression> - <rangeAxisMaxValueExpression><![CDATA[100]]></rangeAxisMaxValueExpression> - </scatterPlot> - </scatterChart> - </band> - </summary> + TBL_$P!{table_name}_PEAKS.host_id, + host_name, + delete_date +ORDER BY + delete_date DESC, + host_id]]> + </queryString> + <field name="host_id" class="java.lang.Object"/> + <field name="host_name" class="java.lang.String"/> + <field name="delete_date" class="java.sql.Timestamp"/> + <field name="avg_cpu_peak" class="java.lang.Integer"/> + <field name="avg_mem_peak" class="java.lang.Integer"/> + <variable name="avg_cpu_peak_1" class="java.lang.Integer" calculation="Sum"> + <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression> + </variable> + <variable name="avg_mem_peak_1" class="java.lang.Integer" calculation="Sum"> + <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> + </variable> + <variable name="avg_mem_peak_2" class="java.lang.Integer" calculation="Sum"> + <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> + </variable> + <variable name="avg_cpu_peak_2" class="java.lang.Integer" calculation="Sum"> + <variableExpression><![CDATA[$F{avg_cpu_peak}]]></variableExpression> + </variable> + <variable name="avg_mem_peak_3" class="java.lang.Integer" calculation="Sum"> + <variableExpression><![CDATA[$F{avg_mem_peak}]]></variableExpression> + </variable> + <summary> + <band height="358" splitType="Stretch"> + <scatterChart> + <chart isShowLegend="true" evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.ScatterChartCustomizer" renderType="draw" theme="ReportsLineBarChartTheme"> + <reportElement x="0" y="0" width="358" height="358" uuid="5c135387-a02d-4f79-896e-67b7b632cffd"/> + <box> + <pen lineWidth="1.0" lineColor="#3C617F"/> + <topPen lineWidth="1.0" lineColor="#3C617F"/> + <leftPen lineWidth="1.0" lineColor="#3C617F"/> + <bottomPen lineWidth="1.0" lineColor="#3C617F"/> + <rightPen lineWidth="1.0" lineColor="#3C617F"/> + </box> + <chartTitle color="#3A5E75"> + <font fontName="SansSerif" isBold="true"/> + <titleExpression><![CDATA[$R{br17b.title}]]></titleExpression> + </chartTitle> + <chartSubtitle> + <font fontName="SansSerif"/> + </chartSubtitle> + <chartLegend> + <font fontName="SansSerif"/> + </chartLegend> + <hyperlinkParameter name="_report"> + <hyperlinkParameterExpression><![CDATA["/Reports/Executive/summary_of_host_usage_resources_br17"]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_DataCenter_ID"> + <hyperlinkParameterExpression><![CDATA[$P{P_DataCenter_ID}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Cluster_ID"> + <hyperlinkParameterExpression><![CDATA[$P{P_Cluster_ID}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Period"> + <hyperlinkParameterExpression><![CDATA[$P{P_Period}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="P_Start_Date"> + <hyperlinkParameterExpression><![CDATA[$P{P_Start_Date}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="is_deleted"> + <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></hyperlinkParameterExpression> + </hyperlinkParameter> + </chart> + <xyDataset> + <xySeries> + <seriesExpression><![CDATA[$F{delete_date} == null ? $R{br17.chart.active.hosts} : $R{br17.chart.deleted.hosts}]]></seriesExpression> + <xValueExpression><![CDATA[$F{avg_cpu_peak}]]></xValueExpression> + <yValueExpression><![CDATA[$F{avg_mem_peak}]]></yValueExpression> + <itemHyperlink> + <hyperlinkTooltipExpression><![CDATA[$F{host_name} + " (" + $R{br17.chart.cpu} + ": " + $F{avg_cpu_peak}.toString() + "%, " + $R{br17.chart.memory} + ": " + $F{avg_mem_peak}.toString() + "%)"]]></hyperlinkTooltipExpression> + </itemHyperlink> + </xySeries> + </xyDataset> + <scatterPlot isShowLines="false" isShowShapes="true"> + <plot/> + <xAxisLabelExpression><![CDATA[$R{axis.cpu.usage.percent}]]></xAxisLabelExpression> + <xAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font fontName="SansSerif"/> + </labelFont> + <tickLabelFont> + <font fontName="SansSerif"/> + </tickLabelFont> + </axisFormat> + </xAxisFormat> + <yAxisLabelExpression><![CDATA[$R{axis.memory.usage.percent}]]></yAxisLabelExpression> + <yAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font fontName="SansSerif"/> + </labelFont> + <tickLabelFont> + <font fontName="SansSerif"/> + </tickLabelFont> + </axisFormat> + </yAxisFormat> + <domainAxisMinValueExpression><![CDATA[0]]></domainAxisMinValueExpression> + <domainAxisMaxValueExpression><![CDATA[100]]></domainAxisMaxValueExpression> + <rangeAxisMinValueExpression><![CDATA[0]]></rangeAxisMinValueExpression> + <rangeAxisMaxValueExpression><![CDATA[100]]></rangeAxisMaxValueExpression> + </scatterPlot> + </scatterChart> + </band> + </summary> </jasperReport> -- To view, visit http://gerrit.ovirt.org/27835 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Id07027702d0751d12e82a9c46b82616008d831de Gerrit-PatchSet: 1 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <sra...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
