Telemetry Report Query: List all environments with metrics as columns

[john-thomas-dotcms]

Parent Issue

No response

Task

We need to be able to retrieve information about the metrics used by individual environments, with a list of specific metrics to show as columns.

For example, we need to be able to generate a query that will generate data in the following way:

client_name   client_env   ACTIVE_USERS_COUNT   SITES_WITH_INDIVIDUAL_PERMISSIONS   UNIQUE_SUBACTIONS_COUNT
client1       prod         15                   2                                   42
client1       dev          6                    3                                   45
client2       prod         3                    1                                   16

Requirements

1. Show all columns which distinguish customer environments. This includes:
   • client_category
   • client_name
   • client_env
2. Prepend the Feature name to the Metric name in the results.
   • For example, display LANGUAGES-COUNT instead of just COUNT
   • This is actually needed for this query to really do what we need.
     • But if this can't be done (or would be very difficult to do), then we can implement the query without it, and we'll have to build the reports that use the query to de-dup generic metric names (like COUNT).

Nice-to-Have

Show the timestamp of the record each row of data was pulled from as a column. For example:

client_name   client_env   timestamp          ACTIVE_USERS_COUNT   SITES_WITH_INDIVIDUAL_PERMISSIONS   UNIQUE_SUBACTIONS_COUNT
client1       prod         2024-04-20 06:30   15                   2                                   42
client1       dev          2024-04-20 14:30   6                    3                                   45
client2       prod         2024-04-19 14:30   3                    1                                   16

Proposed Objective

Same as Parent Issue

Proposed Priority

Priority 2 - Important

Acceptance Criteria

1. Queries can be written to deliver results in the format shown in the Task description.
2. Queries can be written to either include a list of specific metrics in the results (rather than all metrics).

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

QA not needed (scope outside the dotCMS product).

Sub-Tasks & Estimates

No response

[freddyDOTCMS]

A view called metrics_table_view was created on the Telemetry PROD DataBase

[john-thomas-dotcms]

The metrics_table_view returns all expected columns of data, with the expected column names, in the expected order, with the data in the expected format.

Passed IQA