5.11. Use the REST API to manage Speedgains configuration

Speedgain for Databases includes a REST API to offer various services to the frontend and execute business logic.

The communication must follow a certain protocol in order to gain access to the REST API. The individual steps for authentication and creating objects in the configuration are explained below. For example, it is necessary to authenticate against the REST API in order to receive an access token, which is required for further communication.

Please note! The steps explained in this section are aimed only to create configuration objects, not to update them.

5.11.1. Requirements

  • curl or an other command line tool for HTTP(s) communication

  • the password for the admin user is known

  • Optional: command line tool jq for JSON processing

5.11.2. Swagger-UI API Documentation

The Speedgain for Databases Service provides a Swagger-UI API Documentation. After setup of the S4DBs Service, you can reach the build-in documentation over the URL: http://s4dbshost:8080/Speedgain_for_Databases_Service/swagger-ui/index.html

Please replace the placeholder s4dbshost:8080 with your actual S4DBs address.

5.11.3. Task 1: Authenticate and getting access token

REST Endpoint "Speedgain_for_Databases_Service/speedgain/auth/login"

To do any interaction with Speedgains REST API, you need an access token first. You need your admin password in this step to "login" and receive your access token from the API.

Its recommended to create a shell script that is for example called s4dbs_auth.sh:

#! /bin/bash
HOST=192.168.2.211      # enter your host address
PORT=8080               # enter your port
S4DBSUSER=admin         # your admin user
S4DBSPWD=admin          # your admin password

curl -d "user=$S4DBSUSER&password=$S4DBSPWD" -X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/auth/login

After execution you will receive a message like this:

user@s4dbs-host:~$ ./s4dbs_auth.sh
{"pwd":null,"token":"Bearer eyJhbGciOiJIUzUxMiJ9.eyJqdGkiOiJJVEdBSU5XZWJ0b2tlbiIsInN1YiI6ImFkbWluIiwiYXV0aG9yaXRpZXMiOlsiUk9MRV9VU0VSIl0sImlhdCI6MTcxOTU3NDQ4MCwiZXhwIjoxNzE5NTc4MDgwfQ.gCqFCeFYZPTNwFOr9JU7-ohMw-XrWm8Yh3rGFf_U5BwK4wFzw2fJNVeBP6U7kn1nN-wNjBrqvWceDf-ZjDfzxw","role":"admin","user":"admin"}

Use the value of the property "token" for any other REST communication.

We recommend to pipe the output of the authenticate script to jq then grep the "token" property value, remove double quotes and redirect the output into a token file what can be used in any other script.

user@s4dbs-host:~$ ./s4dbs_auth.sh |  jq .token | tr '"' ' ' > s4dbs_access.token

5.11.4. Task 2: Create Monitoring Login

REST Endpoint "Speedgain_for_Databases_Service/speedgain/configuration/setLogin"

To monitor a database or a server, you need to authenticate against the monitored system. A Login is such a user, password and optionally a SSH-key combination to do that. The following script shows how to create a login object.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setLogin \
-d '{
    "name": "unique-login-id",
    "displayName": "Readable-ID",
    "user": "username",
    "password": "Secret",
    "sshKey": ""
}'

The property "name" must be an unique identifier! The properties "displayName", "user", "password" and "sshKey" can be any string. The password is encrypted when saved in the database.

5.11.5. Task 3: Create Node

REST Endpoint "Speedgain_for_Databases_Service/speedgain/configuration/setRemoteNode"

A Node is a server where databases are located. Its possible to also monitor the Node. However the only Node type currently supported for monitoring are Linux based systems.

The following script shows how to create a simple remote node object without monitoring.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setRemoteNode \
-d '{
  "name": "unique-node-id",
  "displayName": "Readable-Nodename",
  "enabled": false,
  "hostname": "example.host.local",
  "remotePort": 22,
  "os": "LINUX",
  "remotePolicy": null,
  "login": null
}'

The property "remotePort" is optional if you don’t want to monitor the node. Properties "remotePolicy" and "login" must set to null! Property "enabled" must be set to false!

The following values are supported for the property "os":

  • LINUX

  • AIX

  • WINDOWS

  • CLOUD_AZURE

If you want to monitor the Remote Node, the creation script is more complex! You need to know the Login object you want to use for authentication. In addition you must set the "remotePolicy" property to either: policy_local_linux or policy_remote_linux.

The following script shows how a Linux node must be created to monitor it locally.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setRemoteNode \
-d '{
  "name": "Unique-Node-ID",
  "displayName": "Readable-Nodename",
  "enabled": true,
  "hostname": "example.host.local",
  "remotePort": 22,
  "os": "LINUX",
  "remotePolicy": {
    "name": "policy_local_linux"
  },
  "login": {
    "name": "unique-login-id"
  },
  "updateConter": 2
}'

To monitor a Node remotely via SSH, you have to use the "remotePolicy" called "policy_remote_linux" as shown below.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setRemoteNode \
-d '{
  "name": "Unique-Remote-Node-ID",
  "displayName": "Readable-Remote-Node-Name",
  "enabled": true,
  "hostname": "example.host.local",
  "remotePort": 22,
  "os": "LINUX",
  "remotePolicy": {
    "name": "policy_remote_linux"
  },
  "login": {
    "name": "unique-login-id"
  },
  "updateConter": 2
}'

For the "login" and "remotePolicy" property, only the "name" is relevant. The Speedgain Service will lookup the respective objects from its configuration database. In other words the Login and RemotePolicy object is only referenced at this point.

5.11.6. Task 4: Create Policy

REST Endpoint "Speedgain_for_Databases_Service/speedgain/configuration/setPolicy"

Policies are even more complex to create. You have to pick a valid combination of "policyType", "policyTypeSpecification" and "allowedPolicyTypes" The following values are supported:

"policyType" and matching "policyTypeSpecification":

  • DATABASE

    • DB2

    • ORACLE

    • SQLSERVER

    • POSTGRESQL

    • AZURE_SQLDATABASE

  • REMOTE_NODE

    • AIX

    • LINUX

    • WINDOWS

    • CLOUD_AZURE

  • LOCAL

    • AIX

    • LINUX

    • WINDOWS

    • CLOUD_AZURE

  • PROMETHEUS

    • MONGODB

    • MARIA_MYSQL

    • GENERIC

Each "policyType" and "policyTypeSepcification" brings a certain array of Collectors. A Collector is the smallest unit that captures some sort of data from the monitored object. Each Collector has the following mandatory configuration data:

  • "name": Unique-ID of the collector (Do not change the name!),

  • "displayName": in general the same value as "name",

  • "enabled": is the collector enabeld or disabled,

  • "collectionIntervalSec": delay between monitoring samples are captured in seconds,

  • "cancelCounter": how often can an error occur until the collection will be stopped,

  • "properties": some collectors provide additional configuration parameters,

  • "mandatory": If a Collector must be enabled or it is optional. If "mandatory" is true, the property "enabled" must also be "true" (Do not change the predefined value!)

The following example script shows how to create a simple custom policy for DB2.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setPolicy\
-d '{
    "name": "unique-policy-ID",
    "displayName": "Readable-Policy-Name",
    "updateConter": 0,
    "enabled": true,
    "policyType": "DATABASE",
    "policyTypeSpecification": "DB2",
    "editable": true,
    "allowedPolicyTypes": [
        "DATABASE"
    ],
    "collectors": [
        {
            "name": "clusterhoststate",
            "displayName": "clusterhoststate",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "envcfsysresouces",
            "displayName": "envcfsysresouces",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "envgetsystemresources",
            "displayName": "envgetsystemresources",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "envinstinfo",
            "displayName": "envinstinfo",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 3600,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "getinstanceinfo",
            "displayName": "getinstanceinfo",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "lockeventmonitor",
            "displayName": "lockeventmonitor",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetactivity",
            "displayName": "mongetactivity",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetbufferpool",
            "displayName": "mongetbufferpool",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 900,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetcf",
            "displayName": "mongetcf",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetconnection",
            "displayName": "mongetconnection",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetcontainer",
            "displayName": "mongetcontainer",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 900,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetdatabase",
            "displayName": "mongetdatabase",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": true
        },
        {
            "name": "mongetextendedlatchwait",
            "displayName": "mongetextendedlatchwait",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongethadr",
            "displayName": "mongethadr",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetindex",
            "displayName": "mongetindex",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 1800,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetlatch",
            "displayName": "mongetlatch",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetmemorypool",
            "displayName": "mongetmemorypool",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetpageaccessinfo",
            "displayName": "mongetpageaccessinfo",
            "updateConter": 0,
            "enabled": false,
            "collectionIntervalSec": 300,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetpkgcachestmt",
            "displayName": "mongetpkgcachestmt",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 900,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetroutine",
            "displayName": "mongetroutine",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 900,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetservicesubclass",
            "displayName": "mongetservicesubclass",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetservicesuperclass",
            "displayName": "mongetservicesuperclass",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongettable",
            "displayName": "mongettable",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 1800,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongettablespace",
            "displayName": "mongettablespace",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 900,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongettransactionlog",
            "displayName": "mongettransactionlog",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetutility",
            "displayName": "mongetutility",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "mongetworkload",
            "displayName": "mongetworkload",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        },
        {
            "name": "monlockwaits",
            "displayName": "monlockwaits",
            "updateConter": 0,
            "enabled": true,
            "collectionIntervalSec": 60,
            "cancelCounter": 100,
            "properties": {},
            "instances": {},
            "mandatory": false
        }
    ],
    "collectorsLoaded": true
}'

To see all Policy templates with its valid "policyType", "policyTypeSpecification" and "Collectors" combination, please expand the following block. Keep in mind that you have to pick a single "allowedPolicyType" if there are multiple available.

Click to show all valid Policy templates
[
    # MariaDB & MySQL Policy
    {
        "name": "policy_maria_mysql",
        "displayName": "policy_maria_mysql",
        "updateConter": 0,
        "enabled": true,
        "policyType": "PROMETHEUS",
        "policyTypeSpecification": "MARIA_MYSQL",
        "editable": true,
        "allowedPolicyTypes": [
            "PROMETHEUS"
        ],
        "collectors": [
            {
                "name": "mariamysql",
                "displayName": "mariamysql",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            }
        ]
    },
    # MongoDB Policy
    {
        "name": "policy_mongodb",
        "displayName": "policy_mongodb",
        "updateConter": 0,
        "enabled": true,
        "policyType": "PROMETHEUS",
        "policyTypeSpecification": "MONGODB",
        "editable": true,
        "allowedPolicyTypes": [
            "PROMETHEUS"
        ],
        "collectors": [
            {
                "name": "mongodb",
                "displayName": "mongodb",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            }
        ]
    },
    # SQL-Server Policy
    {
        "name": "policy_sqlserver",
        "displayName": "sqlserver",
        "updateConter": 0,
        "enabled": true,
        "policyType": "DATABASE",
        "policyTypeSpecification": "SQLSERVER",
        "editable": true,
        "allowedPolicyTypes": [
            "DATABASE"
        ],
        "collectors": [
            {
                "name": "dmdbindexoperationalstats",
                "displayName": "dmdbindexoperationalstats",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmoswaitstats",
                "displayName": "dmoswaitstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "backupset",
                "displayName": "backupset",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmiovirtualfilestats",
                "displayName": "dmiovirtualfilestats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmexecsessions",
                "displayName": "dmexecsessions",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmdbmissingindexdetails",
                "displayName": "dmdbmissingindexdetails",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmdbfilespaceusage",
                "displayName": "dmdbfilespaceusage",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmexecsessionwaitstats",
                "displayName": "dmexecsessionwaitstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "ringbufferschedulermonitor",
                "displayName": "ringbufferschedulermonitor",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmdbindexusagestats",
                "displayName": "dmdbindexusagestats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmosperformancecounters",
                "displayName": "dmosperformancecounters",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            },
            {
                "name": "dmexecquerystats",
                "displayName": "dmexecquerystats",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {
                    "include_SQL_Statement": "true",
                    "include_Query_Plan": "false"
                },
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dmdbindexphysicalstats",
                "displayName": "dmdbindexphysicalstats",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {
                    "detailLevel__1_LIMITED__2_SAMPLED__3_DETAILED": "1"
                },
                "instances": {},
                "mandatory": false
            },
            {
                "name": "databases",
                "displayName": "databases",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "configurations",
                "displayName": "configurations",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            }
        ]
    },
    # Linux Policy
    {
        "name": "policy_linux",
        "displayName": "linux",
        "updateConter": 0,
        "enabled": true,
        "policyType": null,
        "policyTypeSpecification": "LINUX",
        "editable": true,
        "allowedPolicyTypes": [
            "REMOTE_NODE",
            "LOCAL"
        ],
        "collectors": [
            {
                "name": "linuxprocstat",
                "displayName": "linuxprocstat",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": true
            },
            {
                "name": "linuxprocmeminfo",
                "displayName": "linuxprocmeminfo",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "linuxprocdiskstats",
                "displayName": "linuxprocdiskstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "linuxdiskfree",
                "displayName": "linuxdiskfree",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "linuxprocloadavg",
                "displayName": "linuxprocloadavg",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            }
        ]
    },
    # AIX Policy
    {
        "name": "policy_aix",
        "displayName": "aix",
        "updateConter": 0,
        "enabled": true,
        "policyType": null,
        "policyTypeSpecification": "AIX",
        "editable": true,
        "allowedPolicyTypes": [
            "REMOTE_NODE",
            "LOCAL"
        ],
        "collectors": []
    },
    # Windows Policy
    {
        "name": "policy_windows",
        "displayName": "windows",
        "updateConter": 0,
        "enabled": true,
        "policyType": null,
        "policyTypeSpecification": "WINDOWS",
        "editable": true,
        "allowedPolicyTypes": [
            "REMOTE_NODE",
            "LOCAL"
        ],
        "collectors": []
    },
    # Generic Prometheus Policy
    {
        "name": "policy_generic",
        "displayName": "policy_generic",
        "updateConter": 0,
        "enabled": true,
        "policyType": "PROMETHEUS",
        "policyTypeSpecification": "GENERIC",
        "editable": true,
        "allowedPolicyTypes": [
            "PROMETHEUS"
        ],
        "collectors": [
            {
                "name": "generic",
                "displayName": "generic",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            }
        ]
    },
    # Db2 Policy
    {
        "name": "policy_db2",
        "displayName": "db2",
        "updateConter": 0,
        "enabled": true,
        "policyType": "DATABASE",
        "policyTypeSpecification": "DB2",
        "editable": true,
        "allowedPolicyTypes": [
            "DATABASE"
        ],
        "collectors": [
            {
                "name": "mongetcontainer",
                "displayName": "mongetcontainer",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetutility",
                "displayName": "mongetutility",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetlatch",
                "displayName": "mongetlatch",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "lockeventmonitor",
                "displayName": "lockeventmonitor",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "monlockwaits",
                "displayName": "monlockwaits",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongettablespace",
                "displayName": "mongettablespace",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetindex",
                "displayName": "mongetindex",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 1800,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetmemorypool",
                "displayName": "mongetmemorypool",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "envgetsystemresources",
                "displayName": "envgetsystemresources",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetcf",
                "displayName": "mongetcf",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetdatabase",
                "displayName": "mongetdatabase",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": true
            },
            {
                "name": "mongetextendedlatchwait",
                "displayName": "mongetextendedlatchwait",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetworkload",
                "displayName": "mongetworkload",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetactivity",
                "displayName": "mongetactivity",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetservicesubclass",
                "displayName": "mongetservicesubclass",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongettransactionlog",
                "displayName": "mongettransactionlog",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "clusterhoststate",
                "displayName": "clusterhoststate",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "envinstinfo",
                "displayName": "envinstinfo",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 3600,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetroutine",
                "displayName": "mongetroutine",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "envcfsysresouces",
                "displayName": "envcfsysresouces",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetconnection",
                "displayName": "mongetconnection",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetpkgcachestmt",
                "displayName": "mongetpkgcachestmt",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetservicesuperclass",
                "displayName": "mongetservicesuperclass",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetbufferpool",
                "displayName": "mongetbufferpool",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 900,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongetpageaccessinfo",
                "displayName": "mongetpageaccessinfo",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongethadr",
                "displayName": "mongethadr",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "getinstanceinfo",
                "displayName": "getinstanceinfo",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "mongettable",
                "displayName": "mongettable",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 1800,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            }
        ]
    },
    # Azure Cloud Node Policy
    {
        "name": "policy_cloud_azure",
        "displayName": "cloud_azure",
        "updateConter": 0,
        "enabled": true,
        "policyType": "REMOTE_NODE",
        "policyTypeSpecification": "CLOUD_AZURE",
        "editable": true,
        "allowedPolicyTypes": [
            "REMOTE_NODE"
        ],
        "collectors": []
    },
    # PostgreSQL Policy
    {
        "name": "policy_postgresql",
        "displayName": "postgresql",
        "updateConter": 0,
        "enabled": true,
        "policyType": "DATABASE",
        "policyTypeSpecification": "POSTGRESQL",
        "editable": true,
        "allowedPolicyTypes": [
            "DATABASE"
        ],
        "collectors": [
            {
                "name": "pgtablespace",
                "displayName": "pgtablespace",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgstatstatements",
                "displayName": "pgstatstatements",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgstatactivity",
                "displayName": "pgstatactivity",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgsettings",
                "displayName": "pgsettings",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgstatreplication",
                "displayName": "pgstatreplication",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pglswaldir",
                "displayName": "pglswaldir",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgstatdatabase",
                "displayName": "pgstatdatabase",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            },
            {
                "name": "pgmonlockwaits",
                "displayName": "pgmonlockwaits",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            }
        ]
    },
    # Oracle Policy
    {
        "name": "policy_oracle",
        "displayName": "oracle",
        "updateConter": 0,
        "enabled": true,
        "policyType": "DATABASE",
        "policyTypeSpecification": "ORACLE",
        "editable": true,
        "allowedPolicyTypes": [
            "DATABASE"
        ],
        "collectors": [
            {
                "name": "systemevent",
                "displayName": "systemevent",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pgastat",
                "displayName": "pgastat",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "sqlstats",
                "displayName": "sqlstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 3600,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "dbawaiters",
                "displayName": "dbawaiters",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 3600,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "instance",
                "displayName": "instance",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "sesstimemodel",
                "displayName": "sesstimemodel",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "database",
                "displayName": "database",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "systimemodel",
                "displayName": "systimemodel",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "sesstat",
                "displayName": "sesstat",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 300,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "session",
                "displayName": "session",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "datafileusage",
                "displayName": "datafileusage",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "sga",
                "displayName": "sga",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "osstat",
                "displayName": "osstat",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "systemwaitclass",
                "displayName": "systemwaitclass",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "pdbs",
                "displayName": "pdbs",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "sgainfo",
                "displayName": "sgainfo",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 100,
                "properties": {},
                "instances": {},
                "mandatory": false
            }
        ]
    },
    # Azure SQL-Database Policy
    {
        "name": "policy_azure_sqldatabase",
        "displayName": "azure_sqldatabase",
        "updateConter": 0,
        "enabled": true,
        "policyType": "DATABASE",
        "policyTypeSpecification": "AZURE_SQLDATABASE",
        "editable": true,
        "allowedPolicyTypes": [
            "DATABASE"
        ],
        "collectors": [
            {
                "name": "azdmdbmissingindexdetails",
                "displayName": "azdmdbmissingindexdetails",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbindexoperationalstats",
                "displayName": "azdmdbindexoperationalstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmexecsessions",
                "displayName": "azdmexecsessions",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbindexusagestats",
                "displayName": "azdmdbindexusagestats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbindexphysicalstats",
                "displayName": "azdmdbindexphysicalstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {
                    "detailLevel__1_LIMITED__2_SAMPLED__3_DETAILED": "2"
                },
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmexecrequests",
                "displayName": "azdmexecrequests",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdatabasescopedconfigurations",
                "displayName": "azdatabasescopedconfigurations",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbwaitstats",
                "displayName": "azdmdbwaitstats",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmexecsessionwaitstats",
                "displayName": "azdmexecsessionwaitstats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmosperformancecounters",
                "displayName": "azdmosperformancecounters",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmuserdbresourcegovernance",
                "displayName": "azdmuserdbresourcegovernance",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            },
            {
                "name": "azindexcolumns",
                "displayName": "azindexcolumns",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {
                    "include_sys_schema": "false",
                    "include_index_columns": "false"
                },
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmexecquerystats",
                "displayName": "azdmexecquerystats",
                "updateConter": 0,
                "enabled": false,
                "collectionIntervalSec": 900,
                "cancelCounter": 10,
                "properties": {
                    "include_SQL_Statement": "true",
                    "include_Query_Plan": "false"
                },
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbfilespaceusage",
                "displayName": "azdmdbfilespaceusage",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": false
            },
            {
                "name": "azdmdbresourcestats",
                "displayName": "azdmdbresourcestats",
                "updateConter": 0,
                "enabled": true,
                "collectionIntervalSec": 60,
                "cancelCounter": 10,
                "properties": {},
                "instances": {},
                "mandatory": true
            }
        ]
    }
]

Please copy only a JSON fragment starting in the next line after # <comment> mark. For example, if you want to add a own Linux monitoring policy, you copy this fragment:

    {
        "name": "policy_linux",     #<- Change to a unique ID
        "displayName": "linux",     #<- change to your preferred readable name
        "updateConter": 0,
        "enabled": true,
        "policyType": null,         #<- replace value *null* with either "REMOTE_NODE" or "LOCAL"
        "policyTypeSpecification": "LINUX",
        "editable": true,
        "allowedPolicyTypes": [
            "REMOTE_NODE",          #<- remove the entry that doesn't match with your value of "policyType"
            "LOCAL"
        ],
        "collectors": [
            {
                "name": "linuxprocstat",            #<- Do not change!
                "displayName": "linuxprocstat",     #<- can be changed, but usually it doesn't
                "updateConter": 0,
                "enabled": true,                    #<- true means Collector is active, false means Collector is inactive (check "mandatory" also)
                "collectionIntervalSec": 60,        #<- Sleep time between each data captures in seconds
                "cancelCounter": 100,               #<- How many times an error can happen until the Collector will be parked
                "properties": {},
                "instances": {},
                "mandatory": true                   #<- Do not change!
            },
            {
                "name": "linuxprocmeminfo",
                ...
            },
            ...
        ]
    }

Put the JSON fragment in your script and please don’t forget set a unique ID for "name" and pick either LOCAL or REMOTE_NODE for "allowedPolicyTypes" and "policyType".

5.11.7. Step 5: Create Database

REST Endpoint "Speedgain_for_Databases_Service/speedgain/configuration/setDatabase"

The Database object requires all other objects created before. But you just need to reference a Remote Node, a Login and a Policy. Configuration of these objects is done before, now you just use them. However, the required properties of a Database object vary a bit for each supported DBMS type. Supported DBMS types are:

  • POSTGRESQL

  • DB2

  • ORACLE

  • AZURE_SQLDATABASE (Additional properties required)

  • SQLSERVER (Additional properties required)

  • MARIA_MYSQL (Prometheus based)

  • MONGODB (Prometheus based)

  • GENERIC (Prometheus based)

The following script shows one of the simplest examples how to create a single Db2 Database.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setDatabase \
-d '{
    "name": "unique-database-id",
    "displayName": "Readable DB Name",
    "enabled": true,
    "dbPort": "25000",
    "dbName": "sample",
    "dbms": "DB2",
    "authMethod": "DEFAULT",
    "domain": "",
    "databasePolicy": {
        "name": "db2_high",
        "policyType": "DATABASE",
        "policyTypeSpecification": "DB2"
    },
    "login": {
        "name": "unique-login-id"
    },
    "remoteNode": {
        "name": "unique-remote-node-id"
    },
    "ssl": false,
    "jdbcParameter": []
}'

The properties "login" and "remoteNode" only contain the "name" property. You have to make sure that the value you provide matches to the "name" of an existing Policy, Login or Remote-Node!

ATTENTION You have to provide three properties for "databasePolicy" to configure the a monitoring policy:

  • "name" - Name of policy

  • "policyType" - For a database the policy type has to be "DATABASE", prometheus based policies have to specify "PROMETHEUS" (see listing above)

  • "policyTypeSpecification" - Name of the DBMS in uppercase. for Db2 the value is "DB2". See the policyTypeSpecification value of the policies listed above in chapter Task 4: Create Policy.

If you want to query all available Policies you can use the following script. A JSON array will be returned.

#! /bin/bash
HOST=192.168.2.211               # enter your host address
PORT=8080                        # enter your port
TOKEN=$(cat s4dbs_access.token ) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X GET http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/getPolicies

Please only use Policies of "policyType": "DATABASE".

If you need to provide additional JDBC-Parameters please use the property "jdbcParameter" what is an array! Here are an example how to define these JDBC-Parameters:

...
    "jdbcParameter": [
        "clientProgramName=speedgain",
        "clientApplicationInformation=Bobs-Monitor"
    ]
...

This notation is scheduled to change in the future. Currently you have to provide a key-value pair for each array element. Seperate key and value by an "=" (equal sign).

SQL Server

The property "dbName" must be set always to "master". While optional for other DBMS you must also set the property "authMethod". Values can be "DEFAULT" and "NTLM". Finally there is the property "domain" you have to set if "authMethod" is set to "NTLM".

...
    "dbName": "master",
    "dbms": "SQLSERVER",
    "authMethod": "NTLM",
    "domain": "example.local",
...
Azure SQL Database

The property "dbName" is in contrast to SQL Server again variable. "authMethod" can now have three values:

  • DEFAULT (SQL Server Authentication)

  • AZURE_AD_SERVICE_PRINCIPAL

  • CUSTOM

Its also important to note, that SSL encryption is mandatory for Azure SQL Database! Therefore you have to set the property "ssl" to true and must provide a SSL Certificate object reference via the "certificate" property. How to create an SSL certificate object in Speedgain for Databases is described in the next section.

...
    "dbName": "sampledb",
    "dbms": "AZURE_SQLDATABASE",
    "authMethod": "DEFAULT",
    "ssl": true,
    "certificate": {
        "name": "unique-ssl-cert-id"
    },
...
Prometheus based database monitoring

Keep in mind, that for that type of DBMS (MongoDb, MySQL/MariaDB and Generic) the database isn’t monitored directly! Instead a Prometheus exporter will be queried. Therefore you have to specify not the port of the database you want to monitor but the port of the Prometheus exporter. The following example script show how to add a MariaDB to Speedgains monitoring.

#! /bin/bash
HOST=192.168.2.211              # enter your host address
PORT=8080                       # enter your port
TOKEN=$(cat s4dbs_access.token) # load access token

curl --header "Content-Type: application/json" \
-H "Authorization: $TOKEN" \
-X POST http://$HOST:$PORT/Speedgain_for_Databases_Service/speedgain/configuration/setDatabase \
-d '{
    "name": "unique-database-id",
    "displayName": "readable-db-name",
    "updateConter": 1,
    "enabled": false,
    "dbPort": 9933,
    "jdbcParameter": [
        ""
    ],
    "dbms": "MARIA_MYSQL",
    "databasePolicy": {
        "name": "policy_maria_mysql",
        "policyType": "PROMETHEUS",
        "policyTypeSpecification": "MARIA_MYSQL"
    },
    "login": {
        "name": "unique-login-id"
    },
    "remoteNode": {
        "name": "unique-remote-node-id"
    },
    "ssl": false,
    "certificate": null,
    "metricsPath": "",
    "doNotPersist": false,
    "keyStore": null
}'

You can tweak the data collection by setting the property "metricsPath" accordently. Please refer the documentation of the respective Prometheus exporter you want to query.

5.11.8. SSL Encryption

REST Endpoint "Speedgain_for_Databases_Service/speedgain/configuration/setSslCertificate"

Creation of an SSL Certificate object is simple. The following example script shows how to create one.

{
    "name": "unique-ssl-cert-id",
    "displayName": "SSL-Cert",
    "description": "SSL certificate ",
    "certificate": "-----BEGIN CERTIFICATE-----\nMIIC5jCCAc6gAwIBAgIINtqxno2auOQwDQYJKoZIhvcNAQELBQAwETEPMA0GA1UE\nAxMGaXRnYWluMB4XDTI0MDEyODEzNTgzMloXDTI1MDEyODEzNTgzMlowETEPMA0G\nA1UEAxMGaXRnYWluMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEArxhh\ngVq34A0N4Jlm/eR9HbAB6aR6njlHOYhihyHJq9NppvKsh2RWamEVBcmYUDCwMG13\nyHVzPA7gub/fP0dPjEgkXTEq0xTcr0WLVNT9NKgsy6m0ftEKcDr8FmKN9KszSHnP\np+0RZ2teeTixNOsTFGQ8aG5vgNbiLNJeu/G3ZVF7m0EDVn0CHZzibsGFcwNQ2Imz\nGTgvwdWF4nXqbnhxvwr87NZBJjw5T5MiRVSlzzccH3ty4mIdhz43cvnoUWBi6usF\ndG52sdqHvcULjwc/m2zJofaIbeWbG1KVkaZw7g3i+lzPH+B5sAuBmAeK7cmcdVyD\n0RKUG2mwcthMNMJVEwIDAQABo0IwQDAdBgNVHQ4EFgQUwgH7CAEEb8fTq7N/2dL9\nUwRQBHMwHwYDVR0jBBgwFoAUwgH7CAEEb8fTq7N/2dL9UwRQBHMwDQYJKoZIhvcN\nAQELBQADggEBAA2D5LLDye7LtccjqfoKXPvldaF2hiloFcnKGok4UJD18Zjd71vD\neSYCGKByNZqGDTKKcnHDvYJcmxX3MgXJwLuVxvbJtiXEz0tuSUDHjHYG4m2eRAob\nDVchvGUbGbVnyGmjyy8/R6YGb1py+UGccUubqEwjZ2/SNfNQtkKa2uYBgmkxtX/3\np/Nj3rADUkrFLvkdaAmjFRqGwgYuSAQ21mK1riOUFwZLdBpx7iyP29U1V9VpUCZH\nc8v210y9Rr3yw8wBJ9aDxEi8wWqsbWDSQ0On3bW8usDkR25Q1CUMiXstSvV1+zWF\np5ITlxiNY2kj75kcInTecZK3pmgM3z73cx8=\n-----END CERTIFICATE-----"
}

The property "name" must be an unique identifier like with other Speedgain configuration objects. Property "certificate" is a string containing the text representation of the certificate. Please take care to don’t add spaces. To add a new line please only use the "\n" character.