November 2024 - Dashboard Release Notes

A new system variable `START_OF_FINANCIAL_YEAR_MONTH` has been introduced to the dashboard that defines a month in which a financial year begins. This variable can be set in the Global Variables tab, within the Dashboard Setting screen;

The new field represented the the starting month from which the Financial Year will begin, accepting values from 1 to 12, where the value of 1 represents January and 12 for December.

This `START_OF_FINANCIAL_YEAR_MONTH` System Variable will drive the values available in the new ‘Financial’ tab in the popup panel, for the date range category object type.

The month name in ‘Your Financial Periods run from xx’ can be translated by adding its translated value to the messagesData.properties file. For example, in messagesData_fr_FR.properties file: January=Janvier.

The ‘From’ and ‘To’ for the selected option (e.g. ‘This Quarter’, ‘Next Year’) will be calculated based on the entry for `START_OF_FINANCIAL_YEAR_MONTH`. For example, if you enter 5 (May) for `START_OF_FINANCIAL_YEAR_MONTH` then 'Next Year' will be From: 2025-05-01 to 2026-04-30.

The same calculations for variable replacement based on `START_OF_FINANCIAL_YEAR_MONTH` definition can also be accessed by using the new financial magic variables:

- START_OF_CURRENT_FINANCIAL_QUARTER
- END_OF_CURRENT_FINANCIAL_QUARTER
- START_OF_LAST_FINANCIAL_QUARTER
- END_OF_LAST_FINANCIAL_QUARTER
- START_OF_NEXT_FINANCIAL_QUARTER
- END_OF_NEXT_FINANCIAL_QUARTER
- START_OF_CURRENT_FINANCIAL_YEAR
- END_OF_CURRENT_FINANCIAL_YEAR
- START_OF_LAST_FINANCIAL_YEAR
- END_OF_LAST_FINANCIAL_YEAR
- START_OF_NEXT_FINANCIAL_YEAR
- END_OF_NEXT_FINANCIAL_YEAR

Added an option to enable Java Agent in order to collect and export telemetry data (metrics, traces, logs) for observability and performance monitoring. Further information and examples of the setup can be found in the following link - Monitoring - Using Java Agent. The Java Agent can be enabled in two way, either through the Configuration-Tool-GUI or by Environment Variables.

Environment Variables

Java Agent can be configured through the following environment variables:

• PI_TOMCAT_MONITORING_ENABLED

• PI_TOMCAT_MONITORING_OTLP_EXPORTER_ENDPOINT

• PI_TOMCAT_MONITORING_OTLP_EXPORTER_PROTOCOL

• PI_TOMCAT_MONITORING_SERVICE_NAME

Find more info about the new environment variables - /wiki/spaces/DEV/pages/2010218497

Configuration Tool

This can also be configured using configuration-tool gui:

A new tab has been added to the data connections screen that allows you to configure an SSH Tunnel.

It includes:

1. A checkbox to allow you to include the SSH tunnel as part of your connection

2. The SSH user name

3. The tunnel that the port lives on

4. A field for the private key (note: this will not be visible once it has already been saved, will be encrypted, and will not be visible when accessing a data connection from the API, and will be hidden in the JSON export file)

5. A local port - you will need to set the port on the ‘details’ tab to be the same as this

6. A target port - where your database lives on the target system

7. A target host - here your database lives on the target system

If the checkbox is checked, and any fields are missing, you will not be allowed to save the data connection, and will be presented with a warning on the inputs which are missing.

If the connection is successful, and you are using the SSH tunnel, an additional indicator will be included on the details tab to confirm this.

You can only have one SSH tunnel per data connection. And each tunnel port must be unique to the system.

The SSH tunnel will be instantiated on data connection save, and will persist until you save it again with the box un-checked. A tunnel will also be created briefly upon introspection. All relevant tunnels will also be created on system start up.

All of these fields have been added to the API for you to set.

A thread will run in the background every 10 minutes to check for any closed connections, and re-open them again if they have failed.

You can set the ssh private key with a global variable, this is the only variable support we currently have for this feature. Only secure variables are supported.

We are making Initial steps towards moving away from bundling all of the supported drivers, and instead having many of them be downloaded only for those who are using them. This functionality has initially only been applied to the ClickHouse and SQLite JDBC drivers in this release - further changes around this will be included in future release notes.

Alongside the existing/original driver directories, we have introduced a new directory called ‘dynamic_drivers’. This new directory contains a JSON file that holds details related to the supported versions of the aforementioned drivers - in the example below, we have used ClickHouse;

This block of JSON code, details the files that are needed when using a connection via the listed Driver Class Path. In this example, both the JDBC JAR and an accompanying compression library. The dynamic downloading of these files occurs if they do not already exist in the ‘dynamic_drivers/com.clickhouse.jdbc.ClickHouseDriver’ directory or fail validation checks due to a SHA-256 mismatch:

• On startup ONLY if you have a ClickHouse Data Connection defined

• When you Introspect or Save a ClickHouse Data Connection

• When you try to load a Chart that uses a ClickHouse Data Connection

• Via a thread that runs every 3 minutes to sync defined Data Connections and their Dynamic Drivers if applicable

Previously, a hardcoded default was used for the email claim name that would attempt to link a SAML login response and a Dashboard user. This default ‘XMLSoap’ value will still be used as a last resort but you now have the ability to define a custom email claim name in the SAML section of the Global Variables screen.

Additionally, there is also a field for a custom Usercode claim name which rather than attempting to match to a user’s email address will match based upon their Dashboard Usercode (Username). If the usercode claim name is defined or the default (see below) manages to extract a value from the SAML response then the Email claim will be ignored even if provided.

The defaulting behaviour of these claims is as follows:

Usercode:

• If defined the Custom value will be used. Should it fail to retrieve a value or the retrieved value does not match a Dashboard User’s Usercode then login will fail

• Our namespaced default claim of “https://www.panintelligence.com/claims/usercode“ will be attempted. If it fails to return a value or match a Dashboard User then login will fail

Email:

• If defined the Custom value will be used. Should it fail to retrieve a value or the retrieved value does not match a Dashboard User’s Email then login will fail

• Our namespaced default claim of “/wiki/spaces/DEV/pages/1582238068 will be attempted. If it fails to return a value the final fallback will be attempted

• The final fallback for Email is the ‘XMLSoap’ claim that was previously the only claim used. If this fails to retrieve a value or match a Dashboard User’s Email then login will fail

When users are embedding a Category, we have added the ability to add temporary category objects via a post message. Below is an example HTML and JS file you would use to configure this.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <div id="parent-div"></div>
    <script src="tempcatobjpm.js" type="text/javascript"></script>
</body>
</html>

const url = "http://localhost:8080/pi/?lang=en_GB&categoryMode=0#/dashboard-system/#/redirectToDefaultCategoryState/34";
const iframe = document.createElement("iframe");
iframe.setAttribute("src", url);
iframe.style.width = "750px";
iframe.style.height = "750px";
document.getElementById('parent-div').appendChild(iframe);
window.addEventListener("message", (event) => {
    if (event.data && event.data === "canAddTempCategoryObjects") {
        const postMesssageJson = {
            type: "add-temp-category-objects",
            data: {
                categoryObjects: [
                    {
                        orgId: 1,
                        dataSourceItemIdentifier: "Dashboard Repos",
                        columnIdentifier: "Chart Name"
                    },
                    {
                        orgId: 1,
                        dataSourceItemIdentifier: "Dashboard Repos",
                        columnIdentifier: "Audit Type",
                        dynamicAttribute: "hello"
                    }
                ],
                messageIdentifier: "cat1"
            }
        }
        iframe.contentWindow.postMessage(postMesssageJson, url);
    }
}, false);
window.addEventListener("message", (event) => {
    if (event.data && event.data.message === "tempCategoryObjectsAdded" && event.data.messageIdentifier === "cat1") {
        const newHash = "PA-CA%23%23%23category%2F34%2Fc-o-p%3D34__0%2F*%24*eq__%5B%5BAudit%20Date%5D%5D%7B%7B--%20All%20--!%7C%23%7C--%20ALL%20VALUES%20ARE%20SELECTED%20--%7D%7D%2F*%24*eq__%5B%5BChart%20Name%5D%5D%7B%7BChart%20Types%7D%7D%2F*%24*eq__%5B%5BAudit%20Type%5D%5D%7B%7B--%20All%20--%7D%7D";
        const newUrl = url.split('#')[0] + '#' + newHash;
        iframe.src = newUrl;
    }
})

Considerations

1. Ensure the you wait for the event ‘canAddTempCategoryObjects’ before attempting to make this post message, this will ensure the data stores required are loaded

2. Ensure the ‘type’ param is always ‘add-temp-category-objects’

3. Each category object requires an org id, ‘dataSourceItemIdentifier '- which is the display name of the data source, and 'columnIdentifier’ - which is the display name of the object

4. You can add an optional 4th param which is 'dynamicAttribute' which allows this to work with custom fields

5. The ‘messageIdentifier’ is used to link the post message to the next event listener in the JS example, ensuring that you apply the correct hash to the correct category

6. In the 2nd event listener, you are waiting for the category objects to be added before you update the URL hash, which will allow you to populate the category objects - alter the ‘newHash’ variable to match the data within your dashboard

As part of our drive to support infrastructure as code, and dev ops ways of working, we have added the ability to create a branch of a full setup including connection, categories, charts etc. Once created, you can use import-export to migrate live content into this isolated environment and work on it, then extract it again later to move to production.

You can now have the Scheduler log in JSON format. This can be achieved by checking the JSON Format box in the Configuration-Tool GUI, or by setting the SCHEDULER_LOGGING_JSON Environment Variable to true.

This setting, the other logging settings, and the JWT Key Path setting have also been added to the Configuration-Tool GUI. - Default level changed to INFO Default level changed to INFO

You can now have the Pirana log in JSON format. This can be achieved by checking the JSON Format box in the Configuration-Tool GUI, , or by setting the PIRANA_LOGGER_JSON Environment Variable to true.

You can also now successfully set the appropriate log level, with it defaulting to INFO.

You can use the “PIRANA_LOG_LEVEL" variable to set the log level, either to “ERROR“, “WARN”, “DEBUG” or “INFO”.

You can set the “PIRANA_LOGGER_JSON" variable to either “true” or “false”, to enable JSON logs.

In rare and unforeseen scenarios, an exception can occur when attempts to save a domain class to the Dashboard Repository fails, despite passing validation checks. Additional handling and logging has been introduced to help diagnose these rare issues should they reoccur.

New columns have been added to the database, to track the last update time and the user responsible for modifications to data connections, categories, and charts.

To remove several OS package vulnerabilities in the Pirana container we have updated to the latest stable Debian build - bookworm.

To resolve vulnerability CVE-2024-38808 Spring Framework has been upgraded to 5.3.39

Go has been upgraded to version 1.23.1 to resolve the following vulnerabilities: CVE-2024-34158, CVE-2024-34155, CVE-2024-34156.

Removed ‘Beta’ mode from Drill to API chart type.

Our greatly enhanced, rebuilt word exports for single charts have been enabled by default in this release. If you experience any issue please let us know, for a limited time you can switch back to the old mode by using the beta flag OLD_WORD_EXPORT.

Our multi-tenancy organisations feature has been enabled for all users. If you are unsure of the implications reach out to us for assistance.

Based on load testing we’ve updated some of our default settings for Tomcat to ones we believe will offer better out-of-the-box performance in many cases. Particularly important is the max threads setting (PI_TOMCAT_MAX_THREADS which defaults to 50), if your usage is heavily CPU-bound you may improve performance by lowering that which reduces the amount of work being handled concurrently. If your usage is heavily IO-bound; for example waiting for database queries to complete you may improve performance by raising that setting, increasing the amount of work being handled concurrently. We believe the new defaults will perform better for many people but you are are able to override those if you prefer the old or any other values.

Upgraded from 3.34.2.1 to resolved some vulnerabilities. Please be aware, this Driver is a part of the ‘Dynamic Drivers’ feature added in this release.

Tomcat upgraded to 9.0.97 to resolve CVE-2024-52316 vulnerability.

MySQL JDBC driver upgraded to 8.4.0 to resolve CCVE-2023-22102 vulnerability.

Resolved an issue in regards to tooltips and data labels on charts where certain values would not correctly round up. I.e. 0.15, when 1 decimal was applied, would round to 0.1 instead of 0.2. This is due to underlying behaviour with the JavaScript language itself. This has been resolved, and any edge cases of 0.5s rounding down instead of up have been fixed.

 Fixed a bug where auto scaling did not work with bullet points with card charts. Previously, they would spill over the edge of the chart, now, they fit as normal.

Before:

After:

The variable types listed above should now be correctly replaced in a Schedules Subject and Body fields as well as within any Reports and Exports attached to the Schedule.

When applying i18n translations to category objects, the search functionality will now operate on translated values rather than the base language.

This will also apply to the chart and category names in the chart library.

The non-translated value of x-axis values on numerous charts were being used to determine whether or not the value should be truncated. In the cases were truncation occurred, the truncated version of the non-translated value would also be displayed rather than the translated value. These issues have been resolved.

It was previously possible for the ‘process_date’ of a Schedule Job to be erroneously set to the current time during an upgrade from any version prior to 2023_02. The migration that could cause this has been identified, modified to resolve the issue, and a more robust fix to remove this behaviour has also been implemented (check Schema Changes section below for details)

The following permissions are not relevant for a Subscription and therefore were not able to be set when creating one: ‘Can Logout', ‘Can Access Account’, and ‘Can Change Own Password'. This combined with the inability to edit permissions on other user’s that you do not have meant that a Subscribed Admin would be unable to set these values for a 'Main’ user.

Now, rather than hiding these permission when dealing with a Subscription they are shown with a warning icon and tooltip addition explaining that whilst they are not relevant to the Subscription they will dictate that Subscription’s ability to edit those permissions on Users they can manage within that Organisation.

Upgraded the tomcat embed library within the dashboard from version 9.0.89 to version 9.0.91, fixing a vulnerability.

Previously, there was a bug where the vertical alignment option on card charts wouldn’t work if auto scaling had been turned off. This has now been fixed.

Previously, there was an unnecessary amount of additional white space within the category object panel when it was pinned to the top of the category. Now, so long as you do not have one of the following filter types: Checkboxes, Images or Sliders - the panel will vertically shrink to reduce the white space.

Previously, when attempting to log in with a JWT, when a already logged in with a user on the same browser, the dashboard would disregard the new attempt at logging in and retain the session of the previous user. Now, whether you’ve already logged in via a JWT or other means, as soon as you log in with a 2nd user, the 1st user’s session will be overridden.

Fixed upgrade_dashboard.sh script to make sure it accepts Tomcat in versions 8 or higher. This check is used to make sure the dashboard is in a valid state to be upgraded.

Resolved an issue with the data connection import/export functionality. Previously, if a table had color filters defined with text color set but no background color specified, exporting the data connection would omit this filter in the original JSON file. As a result, upon import, it would be detected as a change from the original content and it would override the existing color filter within the table.

Fixed an issue where the user was not able to import a data connection into a child organisation due to incorrect data source item being resolved. This now takes into consideration orgId and also user’s access to the parent data connection if the child data connection is being imported.

Fixed an issue where the dashboard repository user defaulted to ROOT. It has been updated to use pi_repos_use, a user with 'read' privileges only, to restrict unnecessary access to information schema tables.

Previously, drilling down on null values in the chart did not include the null value in the SQL query, causing the chart to display all values upon drilldown. Now, when drilling into null values, the null value is correctly added to the URL hash with the appropriate operator, ensuring the SQL query resolves accurately.

Previously, browser single-chart exports failed when the dashboard fell back to the default theme because it couldn’t correctly resolve the logo image. This issue could occur, for instance, if the existing theme had been deleted. This has now been resolved with improved error handling, ensuring that if the default theme is used, the dashboard can successfully include the default logo in the export file.

Remove ‘on update’ behaviour of pi_schedule_jobs ‘process_date’ column

scheduler#69

‘updatedAt’ and ‘updatedBy’ columns added to mis_categories, mis_defined_charts and mis_data_sources tables

pi#2472, pi#2611, pi#2612

'orgType' column added to mis_organisations

pi#2621

category_id index added to mis_definedCharts

pi#2712

schedule_id index added to pi_schedule_attachments

pi#2713

Changed the default value from ‘0000-00-00 00:00:00’ to ‘2000-01-01’ for:

• mis_dashboard_jobs : START_DATE_TIME

• mis_dashboard_jobs : END_DATE_TIME

• mis_external_files : TABLES_LAST_UPDATED

migration#58

Removed timestamp “on update” behaviour from:

• mis_external_files

• user_gateway_sessions

• people

• mis_audits

• mis_password_reset_keys

• mis_service_watch

• mis_dashboard_jobs

• pi_schedule_jobs

• mis_tarpit

• pi_rate_limit_record

• mis_defined_charts

• passwords

• users

migration#58