Manage Available Reports

Overview

The manage_available_reports.pl program supports adding/removing user-accessible reports in a deployed N2ACD environment. To add new reports (or remove reports) from a N2ACD deployment, use this application first before configuring access to specific reports.

Once a report is added to N2ACD, report security (that is, which users may access which reports) is managed through the N2ACD administration screens.

Note that users may only see the report screen if they are granted the report_read group via the Jarvis configuration file for N2ACD-FE.

This command line tool supports:

• Listing installed reports.
• Adding new reports based on a TOML definition file.
• Removing existing reports previously installed.

While several reports are provided with N2ACD, package installation will not install these automatically. To install product reports, use the definition files installed into /usr/share/n2acd-fe/reports on the node where n2acd-api is installed.

Running manage_available_reports.pl --help will show the program usage.

Manage N2ACD reports.

    Database connection:
    --db-host       = N2ACD database host name or IP address.               Default = localhost
    --db-port       = N2ACD database port.                                  Default = 5432
    --db-name       = N2ACD database name.                                  Default = n2in
    --db-connect    = N2ACD database connection string. Alternative to
                      --db-host / --db-port / --db-name providing full
                      control over the DBI connection string. This may
                      be useful if [for example] connecting to a
                      service defined in ~/.pg_service.conf.
    --db-user       = N2ACD database user name.                             Default = n2acd_owner
    --db-password   = N2ACD database user password. 
                      If not directly specified, credentials specified in ~/.pgpass will be used.

    --jarvis-config = The Jarvis config file for N2ACD-FE.                  Default = /etc/jarvis/n2acd-fe.xml
                      Dataset directory with prefix 'reports.' will be managed by this script.

    Actions available using this script:

    list            = List all existing reports.
    add <report TOML definition>
                    = Create a new report from the TOML file provided.
    remove <report ID>
                    = Remove a report based on ID. Use the list command to get report IDs

Common Options

The following common options may be specified when executing:

The following actions are available when executing this script:

List

To list available reports, use the list command:

./manage_available_reports.pl list

The results of the list command are a CSV formatted output within ---- lines, listing each of the installed reports:

Installed Reports
---------------------------------------------------------------------------
Report ID,Category,Name,Dataset,Fields,Dataset File
28,Usage,Announcement Usage,announcement_usage,entry_id,/var/lib/n2acd-fe/reports/announcement_usage.xml
---------------------------------------------------------------------------

List command output includes the following fields:

Add

To add a new report, use the add command:

./manage_available_reports.pl add /usr/share/n2acd-fe/reports/announcement_usage.toml

If the report is able to be successfully added, the following will be output:

Loaded Report "Usage.Announcement Usage" into the database.
Created new report file /var/lib/n2acd-fe/reports/announcement_usage.xml

Note that the file and report name /category will be unique to the report being loaded.

If the report already exists, then the add command will not error. The report will be reloaded:

Loaded Report "Usage.Announcement Usage" into the database.
Backing up existing report file /var/lib/n2acd-fe/reports/announcement_usage.xml to /var/lib/n2acd-fe/reports/announcement_usage.xml.bak
Created new report file /var/lib/n2acd-fe/reports/announcement_usage.xml

This allows a report to be reloaded into the database as required - with report parameters and the report SQL changing as required.

Remove

To remove an existing report, use the remove command:

./manage_available_reports.pl remove 28

If a report exists with the given Report ID in the databases, it will be removed:

Backing up report file /var/lib/n2acd-fe/reports/announcement_usage.xml to /var/lib/n2acd-fe/reports/announcement_usage.xml.bak
Removed report #28: "Usage/Announcement Usage" (dataset 'announcement_usage' from the system). 

Like the add command, the dataset file holding the report SQL is backed up in case it is necessary. To determine the correct report ID to provide to the remove command, use the list command and use the Report ID column value for the report to be removed.

Report Definition File Format

Each N2ACD report must be defined in a single TOML formatted file. This file is given to the add command when adding/updating the report in N2ACD. An example report is the Announcement Usage report:

# 
# ACD Report: Announcement Usage
# A general report to list all usages of an announcement (by resource ID) to
# which the user has access.
#
category = "Usage"
name = "Announcement Usage"
description = """
    A general report listing all flows that use an announcement, based on the announcement
    name (or partial name).
"""

[parameter-1]
name = "Announcement Name"
field_name = "announcement_name"
description = "The (partial) announcement name. Use a % for a wildcard match on the start or end of the name."
type = "string"
example = "Menu%"

[script]
datasetName = "announcement_usage"
sql = """
        SELECT
            DISTINCT
            c.customer_name AS "Customer Name"
            , c.customer_reference AS "Customer Reference"
            , s.digits AS "Full Service Number"
            , f.flow_name AS "Flow Name"
            , entries.version AS "Version"
            , entries.change_user AS "Created By"
            , to_char(entries.change_date, 'YYYY-MM-DD HH24:MI:SS') AS "Creation Time"
            , entries.announcement_entry_name AS "Announcement Name"
        FROM
            n2acd.s_customer({$__customer_restriction_list}, {$__unrestricted_customer_access}, NULL, TRUE) c
            JOIN n2acd.s_service({$__customer_restriction_list}, {$__unrestricted_customer_access}) s USING (customer_id)
            JOIN n2acd.s_flow({$__customer_restriction_list}, {$__unrestricted_customer_access}) f USING (customer_id)
            JOIN (
                SELECT
                    fv.flow_id, 
                    fv.version,
                    fv.change_user,
                    fv.change_date,
                    c.value #>> ARRAY['entry_id'] AS announcement_entry_name
                FROM (
                    SELECT
                        v.flow_id,
                        v.version,
                        v.change_user,
                        v.change_date,
                        o.value  #> ARRAY['config'] AS config
                    FROM
                        n2acd.s_flow_version({$__customer_restriction_list}, {$__unrestricted_customer_access}) v
                        JOIN json_each(flow_json #> ARRAY['operations']) o ON true
                ) fv
                JOIN json_each(fv.config) c ON true
                WHERE
                    c.key like '%announcement%' AND c.value #>> ARRAY['entry_id'] ilike {$announcement_name}
            ) entries USING (flow_id)
        ORDER BY
            c.customer_name, s.digits, f.flow_name, entries.version;
"""

# If a user doesn't have either admin_read or audit_read, then the data inside the Created By column will be modified.
[[script.remove_columns]]
user_groups = [ "admin_read", "audit_read" ]
columns = [ "Flow Creator" ]

An ACD report has several top-level properties, zero or more parameter sections, and finally a single script section that defines the SQL to run to generate the report content. No properties have a default value.

Top Level Properties

Report Parameters

Report results are automatically secured and will only present data for customers that the user has access too. However reports are generally also constrained by user-defined parameter values when they are generated.

Each parameter that the user should be able to provide when running a report needs to be defined in its own section. Section names must start with parameter- and be unique within the file. For example, a two-parameter report might be defined using:

[parameter-1]
name = "Customer Name"
field_name = "customer_name"
description = "A partial customer name to limit report content for."
type = "string"
example = "N-Squared"

[parameter-2]
name = "Announcement Entry"
field_name = "entry_id"
description = "The announcement ID."
type = "string"
example = "10%"

In this example, the parameter sections parameter-1 and parameter-2 are unique and both start with parameter-. This is sufficient for the system to add/update the report from the TOML file. The report field name is used internally.

Scheduled time base reports rely on timeframes. A timeframe parameter can be defined similar to the below example:

[parameter-3]
applicability = "scheduling"
name          = "Reporting Period"
field_name    = "a_timeframe"
description   = "The period of time to retrieve EDRs for."
type          = "time_interval"

Whereas an explicit date can be requested using:

[parameter-1]
applicability = "immediate"
name          = "Date From"
field_name    = "a_start_date"
description   = "The earliest date to retrieve edr data from."
type          = "datetime"

Report parameter sections support the following properties:

Report SQL

The SQL to run against the N2ACD database is provided in the TOML section script and must be provided. Two parameters are important in this section:

Report SQL column obscuring

If columns need to be obscured to protect important information, the following options allow for different methods of data obscuring:

Within each of the above sections, the following properties define which columns will be obscured and which permissions a user must have to prevent the obscuring of data.

Note - Multiple of the same section can be placed in the same report to allow for different permission requirements for different columns.

Jarvis Configuration

Reports are run by the N2ACD-FE API and are configured in the associated Jarvis configuration file (by default /etc/jarvis/n2acd-fe.xml). The single relevant configuration option for reports is the report directory where dataset files that hold the SQL query to run for the report are stored:

        <dataset_dir prefix="reports.">/var/lib/n2acd-fe/reports</dataset_dir>

Note that the prefix must be reports. and the report directory referenced by this configuration is suggested to be alongside other environment-specific configuration for N2ACD, such as branding and i18n configuration.

However note that reports are global, and are not defined on a per-branding basis.

Access to reports is governed by the report_read group. Only users granted the report_read or admin_read groups will have access to any reports in the N2ACD-FE GUI - even if the user is granted specific report access via the N2ACD administration GUI.

Deployment Considerations

Report generation relies on the dataset files in the dataset directory prefixed by reports. to be available for each API node in a N2ACD environment. If N2ACD is deployed on multiple SMS nodes the report must be installed on each node individually.

It is suggested that the manage_available_reports.pl add command is run on each N2ACD SMS node. The database updates for the report will be run multiple times but will not duplicate or fail. The report file will be created on each node.