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:

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:

Option Type Default Description
--db-host String localhost IP address or FQDN of the PostgreSQL database instance to connect to.
--db-port Number 5432 Port number of the PostgreSQL database instance to connect to.
--db-name String n2in_owner Name of the PostgreSQL database that contains the N2ACD schema.
--db-connect PSQL DB Connection String Alternative option to using --db-host --db-port and --db-name. Specified as a PostgreSQL connection string. See PostgreSQL Connection Strings for more information.
--db-user String n2acd_owner The username of the user to connect to the PostgreSQL database instance with.
--db-password String The password for the user specified with --db-user to connect to the PostgreSQL database instance with.
--jarvis-config String /etc/jarvis/n2acd-fe.xml The Jarvis XML file that defines the N2ACD Flow Editor configuration.

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:

Field Description
Report ID The ID of the report, matching the database table field n2acd.report.report_id
Category A string that determines the category of the report. The category may be an text value, and is used purely for display grouping.
Name A string that gives the report a name. This is for display purposes only.
Dataset A unique key (made up of characters [A-Za-z0-9_-]) that allow the GUI to match the report to a backend dataset file.
Fields A list of fields, separated by `
Dataset File The file on disk that is used to generate the report. This will be a file in the reports directory as determined by the Jarvis XML file.

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

Property Type Example Description
category String Usage The section in the GUI that this report should appear under. For display purposes only.
name String Announcement Usage The name of the report. The report name will be used for display purposes only.
description String A longer description of the report providing usage information for the user to read on-screen.

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:

Property Type Example Description
name String Announcement Entry The name to use for the field in the GUI.
field_name String entry_id Limited to values [A-Za-z0-9-_] this the name of the parameter to be used when the GUI sends the input value to the server. In the SQL for the report, the format to access the field name is {$fieldName} - e.g. {$entry-id}.
description String A longer description for the field, to help the user give a correct value when providing a field value.
type String string The type of input field that the GUI should provide to the user. Value values are choice and string.
example String 10% An example of the type of value that the user might input in the GUI for this parameter.
choices Array of Strings [ "a", "b" ] When type is set to choice the choices array is the list of valid values for this parameter to show to the user.
applicability String scheduling Either all, scheduling or immediate. If all (or this field is not defined), the parameter is used both for scheduled and ad-hoc reports. If scheduling this parameter will only be shown when scheduling a report. If immediate then this parameter will only be shown in the ad-hoc report generation section of the GUI.

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:

Property Type Example Description
datasetName String announcement_usage The name to use for the dataset file that stores the SQL to run.
databaseName String n2reporting The name of the database connection (as configured in the application Jarvis file) to use. This should either be unset (i.e. not listed as a property in the file) or set to n2reporting to use the N2ACD reporting database.
sql String The SQL that should be run for the report when requested. This should be a multi-line TOML string that conforms to the Jarvis SQL query requirements. See https://nsquared.nz/tech/jarvis.html for more information on Jarvis.

Report SQL column obscuring

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

TOML section Description
[[script.mask_columns]] Columns defined in this section will be masked, replacing all characters, aside from the first and last, with *.
[[script.mask_email_columns]] If defined, the columns will be masked as emails: johndoe@example.com will become j****e@example.com.
Column data not matching an email structure will still be masked.
[[script.remove_columns]] Columns defined in this section will have the entire column removed from response data, including the column headers.

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.

Property Type Example Description
user_groups String[] [ “admin_read”, “audit_read” ] An array containing user groups the user must have to be able to see the columns. If the user has at least one group, the column wont be obscured.
columns String[] [ “Created By” ] An array of columns that will be obscured if the user has none of the user groups assigned inside user_groups

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.