Create reports

The “Create reports” tool allows the user to create and edit custom reports and schedule them for periodic execution. The required permissions are “Engineer” (user level 3) or higher. All created reports are shared which allows anyone to view or modify these defined reports.

Custom reports are defined in the same manner as the “Relations” as hey are basically SQL queries that report on the various NetYCE databases. Basic knowledge on writing a SQL SELECT query is mandatory as is some experience with the NetYCE data model. The NetYCE entity-relationshp-diagram or ERD is a useful starting point here. The tables and their current data is accessible using the “Admin - Custom data” tool. Where the ERD includes the mostly the tables for all the objects involved with the “Build” view of the network, the Custom data offers access to ALL tables in the various databases, including any custom tables.

Since NetYCE uses a MariaDB database (the open source version of MySQL), the supported SQL syntax has to conform to MySQL 5.x / MariaDB 10.x. The reporting tool will NOT allow you to create SQL queries using the UPDATE, INSERT or DELETE commands. Nor are table manipulations allowed.

Many examples of useable SQL statements for custom reports can be found in the “Build - Relations” tools.

The the 'Create reports' tools shows in its initial overview one or more columns (depending on the number of reports) all currently defined custom reports.

A new report can be created by typing a report name in the box following the 'New' button. Typing an existing name opens the existing report for editing. Clicking the 'New' button without a name will produce an error.

Selecting a report using the radio button and then clicking 'Edit' will open the report query for editing or re-scheduling.

Clicking the 'Edit' button without a name or selected report will allow you to start a query without a name. This is useful only to execute an ad-hoc query without needing the results or report definition.

In the edit mode, the report name can be added or changed. When changing a name the currently edited report is actually duplicated so that the user needs to manually delete the report with the old name if that was the intention.

Existing reports already display a 'Query' that can be executed using the 'Preview' button. Clicking the 'Preview' button will execute the query as displayed in the query box allowing for a try-as-you-go approach in finding the correct query. If the report has a name defined, its results are saved and is accessible using the 'View reports' tool. Each time the 'Preview' button is hit, the results (if any) will overwrite the existing results.

Since reports can generate very large number of rows in the resulting output, a limit is imposed on its preview: only the first 5000 rows are displayed. This was done to protect the browser from overloading its host and reduce the upload time. A truncation message is included should that be the case. The stored report results are not truncated or limited in any way.

The report queries do not support variables like <hostname> since there is no 'context' to provide a value for these variables. Only Relations can use this kind of variable substitution.

when the 'Overwrite output' option box is checked (the default), each time the report is generated it will overwrite the earlier version as there will exist only one output image for this report.

Should a history of older reports be desired, the 'Overwrite output' can be unchecked. The report image name will then receive the suffix of today's date in the format '-YYYYMMDD'. As each image is thus unique for the date, a history is created.

The number of historic reports maintained is configurable using the Settings Tweak "Age_custom_reports" which has the default of 30 days.

A warning on creating a longer history and large or many reports is in order here. The report outputs are stored in the 'YCE' database which could potentially cause the database to run out of disk space at which time it will simply halt all operation.

Once a report is scheduled, it can be emailed automatically after it is executed. Enter one or more email addresses and check the 'email to' checkbox. As a shortcut, the 'Me' button will copy the email address from your user profile to the 'email to' box.

The report will be emailed in CSV format. The email body will list up to 500 of these CSV lines before truncating. Additionally, the same CSV report will be included as an attached file which will not be truncated.

The maximum number of CSV lines in the email body can be configured using the Settings Tweak "Email_report_body_limit" that has de default value of 500. Setting it to “0” will disable the CSV lines in the body and only issue the email with the report as attachment.

Below a sample report as received by email where the limit was set to 5 lines:

As with the “relations”, the custom report queries can use the NetYCE SQL extension for the “TRANSFORM” command. This command will convert the values of a named column into its own column.

The syntax is TRANSFORM <source-column> WITH <value-column> and must be the first line of the SQL statement and can only be used in SELECT queries.

See the article on Transform columns for details.

The NetYCE database uses several (user-defined) columns in selected tables where the data is stored in encrypted format. See the article on Encryption for details on the encryption facilities within NetYCE.

When reporting on these encrypted columns, the permissions of the user creating the report will determine if these columns are decrypted transparently or not.

The permission levels for who this decryption takes place is controlled by a Setup 'Tweak': AllowCustomReportsDecrypt. For details on this tweak see Settings Tweaks "AllowCustomReportsDecrypt"

NOTE: Be aware that a report generated by a user with decryption permissions will be readable by all operators.

A report is not stored until the Save and schedule is selected. When a report is saved, it is automatically scheduled to run periodically. The default is 04:05 in the morning every day.

Five specifiers are used to schedule the report: 'hour', 'minute', 'day of the month', 'weekday' and 'month'. These can all be selected using dropdown menus. See the section on 'More on schedules' for advanced scheduling options.

If the intention is to run the report manually and fetch the results, select the report and 'Edit', then 'Preview'. Switch to the 'View reports' tool and open it for review or download.

If the intention is to never run the report automatically, set its schedule for february 31. This non-existing day will be skipped.

Since the report scheduler deploys the standard unix 'crontab' functions, its advanced scheduling functions are made available in the edit reports too. The crontab options are powerful but require some familiarity.

The Custom reports tool includes a text box with the resulting crontab time specification after the (basic) choices are made. This text box can then be modified to include the advanced options as desired. This information is then used to schedule the report. Incorrect settings are rejected.

The crontab format uses five fields:

 .---------------- minute (0 - 59)
 |  .------------- hour (0 - 23)
 |  |  .---------- day of month (1 - 31)
 |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
 |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
 |  |  |  |  |
 *  *  *  *  * 

Examples:
 00 12 * * *                   = daily at noon
 05 04 * * mon                 = mondays at 04:05 in the morning

The advanced options allow values to be divided, enumerated and ranged:

Examples:
 */10 * * * *                   = every 10 minutes
 00 12 * * mon,tue,wed,thu,fri  = noon on weekdays
 */15 7-18 * * mon-fri          = every quarter hour during office hours

Custom reports definitions are stored in the database (using the YCE.Images table) as are the resulting reports. By using the database for storage the reports can run independent of the NetYCE server and can also be independently of the server be retrieved.

Ans as all NetYCE servers need direct access to one of its databases, the task of generating the reports is automatically distributed evenly over the NetYCE servers present in the setup. As a consequence, each time a report is (re)scheduled, a message stating <servername>: crontab updated is issued for each of the servers. It might be that the crontab of only one server is modified, but all servers will verify the assigned reports are actually configured.

Manual alteration of the server where a report will be executed can be accomplished using the “Admin - Custom data” tool. Select from the YCE database the 'Images' table and filter the report definition entries on Image_name using “.sql”. By changing the server name in the Image_filename column the report will be reassigned to that server on the next report (re)schedule. Edit any report and 'Save and schedule' will reconfigure the crontabs where required.