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 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 to collect and export telemetry data (metrics, traces, logs) for observability and performance monitoring. Further information and examples of the setup can be found HERE. 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

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 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 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 can 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

This was organically going to be delivered as a Javascript message. You an now add a temporary category filter by manipulating the url.

Here's how a temporary category filter is presented on the URL:

• it has to be after normal category filters

  • this implicitly means they have to be on the last category if a category is a child category

• it has to have the data connection element

  • the presence of the data connection name decides that a category filter is a temporary category filter

  • url format for normal category filter: [[Departments+cf=Name]]

  • url format for temporary category filter: [[Sales+obj=Departments+cf=Name]]

  • +obj= is the delimiter between the data connection name and the object display name

• this temporary category filter cannot be a duplicate

  • the same object display name and custom field dynamic attribute should not be present

  • the data connection element does not matter, if display name and dynamic attribute combination is present, you cannot have a temp category object with the same combination

Example:

I have a page with normal category object in place.

The url used is built up as follows; (This example is built using a Custom Fields cf table and objects)

#PA-CA###category/44/c-o-p=44__0/*$*in__[[Custom_field_value+cf=Dependants]]{{0::::1}}

Now to find out how the url is to be coded - I am going to manually add a temporary category object using the + icon in the top right hand corner;

And add an object and make sure the filter is applied;

Then if I open developer tools, and type hash() in the console (You may need to clear it.

Then I can now see the URL.

So my url with the temporary category object now looks like;

#PA-CA###category/44/c-o-p=44__0/*$in__[[Custom_field_value+cf=Dependants]]{{0::::1}}/$*eq__[[Custom Fields+obj=Custom_field_value+cf=Probation]]{{Yes}}

It’s construction is almost identical to the existing category object item except it contains a data connection name. So it is made up as follows.

The simplest way to find this construction, is to add through the interface and inspect as above.

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.

This is our intial drop of DevOps functionality however we intend to build on this further, and therefore we would appreciate your feedback on this, so that we can refine and extend the functionality in this area. For more information,

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. More information on these changes can be found in the Schema section at the end of the release note.

Our multi-tenancy Organisations feature has now been enabled for all users, however will not be initiated unless you add a new Organisation to your repository. Many of our customers have already started to use Organisations whilst it was in BETA mode, and therefore for those, no changes are needed to continue to use this functionality.

Using Organisations in full, can be a notable change dependant on your specific use case for multi tenancy, and therefore before doing so, we always recommend that you reach out to your Customer Success Manager to discuss how we can best support you on this journey. Making sure we understand your use case allows us to provide you with the best possible advice before you explore the Organisations features.

Based on load testing results, we have updated some of the default settings for Tomcat to ensure we offer a 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 performance can be improved by lowering this setting - which reduces the amount of work being handled concurrently.

If your usage is heavily IO-bound; for example, waiting for database queries to complete, performance can be improved by increasing this setting - which increases the amount of work being handled concurrently. We believe the new default settings will perform better for many people; however, you do have the option to override them should you prefer the old, or indeed any other values.

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

Removed ‘Beta’ mode from the Microsoft Word exports for single charts - this is now enabled by default.

The Pirana container has been updated to the latest stable Debian build (bookworm), to address several OS package vulnerabilities.

The Spring framework has been updated to 5.3.39, to address the CVE-2024-38808 vulnerability.

The Go libraries have been updated to 1.23.1, to address the following vulnerabilities: CVE-2024-34158, CVE-2024-34155 & CVE-2024-34156.

The SQLite driver has been updated to 3.46.1.3 to address several vulnerabilities.

Tomcat has been updated to 9.0.97, to address the CVE-2024-52316 vulnerability.

The MySQL JDBC driver has been updated to 8.4.0, to address the CVE-2023-22102 vulnerability.

The Tomcat Embed library has been updated to 9.0.91, to address a vulnerability.

Tooltips and Data Labels were showing incorrect values, on certain values that had not been rounded correctly. For example, 0.15 (when 1 decimal was applied) would round to 0.1 instead of 0.2. This was due to underlying behaviour with the JavaScript language itself, which has been resolved. Any edge cases of 0.5s rounding down instead of up have been addressed.

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

Addressed an issue where auto scaling did not work with bullet points within a Card Chart. Previously, they would spill over the edge of the chart, now, they fit as normal.

When applying i18n translations to Category Objects, the search functionality will now operate on translated values rather than the base language. This also applies to the chart and category names in the chart library.

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.

The non-translated value of x-axis values on numerous charts were being used to determine whether 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.

It was previously possible for the ‘process_date’ of a Schedule Job to be erroneously set to the current time during an upgrade, when upgrading from any version prior to 2023_02.

The User and Role variable types are now correctly replaced in a Schedules Subject and Body fields, as well as within any Reports and Exports attached to the Schedule.

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’

• ‘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 - which explains 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.

Previously, when attempting to log in with a JWT, when an 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 have 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.

The Linux upgrade script (upgrade_dashboard.sh) has been amended to ensure its allows Tomcat versions of 8 or higher. This check is used to validate that the dashboard is in a valid state to be upgraded.

Previously, if a table had colour filters defined with text colour set (but no background colour specified), exporting the data connection would omit this filter in the original JSON file. As a result, when attempting to import, it would be detected as a change from the original content and it would override the existing colour filter within the table.

Resolved 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 user’s access to the parent data connection if the child data connection is being imported.

Resolved 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 could not correctly resolve the logo image - this issue could occur if the existing theme had been deleted. This has now been addressed with improved error handling, ensuring that if the default theme is used, the dashboard can successfully include the default logo in the export file.

Previously, single chart PDF exports with a default chart colour set (written in capitals), would always display black as the default chart colour instead. Now, the default chart colour is applied correctly regardless of the case used.

JDBC drivers are now loaded into and from a custom classloader, which reduces the chance of class or library clashes. Drivers are loaded on the first connection attempt and reused, eliminating the need for the previously used late load directory to avoid slow startup

Translations of Filter Values has been addressed, to ensure these now translate for all Category Object Replacements, Chart Filters, Drill Down Filters, Parent Filters and Temporary Filters.

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