database Integration
custom-sql-request (en)


ServicePilot database-custom-sql-request


# Custom SQL Requests

Overview

This package monitors a standard SQL request.

Description

This package uses a ServicePilot Agent in order to execute SQL requests periodically. This request can be made on following database type:

  • SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • ODBC

The result of each SQL request is the response itself and the total response time.

Requirements

  • Network Flows - It is is necessary to ensure network connectivity between ServicePilot and the monitored device. In case of a network infrastructure using a firewall, all the ports below must be opened:

    • TCP/1521: Between ServicePilot Agent and Oracle Database
    • ServicePilot Manager Web server access (by default TCP/80 when using HTTP or TCP/443 when using HTTPS although this port is configurable): Between ServicePilot Agent and ServicePilot Manager
  • Third Party Configuration

    • Login Account on the monitored database

    • For Oracle Data Provider for .NET 4 connections, the following procedure must be performed on the server where the ServicePilot Agent is installed (by default, on the same server as ServicePilot Manager):

      1. Install the Oracle 64-bit ODAC prerequisite if not already present. Microsoft Visual C++ 2010 SP1 Redistributable x64 Package for ODAC 12c Release 4, Microsoft Visual C++ 2005 SP1 Redistributable x64 Package for ODAC 11.2 Release 6
      2. Download an Oracle 64-bit ODAC (Oracle Data Access Components) Xcopy for Windows x64 installation archive appropriate for the version of Oracle database to which you are to connect: ODAC 12c Release 4 (12.1.0.2.4) for Oracle 10g Release 2 or later, ODAC 11.2 Release 6 (11.2.0.4.0) for Oracle 9i Release 2 to 11g Release 2
      3. Create a folder named "C:\oracle"
      4. Extract the contents of the download ZIP in to the folder "C:\oracle\ODAC121024Xcopy_x64"
      5. Open a command prompt using Run as administrator
      6. Execute the following commands from the command prompt:

         cd C:\oracle\ODAC121024Xcopy_x64
        
         install.bat odp.net4 C:\oracle\odac odac true
        
      7. Edit the Environment Variable Path to add the location of the ODAC that was just installed. To do so, follow these steps:

        1. Go to Control Panel. In the Search box (upper right corner), type "Environment Variable". Click on Edit the system environment variables
        2. In the System Properties window, click on Environment Variables
        3. In the Environment Variables window, look for the variable Path in the System Variables section. Select the variable and click on Edit
        4. In the Edit System Variable window, add the following line at the end of the variable value

          ;C:\oracle\odac;C:\oracle\odac\bin
          
        5. Click OK on all the open windows to exit them.

    • For Oracle Provider for OLE DB connections, the following procedure must be performed on the server where the ServicePilot Agent is installed (by default, on the same server as ServicePilot Manager):

      1. Install the Oracle 64-bit ODAC prerequisite if not already present. Microsoft Visual C++ 2010 SP1 Redistributable x64 Package for ODAC 12c Release 4, Microsoft Visual C++ 2005 SP1 Redistributable x64 Package for ODAC 11.2 Release 6
      2. Download an Oracle 64-bit ODAC (Oracle Data Access Components) Xcopy for Windows x64 installation archive appropriate for the version of Oracle database to which you are to connect: ODAC 12c Release 4 (12.1.0.2.4) for Oracle 10g Release 2 or later, ODAC 11.2 Release 6 (11.2.0.4.0) for Oracle 9i Release 2 to 11g Release 2, ODAC 10g Release 2 (10.2.0.3) for Oracle 8i Release 3 to 10g Release 2 (supported on XP, Server 2003)
      3. Create a folder named "C:\oracle"
      4. Extract the contents of the download ZIP in to the folder "C:\oracle\ODAC121024Xcopy_x64"
      5. Open a command prompt using Run as administrator
      6. Execute the following commands from the command prompt:

         cd C:\oracle\ODAC121024Xcopy_x64
        
         install.bat oledb C:\oracle\odac odac true
        
      7. Add a correctly configured tnsnames.ora file in the C:\oracle\odac\network\admin directory. Note that there is a sample file in the C:\oracle\odac\network\admin\sample directory.

      8. Edit the Environment Variable Path to add the location of the ODAC that was just installed. To do so, follow these steps:

        1. Go to Control Panel. In the Search box (upper right corner), type "Environment Variable". Click on Edit the system environment variables
        2. In the System Properties window, click on Environment Variables
        3. In the Environment Variables window, look for the variable Path in the System Variables section. Select the variable and click on Edit
        4. In the Edit System Variable window, add the following line at the end of the variable value

          ;C:\oracle\odac;C:\oracle\odac\bin
          
        5. Click OK on all the open windows to exit them.

  • ServicePilot Requirements

    • ServicePilot Manager minimum version: 8.5
    • ServicePilot Agent minimum version: 8.5 installed and configured

Installation

Before adding a resource to monitor, make certain that all pre-requisites are in place and if a ServicePilot Agent is required, that it is communicating correctly with the ServicePilot Manager. Resources can be added to ServicePilot configuration in a number of ways:

Add resource using Views Configuration web interface

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration. The Configuration > Views web page will open.
  3. Click on the view in which to place the new resource in the Views hierarchy on the left of the interface. The View editor section will show the existing view contents.
  4. From the Packages list on the right of the interface, click and drag the database-custom-sql-request package into the View editor and let go.
  5. The Package properties dialog box will open to allow resource configuration.
  6. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  7. Click Save to apply the new resource to ServicePilot configuration.

Add resource by changing servicepilot.conf configuration file

Resources can be added to ServicePilot configuration by directly editing the servicepilot.conf or other included YAML configuration files. The ServicePilot web interface can be used to make these changes and apply them to the running configuration.

  1. As an administrative user of ServicePilot, open the ServicePilot web interface.
  2. Navigate to Administration.
  3. Navigate to Configuration > Edit configuration.
  4. Expand the configuration to find the provisioning: and then packages: section of the view in which the new resource will be placed.
  5. Add the example package configuration line below.
  6. Click on the green - package: word to open then Package properties dialog box to allow resource configuration.
  7. Click OK to close the Package properties dialog box. Note that the dialog box will not close if required parameters are not set.
  8. Click Save to apply the new resource to ServicePilot configuration.

Example:

- package: "database-custom-sql-request;;;;;;;;My Custom SQL Request;;MySQL;60;SELECT COUNT(*) FROM MYSQL;;;;;;;;;;;;;;;;;;;;;127.0.0.1;mysql;;spmonitor;2O+7+z4djbQc+mBVDWTJfQ==;;"

Key field notes

  1. In the Monitoring Policies tab, specify the policy or policies to apply to the package

  2. Basic Parameters tab: Select Database Type: Select the type of database to monitor. The available options are:

    1. Microsoft SQL Server
    2. Oracle Data Access Components (ODAC)
    3. Microsoft OLE DB Provider for Oracle
    4. MySQL
    5. PostgreSQL
    6. ODBC
    7. Access 95-97
    8. Access 2000-XP
    9. Excel 3/4/5/8
  3. Monitoring Options tab:

    1. Polling Interval (sec): Specify the time interval (in seconds) at which ServicePilot will poll the database (default 60)
    2. SQL Request 1/2/3: Specify up to three (3) SQL requests to execute on the database
  4. MS SQL tab:

    1. IP address/FQDN: Specify the IP address, host name or FQDN for the Microsoft SQL Server Database, as resolvable by the machine on which the ServicePilot Agent is running
    2. Database Instance Name: Specify the database instance to monitor in the format Host\Instance
    3. Select authentication type: Select the authentication type (Windows or SQL) to be used by ServicePilot to connect to the SQL Server Database
    4. Database Login: Specify the username of the SQL Server Login Account
    5. Database Password: Specify the password of the SQL Server Login Account Note: Database Login and Password are only required for SQL type authentication
  5. Oracle DP .NET 4 tab:

    1. Database Service Name or ID: Specify the service name or ID of the Oracle Database e.g. XE or ORCL
    2. User: Specify the username of the Oracle Login Account to be used by ServicePilot
    3. Password: Specify the password of the Oracle Login Account to be used by ServicePilot
    4. Number of connections: Specify the number (up to 3) of connections to the host server(s) of the Oracle Database.
    5. Database IP Address/FQDN: Specify the IP address, host name or FQDN for the Oracle Database server, as resolvable by the machine on which the ServicePilot Agent is running
    6. Database Listener Port Specify the listener port of the Oracle Database on the host server (default: 1521)
  6. Oracle OLE DB tab:

    1. Data Source (TNS Name Entry or EZCONNECT string): Specify an alias in the tnsnames.ora file or use an EZCONNECT definition in the form host[:port][/service_name]
    2. User: Specify the username of the Oracle Login Account to be used by ServicePilot
    3. Password: Specify the password of the Oracle Login Account to be used by ServicePilot
  7. MySQL / PostgreSQL tab:

    1. IP address/FQDN: Specify the IP address, host name or FQDN for the MySQL Database, as resolvable by the machine on which the ServicePilot Agent is running
    2. Database Name: Specify the name of the database to monitor
    3. User: Specify the username of the Login Account
    4. Password: Specify the password of the Login Account
  8. ODBC tab:

    1. Data Source: Specify the Data Source name
  9. Access / Excel tab:

    1. File path: Specify the full path to the Access/Excel file.
database custom-sql-request 0

database custom-sql-request 1

database custom-sql-request 2