Prometheus Dashboard - How to monitor SAP IQ metrics using Prometheus

[former_member668835]

Overview

Prometheus is a free open-source software application used for system or event monitoring, and alerting. It records real-time metrics in a time series database (allowing for high dimensionality) built using a HTTP pull model, with flexible queries and real-time alerting. It works well for recording any purely numeric time series. It fits both machine-centric monitoring as well as monitoring of highly dynamic service-oriented architectures.  

In this blog post, we will learn how to start an SAP IQ HTTP web server, create a web service and access it from a web browser. We will then finally use Prometheus to collect specific data from this webservice and have it displayed on its dashboard. 

Pre-requisite

SAP IQ Server and Client should be installed beforehand. 

For more information regarding the IQ Setup and how to create a demo database, please refer to SAP IQ Installation and Configuration Guide  

Steps

Starting the SAP IQ server and connecting to a database

You will need an IQ server for this task. To start your IQ server, please follow the next few steps: 

1. Navigate to the Start Menu.


2. 
   

   Under the SAP folder, select the ‘Interactive SQL’ icon.

   
   


3. You will then see a prompt wherein you will have to enter your database credentials 


4. Enter the database credentials and select ‘Connect'.

 

 

Starting the HTTP server 

The HTTP web server that is built into the SAP IQ database server can be started with the -xs server command option or the sp_start_listener system procedure. 

Both methods allow you to perform the following tasks: 

• Enable a web service protocol to listen for web service requests. 


• Configure network protocol options, such as server port, logging, timeout criteria, and the maximum request size. 

The general format of the command line is: 

start_iq -xs <protocol-type>(<protocol-options>) <your-database-name.db>​

The general format for the stored procedure call is: 

CALL sp_start_listener(<protocol-type>,<address-port>,<protocol-options>); 

You may also refer to documentation on How to start an HTTP web server.

For now, we will use the system procedure to start the HTTP server. An example of calling the stored procedure call: 

CALL sp_start_listener('HTTP','127.0.0.1:8082','LOG=c:\\temp\\http.log'); 

The above statement instructs the server to listen for HTTP requests on port 8082.  

Run a statement like the example mentioned above. Use a different port number if a web server is already running on the port number that you initially chose.  

The following message should appear on the IQ Network server window if you have successfully started the HTTP server: 

Creating a webservice

We will be using a small example to demonstrate how to create a webservice. In this example, the web service that we will be creating contains a function called ‘cpu’ that will return the ‘CPU Total Time’ metric (collected from the database).   

We will be using the ‘CREATE SERVICE’ command to create the webservice. 

Execute the following SQL statement in Interactive SQL: 

CREATE OR REPLACE FUNCTION cpu_time_metric(metric_name long varchar) RETURNS LONG VARCHAR  



BEGIN  



    declare @cpu_total_time long varchar;  



    set @cpu_total_time = (select stat_value from dbo.sp_iqstatistics() where stat_name='CpuTotalTime');  



    return metric_name || '{' || 'iqserver = "' || property('ServerName') || '"} '|| @cpu_total_time ||'\n'  



END;  



CREATE OR REPLACE FUNCTION thread_in_use_metric(metric_name long varchar) RETURNS LONG VARCHAR  



BEGIN  



    declare @thread_in_use long varchar;  



    set @thread_in_use = (select stat_value from dbo.sp_iqstatistics() where stat_name='ThreadsInUse');  



    return metric_name || '{' || 'iqserver = "' || property('ServerName') || '"} '|| @thread_in_use ||'\n'  



END; 



CREATE OR REPLACE FUNCTION thread_free_metric(metric_name long varchar) RETURNS LONG VARCHAR  



BEGIN  



    declare @thread_free long varchar;  



  set @thread_free = (select stat_value from dbo.sp_iqstatistics() where stat_name='ThreadsFree');  



    return metric_name || '{' || 'iqserver = "' || property('ServerName') || '"} '|| @thread_free ||'\n'  



END; 



CREATE OR REPLACE FUNCTION active_connection_metric(metric_name long varchar) RETURNS LONG VARCHAR  



BEGIN  



    declare @active_connection long varchar;  



    set @active_connection = (select stat_value from dbo.sp_iqstatistics()  where stat_name='ConnectionsActive');  



    return metric_name || '{' || 'iqserver = "' || property('ServerName') || '"} '|| @active_connection ||'\n'  



END; 



--DROP SERVICE metrics; 



--This can be used after the first time the service is created 



CREATE SERVICE metrics  



    TYPE 'RAW'  



    AUTHORIZATION OFF  



    USER DBA 



    AS SELECT cpu_time_metric('CPU_Total_Time'), thread_in_use_metric('Threads_In_Use'),  



    thread_free_metric('Threads_Free'), active_connection_metric('Active_Connections') 



    from dummy;   

 

Some notes regarding the ‘CREATE SERVICE’ command, with reference to the above SQL Script: 

The CREATE SERVICE statement creates the ‘TEST_PROMETHEUS’ web service.  

The TYPE statement is used to set the desired web service type. The HTML type clause is recommended for web browser compatibility. Other general HTTP web service type clauses include XML, RAW, and JSON. In this case, we will use ‘RAW’.  

The AUTHORIZATION OFF clause indicates that authorization is not required to access the web service. 

The USER DBA statement indicates that the service statement should be run under the DBA login name. 

The AS SELECT statement can be used to select from a table or function, or view data directly. An alternative to this could be the AS CALL clause which lets the service call a stored procedure or a function. 

For more information regarding the ‘CREATE SERVICE’ command, please refer to the information here.  

Run the statement.  

 

Accessing the newly created web-service 

On the computer running the SAP IQ HTTP web server, open a web browser, such as Microsoft Edge or Google Chrome, and go to the following URL: 

http://localhost:8082/iqdemo/metrics 

This URL directs your web browser to the HTTP web server on port 8082. The web service ‘metrics’ prints various metrics like ‘CPU_Total_Time’ and ‘Active_Connections’.  

It should look very similar to the following: 

The result set output is displayed in the format specified by the <web-service-type-clause> from step 2. 

You have now successfully created and viewed your webservice! All that is left is configuring your Prometheus setup so that it can detect this webservice. 

Make changes to the Prometheus Configuration file 

1. You can download Prometheus from here.


2. You can extract the download with the help of the following command:
   

   tar xvfz prometheus-*.tar.gz

   
   


3. Navigate to the extracted folder:
   

   cd prometheus-* 

   
   


4. We will now configure Prometheus with the help of the prometheus.yml file. The Prometheus download comes with this file. Open the .yml file in a text editor.  It should look very similar to the following:
   There are three blocks of configuration in the configuration file: global, rule_files, and scrape_configs.
   
   The global block controls the Prometheus server's global configuration. Currently, we have two options.
   
   The first, scrape_interval, controls how often Prometheus will scrape targets. You can override this for individual targets. In this case the global setting is to scrape every 15 seconds.
   
   The evaluation_interval option controls how often Prometheus will evaluate rules. Prometheus uses rules to create new time series and to generate alerts. The rule_files block specifies the location of any rules we want the Prometheus server to load. For this example, we have no rules.
   
   The last block, scrape_configs, controls what resources Prometheus monitors.
   
   For more information regarding the configuration, please refer to the first steps documentation and the configuration documentation.
   
   We will be configuring the section under scrape_configs so that it monitors the web service that we have created in the previous steps.  Replace all the text under the <scrape_configs> section with:
   

   - job_name: 'IQ' 
   
   
   
       # metrics_path defaults to '/metrics' 
   
   
   
       # scheme defaults to 'http'. 
   
   
   
       static_configs: 
   
   
   
       - targets: ['localhost:8082'] 

   
   ‘job_name’ is the name assigned to the scraped metrics by default. 
   
   ‘metrics_path’ is used to determine the HTTP resource path on which to fetch metrics from targets. By default, it is set to ‘/metrics’. We do not need to change it because the web-service that we have created is called metrics and hence, this aligns with the URL of the web-service. 


5. To start Prometheus with our newly created configuration file, run the following from the directory containing the Prometheus binary: 
   

   prometheus --config.file=prometheus.yml ​

   
   

   

   
   


6. Give it about 30 seconds and then, navigate to the following URL:
   

   http://localhost:9090/targets 

   
   

   

   
   The state of your target should always be ‘UP’ when it is being scraped for metrics. Hence, this is a good way to verify that. For our case, the endpoint would be the webservice we created.

Hence, you have successfully configured the Prometheus set-up so that it is able to detect your webservice.  

The next step demonstrates the various ways we can view/asses the metrics that our web-service returns, using Prometheus’ UI and dashboard. 

Using Prometheus 

Navigate to the following URL: 

http://localhost:9090/graph 

This leads us to Prometheus's built-in expression browser 

By default, the "Console" view is displayed:

 

On selecting the drop-down ‘insert metric at cursor’, a large number of metrics that Prometheus exports for itself can be viewed. However, we are interested in the metrics fetched by the web-service, that we created in the previous steps. 

So, for example, if we want to see the value for the metrics ‘Active Connections’, select it from the drop-down.

 

Next, select the ‘Execute’ button.

You should see a result similar to the following: 

You can also view multiple metrics simultaneously. 

To do so, simply select the ‘Add Graph’ button. 

Another console should open, and you may follow the previous steps again to view the value of another metric.

In the screenshot below, the metric ‘CPU_Total_Time’ was selected from the drop-down. 

Hence, you can view the values of both ‘Active_Connections’ and ‘CPU_Total_Time’ simultaneously. 

Now, if you wanted to asses the respective metrics with the help of a graphical layout.  

Select the ‘Graph’ tab as show below:

A view similar to the screenshot below, will be displayed.  

You may further experiment with the graph range parameters and other settings. 

Conclusion

Congratulations!  

You have now learnt how to successfully configure Prometheus so that it monitors your web-service. To continue learning about Prometheus, refer to the Overview for ideas on what to explore next.