Shirly Radco has uploaded a new change for review. Change subject: reports: br11b report - added comments to queries ......................................................................
reports: br11b report - added comments to queries Changed the code structure so it will be more readable and added commets. Change-Id: I7f0f4752333b8886a10acb216c2f0913e254342f Signed-off-by: Shirly Radco <[email protected]> --- M packaging/ovirt-reports/resources/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_jrxml.data 1 file changed, 403 insertions(+), 269 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-reports refs/changes/44/27844/1 diff --git a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_jrxml.data b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_jrxml.data index f9a7104..d002b2b 100644 --- a/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_jrxml.data +++ b/packaging/ovirt-reports/resources/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_jrxml.data @@ -1,272 +1,406 @@ <?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="BR11B" language="groovy" pageWidth="832" pageHeight="585" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="832" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error"> - <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/five_most_utilized_vms_over_time_br11"/> - <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"> - <parameterDescription><![CDATA[Period]]></parameterDescription> - <defaultValueExpression><![CDATA[0]]></defaultValueExpression> - </parameter> - <parameter name="P_Start_Date" class="java.util.Date"> - <defaultValueExpression><![CDATA["2000-01-01"]]></defaultValueExpression> - </parameter> - <parameter name="P_DataCenter_ID" class="java.lang.String"> - <parameterDescription><![CDATA[Select a DataCenter]]></parameterDescription> - <defaultValueExpression><![CDATA["11111111-1111-1111-1111-111111111111"]]></defaultValueExpression> - </parameter> - <parameter name="P_Cluster_ID" class="java.lang.String"> - <parameterDescription><![CDATA[Select a Cluster]]></parameterDescription> - <defaultValueExpression><![CDATA["11111111-1111-1111-1111-111111111111"]]></defaultValueExpression> - </parameter> - <parameter name="P_VM_Type" class="java.lang.Integer"> - <parameterDescription><![CDATA[Select Virtual Machine Type]]></parameterDescription> - <defaultValueExpression><![CDATA[-1]]></defaultValueExpression> - </parameter> - <parameter name="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> - <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> - <defaultValueExpression><![CDATA["C:\\Program Files\\jasperserver-pro-3.7\\"]]></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 distinct $P!{calendar_column} as the_date, coalesce(vm_name,max(vm_name) over (partition by 1),'') as vm_name, CPU_Usage, MEM_Usage -from (SELECT v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id, - CASE - WHEN delete_date IS NULL THEN vm_name - ELSE vm_name || ' (Removed on ' || cast(cast(delete_date as date) as varchar) || ')' - END as vm_name, - v3_5_statistics_vms_resources_usage_$P!{table_name}.history_datetime AS time, - SUM(coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.max_cpu_usage, 0)*coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status,0))/SUM(coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status,0)) AS CPU_Usage, - SUM(coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.max_memory_usage,0)*coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status,0))/SUM(coalesce(v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status,0)) AS MEM_Usage +<!-- 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="BR11B" language="groovy" pageWidth="832" pageHeight="585" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="832" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="2e32e352-cf44-4a9f-830f-f04834f90aa4"> + <property name="ireport.jasperserver.reportUnit" value="/reports_resources/embedded_reports/Trends_Dashboard/five_most_utilized_vms_over_time_br11"/> + <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/Trends_Dashboard/five_most_utilized_vms_over_time_br11_files/five_most_utilized_vms_over_time_br11_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"> + <parameterDescription><![CDATA[Period]]></parameterDescription> + <defaultValueExpression><![CDATA[0]]></defaultValueExpression> + </parameter> + <parameter name="P_Start_Date" class="java.util.Date"> + <defaultValueExpression><![CDATA["2000-01-01"]]></defaultValueExpression> + </parameter> + <parameter name="P_DataCenter_ID" class="java.lang.String"> + <parameterDescription><![CDATA[Select a DataCenter]]></parameterDescription> + <defaultValueExpression><![CDATA["11111111-1111-1111-1111-111111111111"]]></defaultValueExpression> + </parameter> + <parameter name="P_Cluster_ID" class="java.lang.String"> + <parameterDescription><![CDATA[Select a Cluster]]></parameterDescription> + <defaultValueExpression><![CDATA["11111111-1111-1111-1111-111111111111"]]></defaultValueExpression> + </parameter> + <parameter name="P_VM_Type" class="java.lang.Integer"> + <parameterDescription><![CDATA[Select Virtual Machine Type]]></parameterDescription> + <defaultValueExpression><![CDATA[-1]]></defaultValueExpression> + </parameter> + <parameter name="REPORT_NAME" class="java.lang.String" isForPrompting="false"/> + <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false"> + <defaultValueExpression><![CDATA["C:\\Program Files\\jasperserver-pro-3.7\\"]]></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[-- BR4B - This report returns the +-- cpu and memory usage over time +-- for the 5 most utilized vms. + +SELECT DISTINCT + $P!{calendar_column} as the_date, + COALESCE ( + vm_name, + MAX ( vm_name ) over ( partition by 1 ), + '' + ) AS vm_name, + CPU_Usage, + MEM_Usage +FROM ( + SELECT + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id, + CASE + WHEN delete_date IS NULL + THEN vm_name + ELSE + vm_name + || + ' (Removed on ' + || + CAST ( + CAST ( delete_date AS date ) + AS varchar + ) + || + ')' + END AS vm_name, + v3_5_statistics_vms_resources_usage_$P!{table_name}.history_datetime AS time, + SUM ( + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.max_cpu_usage, + 0 + ) * + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ) / + SUM ( + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ) AS CPU_Usage, + SUM ( + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.max_memory_usage, + 0 + ) * + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ) / + SUM ( + COALESCE ( + v3_5_statistics_vms_resources_usage_$P!{table_name}.minutes_in_status, + 0 + ) + ) AS MEM_Usage + -- If "Period" equals to "Daily" then "table_name" parameter equals to "hourly" else "daily" FROM v3_5_statistics_vms_resources_usage_$P!{table_name} - INNER JOIN v3_5_configuration_history_vms - ON (v3_5_configuration_history_vms.vm_id = v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id) - WHERE v3_5_statistics_vms_resources_usage_$P!{table_name}.history_datetime >= cast($P{P_Start_Date} as timestamp) - AND v3_5_statistics_vms_resources_usage_$P!{table_name}.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_vms.history_id in (SELECT max(a.history_id) - FROM v3_5_configuration_history_vms as a - GROUP BY a.vm_id) - AND v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_status = 1 - AND v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id IN - ( -- Calculation of Top 5 VMs - SELECT a.vm_id - FROM v3_5_statistics_vms_resources_usage_$P!{table_name} a - INNER JOIN v3_5_configuration_history_vms b - ON (a.vm_id = b.vm_id) - WHERE a.vm_status = 1 - AND b.cluster_id in (SELECT v3_5_configuration_history_clusters.cluster_id - FROM v3_5_configuration_history_clusters - WHERE v3_5_configuration_history_clusters.datacenter_id = cast($P{P_DataCenter_ID} as uuid)) - AND b.cluster_id = CASE $P{P_Cluster_ID} - WHEN '11111111-1111-1111-1111-111111111111' then b.cluster_id - ELSE cast($P{P_Cluster_ID} as uuid) - END - AND b.vm_type = CASE $P{P_VM_Type} - WHEN -1 THEN b.vm_type - WHEN NULL THEN b.vm_type - ELSE $P{P_VM_Type} - END - AND b.history_id in (SELECT max(g.history_id) - FROM v3_5_configuration_history_vms as g - GROUP BY g.vm_id) - AND a.history_datetime >= cast($P{P_Start_Date} as timestamp) - AND a.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 - $P!{is_deleted} - GROUP BY a.vm_id - ORDER BY SUM(coalesce(a.max_memory_usage*a.minutes_in_status,0))/SUM(coalesce(a.minutes_in_status,0)) + - SUM(coalesce(a.max_cpu_usage*a.minutes_in_status,0))/SUM(coalesce(a.minutes_in_status,0)) DESC - LIMIT 5) - GROUP BY v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id, vm_name, delete_date, history_datetime) a right outer join calendar b on (a.time = b.$P!{calendar_column}) -where $P!{calendar_column} >= cast($P{P_Start_Date} as date) - AND $P!{calendar_column} <= 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 -order by $P!{calendar_column}]]> - </queryString> - <field name="the_date" class="java.sql.Timestamp"/> - <field name="vm_name" class="java.lang.String"/> - <field name="cpu_usage" class="java.math.BigDecimal"/> - <field name="mem_usage" class="java.math.BigDecimal"/> - <group name="Cluster"> - <groupExpression><![CDATA[$P{P_Cluster_ID}]]></groupExpression> - <groupFooter> - <band height="202"> - <timeSeriesChart> - <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.PercentTimelineChartCustomizer" theme="ReportsLineBarChartTheme"> - <reportElement mode="Opaque" x="2" y="1" width="410" height="200"/> - <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> - <font fontName="SansSerif"/> - <titleExpression><![CDATA[$R{br11b.chart.vms.cpu.usage}]]></titleExpression> - </chartTitle> - <chartSubtitle/> - <chartLegend/> - <hyperlinkParameter name="_report"> - <hyperlinkParameterExpression><![CDATA["/Reports/Trend/Virtual_machines/five_most_utilized_vms_over_time_br11"]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="is_deleted"> - <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></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_VM_Type"> - <hyperlinkParameterExpression><![CDATA[$P{P_VM_Type}]]></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> - </chart> - <timeSeriesDataset timePeriod="Hour"> - <timeSeries> - <seriesExpression><![CDATA[$F{vm_name}]]></seriesExpression> - <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> - <valueExpression><![CDATA[$F{cpu_usage}]]></valueExpression> - <itemHyperlink> - <hyperlinkTooltipExpression><![CDATA[$F{vm_name} + ", " + $F{cpu_usage}.setScale(2, RoundingMode.HALF_UP).toString()]]></hyperlinkTooltipExpression> - </itemHyperlink> - </timeSeries> - </timeSeriesDataset> - <timeSeriesPlot isShowShapes="false"> - <plot labelRotation="-45.0"/> - <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> - <timeAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font isBold="true"/> - </labelFont> - </axisFormat> - </timeAxisFormat> - <valueAxisLabelExpression><![CDATA[$R{axis.cpu.usage.percent}]]></valueAxisLabelExpression> - <valueAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font fontName="SansSerif"/> - </labelFont> - <tickLabelFont> - <font fontName="SansSerif"/> - </tickLabelFont> - </axisFormat> - </valueAxisFormat> - </timeSeriesPlot> - </timeSeriesChart> - <timeSeriesChart> - <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.PercentTimelineChartCustomizer" theme="ReportsLineBarChartTheme"> - <reportElement mode="Opaque" x="421" y="1" width="410" height="200"/> - <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> - <font fontName="SansSerif"/> - <titleExpression><![CDATA[$R{br11b.chart.vms.memory.usage}]]></titleExpression> - </chartTitle> - <chartSubtitle/> - <chartLegend/> - <hyperlinkParameter name="_report"> - <hyperlinkParameterExpression><![CDATA["/Reports/Trend/Virtual_machines/five_most_utilized_vms_over_time_br11"]]></hyperlinkParameterExpression> - </hyperlinkParameter> - <hyperlinkParameter name="is_deleted"> - <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></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_VM_Type"> - <hyperlinkParameterExpression><![CDATA[$P{P_VM_Type}]]></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> - </chart> - <timeSeriesDataset timePeriod="Hour"> - <timeSeries> - <seriesExpression><![CDATA[$F{vm_name}]]></seriesExpression> - <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> - <valueExpression><![CDATA[$F{mem_usage}]]></valueExpression> - <itemHyperlink> - <hyperlinkTooltipExpression><![CDATA[$F{vm_name} + ", " + $F{mem_usage}.setScale(2, RoundingMode.HALF_UP).toString()]]></hyperlinkTooltipExpression> - </itemHyperlink> - </timeSeries> - </timeSeriesDataset> - <timeSeriesPlot isShowShapes="false"> - <plot labelRotation="-45.0"/> - <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> - <timeAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font isBold="true"/> - </labelFont> - </axisFormat> - </timeAxisFormat> - <valueAxisLabelExpression><![CDATA[$R{axis.memory.usage.percent}]]></valueAxisLabelExpression> - <valueAxisFormat> - <axisFormat labelColor="#666666" verticalTickLabels="false"> - <labelFont> - <font fontName="SansSerif"/> - </labelFont> - <tickLabelFont> - <font fontName="SansSerif"/> - </tickLabelFont> - </axisFormat> - </valueAxisFormat> - </timeSeriesPlot> - </timeSeriesChart> - </band> - </groupFooter> - </group> - <title> - <band height="20"> - <textField> - <reportElement x="1" y="0" width="831" height="20" forecolor="#3A5E75"/> - <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> - <font fontName="SansSerif" size="16" isBold="true"/> - </textElement> - <textFieldExpression><![CDATA[$R{br11b.title}]]></textFieldExpression> - </textField> - </band> - </title> + INNER JOIN v3_5_configuration_history_vms + ON ( + v3_5_configuration_history_vms.vm_id = + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id + ) + WHERE + v3_5_statistics_vms_resources_usage_$P!{table_name}.history_datetime >= + CAST ( $P{P_Start_Date} as TIMESTAMP ) + AND v3_5_statistics_vms_resources_usage_$P!{table_name}.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 vms configuration + AND v3_5_configuration_history_vms.history_id IN ( + SELECT MAX ( a.history_id ) + FROM v3_5_configuration_history_vms AS a + GROUP BY a.vm_id + ) + -- Here we get only active vms + AND v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_status = 1 + -- Here we get the top 5 most utilized vms + AND v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id IN ( + SELECT a.vm_id + FROM v3_5_statistics_vms_resources_usage_$P!{table_name} AS a + INNER JOIN v3_5_configuration_history_vms AS b + ON ( a.vm_id = b.vm_id ) + WHERE + -- Here we get only active vms + a.vm_status = 1 + -- Here we filter by the datacenter chosen by the user + AND b.cluster_id IN ( + SELECT v3_5_configuration_history_clusters.cluster_id + FROM v3_5_configuration_history_clusters + WHERE + v3_5_configuration_history_clusters.datacenter_id = + CAST ( $P{P_DataCenter_ID} as uuid ) + ) + -- Here we filter by the cluster chosen by the user + AND b.cluster_id = + CASE $P{P_Cluster_ID} + WHEN '11111111-1111-1111-1111-111111111111' + THEN b.cluster_id + ELSE CAST ( $P{P_Cluster_ID} as uuid ) + END + -- Here we filter by the vms type chosen by the user + AND b.vm_type = + CASE $P{P_VM_Type} + WHEN -1 + THEN b.vm_type + WHEN NULL + THEN b.vm_type + ELSE $P{P_VM_Type} + END + -- Here we get the latest vms configuration + AND b.history_id IN ( + SELECT MAX ( g.history_id ) + FROM v3_5_configuration_history_vms as g + GROUP BY g.vm_id + ) + AND a.history_datetime >= CAST ( $P{P_Start_Date} as TIMESTAMP ) + AND a.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 + -- 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 a.vm_id + ORDER BY + SUM ( + COALESCE ( + a.max_memory_usage * a.minutes_in_status, + 0 + ) + ) / + SUM ( + COALESCE ( + a.minutes_in_status, + 0 + ) + ) + + SUM ( + COALESCE ( + a.max_cpu_usage * a.minutes_in_status, + 0 + ) + ) / + SUM ( + COALESCE ( + a.minutes_in_status, + 0 + ) + ) DESC + LIMIT 5 + ) + GROUP BY + v3_5_statistics_vms_resources_usage_$P!{table_name}.vm_id, + vm_name, + delete_date, + history_datetime +) AS a + RIGHT OUTER JOIN calendar b + ON ( + a.time = b.$P!{calendar_column} + ) +WHERE $P!{calendar_column} >= CAST ( $P{P_Start_Date} as date ) + AND $P!{calendar_column} < + 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 +ORDER BY $P!{calendar_column}]]> + </queryString> + <field name="the_date" class="java.sql.Timestamp"/> + <field name="vm_name" class="java.lang.String"/> + <field name="cpu_usage" class="java.math.BigDecimal"/> + <field name="mem_usage" class="java.math.BigDecimal"/> + <group name="Cluster"> + <groupExpression><![CDATA[$P{P_Cluster_ID}]]></groupExpression> + <groupFooter> + <band height="202"> + <timeSeriesChart> + <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.PercentTimelineChartCustomizer" theme="ReportsLineBarChartTheme"> + <reportElement mode="Opaque" x="2" y="1" width="410" height="200" uuid="62de6abe-8985-4ba1-b296-dbfaf53fcf6f"/> + <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> + <font fontName="SansSerif"/> + <titleExpression><![CDATA[$R{br11b.chart.vms.cpu.usage}]]></titleExpression> + </chartTitle> + <chartSubtitle/> + <chartLegend/> + <hyperlinkParameter name="_report"> + <hyperlinkParameterExpression><![CDATA["/Reports/Trend/Virtual_machines/five_most_utilized_vms_over_time_br11"]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="is_deleted"> + <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></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_VM_Type"> + <hyperlinkParameterExpression><![CDATA[$P{P_VM_Type}]]></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> + </chart> + <timeSeriesDataset timePeriod="Hour"> + <timeSeries> + <seriesExpression><![CDATA[$F{vm_name}]]></seriesExpression> + <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> + <valueExpression><![CDATA[$F{cpu_usage}]]></valueExpression> + <itemHyperlink> + <hyperlinkTooltipExpression><![CDATA[$F{vm_name} + ", " + $F{cpu_usage}.setScale(2, RoundingMode.HALF_UP).toString()]]></hyperlinkTooltipExpression> + </itemHyperlink> + </timeSeries> + </timeSeriesDataset> + <timeSeriesPlot isShowShapes="false"> + <plot labelRotation="-45.0"/> + <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> + <timeAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font isBold="true"/> + </labelFont> + </axisFormat> + </timeAxisFormat> + <valueAxisLabelExpression><![CDATA[$R{axis.cpu.usage.percent}]]></valueAxisLabelExpression> + <valueAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font fontName="SansSerif"/> + </labelFont> + <tickLabelFont> + <font fontName="SansSerif"/> + </tickLabelFont> + </axisFormat> + </valueAxisFormat> + </timeSeriesPlot> + </timeSeriesChart> + <timeSeriesChart> + <chart evaluationTime="Report" hyperlinkType="ReportExecution" hyperlinkTarget="Blank" customizerClass="com.ovirt.reports.jasper.PercentTimelineChartCustomizer" theme="ReportsLineBarChartTheme"> + <reportElement mode="Opaque" x="421" y="1" width="410" height="200" uuid="0d1475a1-bb3b-40e5-a6ff-10b0d66f3cdd"/> + <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> + <font fontName="SansSerif"/> + <titleExpression><![CDATA[$R{br11b.chart.vms.memory.usage}]]></titleExpression> + </chartTitle> + <chartSubtitle/> + <chartLegend/> + <hyperlinkParameter name="_report"> + <hyperlinkParameterExpression><![CDATA["/Reports/Trend/Virtual_machines/five_most_utilized_vms_over_time_br11"]]></hyperlinkParameterExpression> + </hyperlinkParameter> + <hyperlinkParameter name="is_deleted"> + <hyperlinkParameterExpression><![CDATA[$P{is_deleted}]]></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_VM_Type"> + <hyperlinkParameterExpression><![CDATA[$P{P_VM_Type}]]></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> + </chart> + <timeSeriesDataset timePeriod="Hour"> + <timeSeries> + <seriesExpression><![CDATA[$F{vm_name}]]></seriesExpression> + <timePeriodExpression><![CDATA[$F{the_date}]]></timePeriodExpression> + <valueExpression><![CDATA[$F{mem_usage}]]></valueExpression> + <itemHyperlink> + <hyperlinkTooltipExpression><![CDATA[$F{vm_name} + ", " + $F{mem_usage}.setScale(2, RoundingMode.HALF_UP).toString()]]></hyperlinkTooltipExpression> + </itemHyperlink> + </timeSeries> + </timeSeriesDataset> + <timeSeriesPlot isShowShapes="false"> + <plot labelRotation="-45.0"/> + <timeAxisLabelExpression><![CDATA[$R{axis.date}]]></timeAxisLabelExpression> + <timeAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font isBold="true"/> + </labelFont> + </axisFormat> + </timeAxisFormat> + <valueAxisLabelExpression><![CDATA[$R{axis.memory.usage.percent}]]></valueAxisLabelExpression> + <valueAxisFormat> + <axisFormat labelColor="#666666" verticalTickLabels="false"> + <labelFont> + <font fontName="SansSerif"/> + </labelFont> + <tickLabelFont> + <font fontName="SansSerif"/> + </tickLabelFont> + </axisFormat> + </valueAxisFormat> + </timeSeriesPlot> + </timeSeriesChart> + </band> + </groupFooter> + </group> + <title> + <band height="20"> + <textField> + <reportElement x="1" y="0" width="831" height="20" forecolor="#3A5E75" uuid="5b3598c8-4ead-4513-8052-bfd3803dde65"/> + <textElement textAlignment="Center" verticalAlignment="Middle" markup="none"> + <font fontName="SansSerif" size="16" isBold="true"/> + </textElement> + <textFieldExpression><![CDATA[$R{br11b.title}]]></textFieldExpression> + </textField> + </band> + </title> </jasperReport> -- To view, visit http://gerrit.ovirt.org/27844 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I7f0f4752333b8886a10acb216c2f0913e254342f 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
