MONITORING DENODO SERVERS

Denodo Solution Manager provides administrators with a web tool called Diagnostics and Monitoring Tool (also known as DMT) to monitor all the Virtual DataPort servers from a single location in real time and detect malfunctions as soon as possible.

At the same time, the Solution Manager also achieves historical monitoring by enabling Denodo Monitor to be on specific servers at any moment. Denodo Monitor generates several historical logs that can be loaded later into the Diagnostics and Monitoring Tool to view a graphical representation of the server performance metrics.

Now, let us go through the two types of monitoring (live and historical) one by one. Below, you have direct links to each section:

  • Real-time (live) monitoring.
  • Historical monitoring.

We will see how to start learning about Live monitoring!

It is strongly advised that you complete the Data Services tutorial before starting this one, as we will use the resources (data source, base views, and integrations) created on it.

If you have already completed it, please proceed to the next section. Otherwise, follow the subsequent instructions:

  1. Launch the resources needed (check how in the Installation & Bootstrapping tutorial).
  2. Log in to Denodo Design Studio (user/password: admin/admin)
  3. Import this VQL to Denodo by clicking on File > Import. Drag ‘n' drop the file:

If all steps have been executed correctly, you should observe the following in the Design Studio's elements tree:

Great! Now it's time to start the tutorial!

You can configure multiple servers across environments using Solution Manager's Diagnostic and Monitoring Tool and monitor them in real time (live).

First Step: Launch Denodo Diagnostic and Monitoring Tool

  1. Open the Denodo Solution Manager Home Page (by default, http://127.0.0.1:19090/solution-manager-web-tool/Login) and login with the administrator credentials (admin / admin)

  1. Start the Diagnostic & Monitoring Tool (DMT) by clicking on the Open icon. (by default, http://127.0.0.1:19090/diagnostic-monitoring-tool/Login).
  2. On the Diagnostic & Monitoring Tool login page, you need to provide the valid login credentials of the tool (by default admin / admin) and click on the Login button.

  1. After that, the home page of the tool will appear:

As you can see, the Diagnostic & Monitoring Tool web tool shows the servers registered in the Solution Manager which is great!

DMT: Creating Environments

For this special case, for adding Virtual DataPort servers manually, you can create a new Environment by using one of these two methods:

  • From the Menu Bar > Options > New Environment.
  • Right-click from the Navigator, click the Home button, and then Select New Environment.

To create the environment, you only have to provide the Name of the environment. In our case, Development:

When done, you can add a new server. For example, let's add a server in the Development environment by following the below steps:

  • Right-click on the created Environment and click on New Server.
  • Set the Name of the server.
  • Provide the Host name (or IP address) and set the Port number of Virtual DataPort.
  • Provide the User and Password (by default, admin / admin).
  • Set the Database name.
  • Click on the Create button.

DMT: Connecting to Denodo servers

On double-clicking the server, you can view some graphs displaying the status of the Virtual DataPort server. These graphs are updated automatically every few seconds:

Check the Monitoring section of the Denodo User Manuals for more information about all the graphs you will have available in the Diagnostic and Monitoring Tool!

Now, it's time to learn how to analyze historical information.

Until now, we have seen how to monitor the Denodo servers in real time using the Diagnostic and Monitoring Tool.

By default, that tool only shows information for the last 30 minutes. What happens if we need to analyze the Denodo servers during a specific time frame some days before because final users reported errors at that time?

There are two main steps involved in achieving the historical monitoring:

  • First, the most important thing is to have enabled Denodo Monitor.
  • Logs generated by Denodo Monitor could be imported into the Diagnostics and Monitoring Tool at any time to view the graphical representation of the server performance metrics.

What is Denodo Monitor?

Denodo Monitor is a command-line tool that runs indefinitely and periodically gathers information about the servers. The information is saved as tab-separated log files.

Denodo Solution Manager provides the option to start/stop Denodo Monitor, so let us go through:

  1. How to start/stop the Denodo Monitor.
  2. How to load Denodo Monitor log files into the Diagnostic and Monitoring Tool.

How to Start the Denodo Monitor

We can start the Denodo Monitor from the Solution Manager web tool by following the below steps:

  1. In the Solution Manager Administration Tool, click on Environments > Overview
  2. Click on the environment (in our example, Development).
  3. Click on the Start Monitor option from the drop-down:

  1. A popup window appears where you have to click the YES button to start monitoring the particular environment.

How to Check the List of Environments being Monitored

You can check the list of monitoring environments by selecting the option Environments > Active Monitors from the header menu:

How to Stop the Denodo Monitor

To stop the Denodo monitor, you only have to select the checkbox of the respective environment and click on the Stop Monitors button.

Next, we will learn how to load the historical Denodo Monitor log files into the Diagnostic and Monitoring Tool.

Previously, in this tutorial, you learned how to start Denodo Monitor to store information about your environments in several log files. For analyzing the logs, for example, for diagnosing something that happened in the past, you could check the log files manually (reading the files).

Still, the Diagnostic and Monitoring Tool includes a handy feature: it allows users to upload these Denodo Monitor log files for a graphical presentation.

Let's see how:

  1. Right-click on the created server in the Diagnostic & Monitoring Tool and select Load Diagnostic Info ... option:

  1. A pop-up appears where you need to set the Diagnostic name and the log file directory:

  1. Once the Create button is pressed, all logs within the configured directory will be graphically loaded into the tool:

That's all for the Diagnostic and Monitoring Tool! Let's now review other Monitoring tools you have available.

Denodo Dashboard is a DenodoConnect component that allows the creation of a set of interactive dashboards. These dashboards aim to visualize and analyze the information logged into a database by the Denodo Monitor tool, included in the Denodo Platform, and metadata from the Denodo server itself.

Denodo Dashboard includes prebuilt dashboards regarding queries, cache, incoming queries, performance, and FinOps:

Denodo Dashboard is based on Apache Superset, an open-source modern data exploration and visualization platform. Denodo has created a container image to customize Apache Superset by adding the ability to connect to Denodo.

First, follow the instructions described below. They are also described in this documentation, specifically in the execution section.

Configure the Solution Manager to write the monitoring data into an external database

  1. In your solution manager local installation, copy the PostgreSQL driver .jar file located in \lib\extensions\jdbc-drivers\postgresql-13 on both directories below:

    /resources/solution-manager-monitor/denodo-monitor/lib
    /lib/solution-manager-extensions

    See this documentation for more information.
  2. Restart Denodo Solution Manager server.
  3. Launch the Solution Manager web application and click on Configuration -> Monitoring configuration

  1. Then click on the DB tab.

  1. Define the external database connection:

    Database: in the dropdown list, choose PostgreSQL
    URL of the database: jdbc:postgresql://localhost:15432/denodo_monitor
    Provide the User and Password (denodo / denodo).
    Click on the Save icon.

    Solution Manager will automatically create the monitor metadata (tables that the Denodo Monitor needs to store its logging events and to store some monitor configuration) in the PostgreSQL database denodo_monitor.

Configuring and Importing Denodo Dashboard Metadata in your Virtual DataPort Server

  1. Download the Denodo Dashboard package on the Downloads > DenodoConnets section of the Denodo Support Site and unzip the Denodo Dashboard.zip file.

    Edit the denodo_dashboards.properties file located in the denodo-dashboard-xxxxxxx\dashboards\vql directory with these values:
denodo.dashboards.database.denodo_monitor.DRIVERCLASSNAME=org.postgresql.Driver
denodo.dashboards.database.denodo_monitor.DATABASEURI=jdbc\:postgresql\://localhost\:15432/denodo_monitor
denodo.dashboards.database.denodo_monitor.USERNAME=denodo
denodo.dashboards.database.denodo_monitor.USERPASSWORD=denodo
denodo.dashboards.database.denodo_monitor.USERPASSWORD.ENCRYPTED=
denodo.dashboards.database.denodo_monitor.CLASSPATH=postgresql-12
denodo.dashboards.database.denodo_monitor.DATABASENAME=postgresql
denodo.dashboards.database.denodo_monitor.DATABASEVERSION=12
denodo.dashboards.database.denodo_monitor.SCHEMANAME=public
  1. Log in to Denodo Design Studio (user/password: admin/admin):

  1. Import the denodo_dashboards.vql file located in the directory denodo-dashboard-xxxxxxx\dashboards\vql of the Denodo Dashboard to Denodo Virtual DataPort by clicking on File > Import. Drag ‘n' drop the denodo_dashboards.vql file in the VQL File section, check the option Use properties file and drag ‘n' drop the denodo_dashboards.properties :

If all steps have been executed correctly, you should observe the following in the Design Studio's elements tree:

To confirm that your environment is fully operational, try executing the final view server_resources, for instance, and see if some rows are returned.

Also, you should execute some views in the tutorial database to get more monitoring data in the denodo_monitor database and, therefore, in the denodo_dashboard database.

Downloading and Running the Denodo Dashboard Container

Once the image has been downloaded, to start a Superset for Denodo instance, you can use the following command:

docker run -d --name superset-denodo -e SUPERSET_SECRET_KEY=<your_secret_key> -p 8088:8088 superset-denodo

Apache Superset requires the environment variable SUPERSET_SECRET_KEY You can generate this environment variable using this command:

openssl rand -base64 42

Once Apache Superset is started, just go to http://localhost:8088, and log in (username /password: admin / admin).

The first step is to import the Denodo Query Reports dashboard provided by Denodo.

  1. On the right side, click on the import button.

  1. Click on the select file button.

  1. Select the dashboard-query-reports.zip file in the denodo-dashboard-xxxxxxx\dashboards directory.
  2. Click on the import button.

A new window will appear, asking you to provide the Denodo database password.

Provide the password admin, and click on IMPORT.

  1. Under the Settings menu, click the Database Connection button to configure the denodo_dashboard database connection.

  1. Click on the edit button:

  1. Edit the denodo_dashboard connection and define the SQLAlchemy URI below:
denodo+psycopg2://admin:admin@localhost:9996/denodo_dashboard
  1. Click the TEST CONNECTION button to confirm that the configuration is correct. You should get this message:

The Denodo Query Reports dashboard contains global information regarding queries (number of queries, duration, number of rows, etc.) categorized according to different criteria, such as databases, views, or status.

Click on the Superset for denodo to return to the home page, then click on the Denodo Query Reports dashboard.

The first tab, Databases, is displayed then. It contains query information classified by Denodo databases.

As we want to focus on the tutorial database, let's create a new filter.

  1. Click on ADD/EDIT FILTERS on the left.

  1. Click on add filters and dividers.

  1. Define Database for the FILTER NAME, and in the Column dropdown list, select database_0.

  1. Click on the SCOPING tab, click on Apply to all panels, and click on SAVE.

  1. You can now see your new filter Database on the left.

Let's now modify and apply some filters.

  1. On the left, click on the Time Range filter.

  1. On the dropdown list Range, select Custom, then on the dropdown list END, select Now, and click APPLY.

  1. On the left, on the Database filter, select the database tutorial, and then click on APPLY FILTERS.

As an immediate effect, you will now only see the request run against the tutorial database.

More Reports

Denodo Dashboard Query Report includes more information. You only have to click on each Tab:

  • Views: It displays query information regarding the views involved in the queries.
  • Queries: This tab shows information about the queries. At the top, you can see a summary of the number of different executed queries and the average number of queries per day
  • Queries Status: This tab contains information regarding the queries based on their status. It is essential to note that from this tab, you can apply cross-filtering to show all the Denodo Query Reports dashboard information by selecting a status.
  • Execution Timeline: This tab shows the number of executed queries, the total number of returned rows, the average query execution time, and the average query waiting time during time grouped per hour.
  • Cache: This tab shows query information about queries regarding the use of a cache.
  • User: It displays information regarding the users that execute the queries received by Denodo.
  • Access Interface: It shows query information about queries grouped by the client's access interface (for instance, Web Design Studio, ODBC, JDBC, etc.; see more details in this documentation).
  • User-Agent: It contains query information about queries categorized by the client user agent (for instance, Denodo-Web-Design-Studio or a custom value defined in the Denodo ODBC DSN; see this documentation for more information) that performed the query.
  • Client IP: It shows query information about queries categorized by the client's IP.

With the help of this tutorial, we have understood the importance of Solution Manager and its advantages concerning its following functionalities:

  • How to achieve Live and Historical Monitoring using Diagnostics and Monitoring Tool and Denodo Monitor.
  • How to configure Denodo Dashboard (Apache Superset for Denodo) to visualize and analyze the information logged into a database by the Denodo Monitor tool

Congratulations! You have finished the Monitoring Denodo Servers tutorial. Look at the official reference manuals of Denodo Platform and play with your own use cases.