Yaniv Dary has posted comments on this change. Change subject: reports:Fixed bug in br18-chart vms with known OS vs. unknown ......................................................................
Patch Set 4: (11 comments) http://gerrit.ovirt.org/#/c/24981/4/packaging/ovirt-reports/resources/Reports/Executive/active_vms_by_os_br18_files/active_vms_by_os_br18_jrxml.data File packaging/ovirt-reports/resources/Reports/Executive/active_vms_by_os_br18_files/active_vms_by_os_br18_jrxml.data: Line 3: <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="BR18" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" whenNoDataType="AllSectionsNoDetail" columnWidth="832" leftMargin="5" rightMargin="5" topMargin="5" bottomMargin="5" resourceBundle="ovirt_reports_bundle" whenResourceMissingType="Error" uuid="94affc6e-e7dd-42b9-9529-60ec67a6c3e6"> Line 4: <property name="ireport.jasperserver.url" value="http://localhost:8080/jasperserver-pro/"/> Line 5: <property name="ireport.jasperserver.report.resource" value="/Reports/Executive/active_vms_by_os_br18_files/active_vms_by_os_br18_jrxml"/> Line 6: <property name="ireport.jasperserver.reportUnit" value="/Reports/Executive/active_vms_by_os_br18"/> Line 7: <subDataset name="dataset1" uuid="dbe64307-d7db-4ba9-925f-efc01d783cef"> would rename the dataset to something meaningful Line 8: <parameter name="is_deleted" class="java.lang.String"> Line 9: <defaultValueExpression><![CDATA["AND delete_date IS NULL"]]></defaultValueExpression> Line 10: </parameter> Line 11: <parameter name="P_Period" class="java.lang.Short"> Line 39: Line 40: -- The query has 2 parts. One is to get VMs data and the other is to get the vms data. Line 41: -- For each part of the query there are 2 INNER JOINS: Line 42: -- 1. From the statistics table --> to the configurations table by id - To get vm configurations. Line 43: -- 2. From the statistics table --> to the configurations table by history_id (allias "latest") . same as below in main query Line 44: -- When history_id is the maximum this join is used to get the latest configurations of the vm. Line 45: Line 46: SELECT DISTINCT Line 47: /* If "Period" equals to "Daily" then */ Line 136: THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 year' Line 137: END Line 138: AND Line 139: /* This will determine where deleted entities will be included in the report, */ Line 140: /* according to the user selection for "is_deleted" parameter */ remove trailing whitespaces Line 141: CASE Line 142: WHEN $P{is_deleted} like 'AND%' Line 143: THEN latest.delete_date IS NULL Line 144: ELSE Line 227: -- The query has 2 parts. One is to get VMs data and the other is to get the vms data. Line 228: -- For each part of the query there are 2 INNER JOINS: Line 229: -- 1. From the statistics table --> to the configurations table by id - To get vm configurations. Line 230: -- 2. From the statistics table --> to the configurations table by history_id (allias "latest") . Line 231: -- When history_id is the maximum this join is used to get the latest configurations of the vm. same Line 232: Line 233: SELECT DISTINCT Line 234: /* If "Period" is "Daily" then */ Line 235: /* the "calendar_column" parameter is equal to "the_datetime" else "the_date" */ Line 319: latest.delete_date IS NOT NULL Line 320: END Line 321: GROUP BY os_type, history_datetime Line 322: ) Line 323: as windows_query I would change this name Line 324: RIGHT OUTER JOIN calendar Line 325: ON ( windows_query.time = calendar.$P!{calendar_column} ) Line 326: WHERE $P!{calendar_column} >= cast ( $P{P_Start_Date} as date ) Line 327: AND $P!{calendar_column} <= Line 376: <queryString language="SQL"> Line 377: <![CDATA[-- BR18 - This query will return the total number of active VMs, Line 378: -- Count by distributions of Windows OS versions. Line 379: Line 380: -- The query has 2 parts. One is to get VMs data and the other is to get the vms data. This sentence doesn't make sense. One is to get vm config data over time and the other is to get the latest vm config data for filtering. Line 381: -- For each part of the query there are 2 INNER JOINS: Line 382: -- 1. From the statistics table --> to the configurations table by id - To get vm configurations. Line 383: -- 2. From the statistics table --> to the configurations table by history_id (allias "latest") . Line 384: -- When history_id is the maximum this join is used to get the latest configurations of the vm. Line 377: <![CDATA[-- BR18 - This query will return the total number of active VMs, Line 378: -- Count by distributions of Windows OS versions. Line 379: Line 380: -- The query has 2 parts. One is to get VMs data and the other is to get the vms data. Line 381: -- For each part of the query there are 2 INNER JOINS: what do you mean by each part? It's a single query. Line 382: -- 1. From the statistics table --> to the configurations table by id - To get vm configurations. Line 383: -- 2. From the statistics table --> to the configurations table by history_id (allias "latest") . Line 384: -- When history_id is the maximum this join is used to get the latest configurations of the vm. Line 385: Line 380: -- The query has 2 parts. One is to get VMs data and the other is to get the vms data. Line 381: -- For each part of the query there are 2 INNER JOINS: Line 382: -- 1. From the statistics table --> to the configurations table by id - To get vm configurations. Line 383: -- 2. From the statistics table --> to the configurations table by history_id (allias "latest") . Line 384: -- When history_id is the maximum this join is used to get the latest configurations of the vm. This is exactly the other way around history_id is joined to get the config history and IDs are used when history id doesn't matter and you want to get a single config value to join on. Line 385: Line 386: SELECT DISTINCT Line 387: /* If "Period" is "Daily" then */ Line 388: /* the "calendar_column" parameter is equal to "the_datetime" else "the_date" */ Line 412: v3_4_configuration_history_vms.operating_system Line 413: AND enum_os_type.enum_type = 'OS_TYPE' Line 414: AND language_code = 'en_US' Line 415: ) Line 416: INNER JOIN v3_4_configuration_history_vms latest I would use lastest_config Line 417: ON ( Line 418: latest.vm_id = Line 419: v3_4_statistics_vms_resources_usage_$P!{table_name}.vm_id Line 420: ) Line 418: latest.vm_id = Line 419: v3_4_statistics_vms_resources_usage_$P!{table_name}.vm_id Line 420: ) Line 421: WHERE v3_4_statistics_vms_resources_usage_$P!{table_name}.vm_status = 1 Line 422: AND UPPER ( enum_os_type.value ) like 'WIND%' I would change this to '%WINDOWS%' Line 423: AND v3_4_configuration_history_vms.cluster_id in Line 424: ( Line 425: SELECT cluster_id Line 426: FROM v3_4_configuration_history_clusters Line 460: THEN CAST ( $P{P_Start_Date} AS TIMESTAMP ) + interval '1 year' Line 461: END Line 462: AND Line 463: /* This will determine where deleted entities will be included in the report, */ Line 464: /* according to the user selection for "is_deleted" parameter */ /**/ is used to put comment across several line, no need to this for each line. Please remove trailing spaces in second line. Line 465: CASE Line 466: WHEN $P{is_deleted} like 'AND%' Line 467: THEN latest.delete_date IS NULL Line 468: ELSE -- To view, visit http://gerrit.ovirt.org/24981 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: comment Gerrit-Change-Id: Icdecb4e0bc4ac4ce5d6ab2000aa0812aa3ea5c2c Gerrit-PatchSet: 4 Gerrit-Project: ovirt-reports Gerrit-Branch: master Gerrit-Owner: Shirly Radco <[email protected]> Gerrit-Reviewer: Shirly Radco <[email protected]> Gerrit-Reviewer: Yaniv Dary <[email protected]> Gerrit-HasComments: Yes _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
