Skip to content
  • There are no suggestions because the search field is empty.

Using the Opscompass CLI to maintain database connections for monitoring.

Maintaining the database connections used by the Opscompass Data Gathering scripts is a key component in the compliance monitoring of the Oracle databases.  While the Opscompass GUI provides a way to manually maintain database connections, the Opscompass CLI provides a way to do the same actions programmatically.   

This document provides example CLI commands to maintain Oracle database configurations.  These commands could be combined with additional shell programming to potentially automate the maintenance against a centralized list of database connections (e.g. tnsnames.ora) 

Get list of companies for monitoring user 

Most Opscompass users will be associated with one company, but it is possible to belong to more than one.  The “list-companies” will list all companies associated with the authenticated user. 

opscompass@testvm:~$ opscompass accounts list-companies
[ 
  { 
    "name": "Acme Testing", 
    "company": "acmetesting" 
  }, 
  { 
    "name": "Another Demo Customer", 
    "company": "democustomer" 
  } 
]

There are some CLI commands where “company” is a required parameter to designate a specific company in case the authenticated user belongs to more than one company.  For those commands, specify the value for the chosen “company” tag listed in the JSON output from the “list-companies” command. 

List the available monitoring configurations for a company 

Each company can have one or more monitoring configurations.  Each monitoring configuration will contain its own defined database connections.  Most companies will have one monitoring configuration for each monitoring server in their environment.  Some companies have multiple monitoring servers because of segmented networks, and each will have a unique monitoring configuration for its associated database connections. 

The following command will list all the monitoring configurations associated with a company: 

opscompass@testvm:~$ opscompass license-manager list-monitoring-configurations --company acmetesting 
[ 
  { 
    "configurationName": "Monitored DB Connections", 
    "id": 9565, 
    "companyId": 2382 
  } 
] 

In the example above, the “acmetesting” company has one configuration defined – “Monitored DB Connections” with an ID of 9565. 

If no monitoring configurations existed for the specified company, the following could be used to create one: 

opscompass@testvm:~$ opscompass license-manager create-monitoring-configuration --company acmetesting --configurationName "Monitored DB Connections" --autoUpdateScripts true --autoUpdateConfigurations true 
{ 
  "id": 9565, 
  "configurationName": "Monitored DB Connections", 
  "companyId": 2382, 
  "autoUpdateScripts": true, 
  "autoUpdateConfigurations": true, 
  "dataFileRetainDays": 90, 
  "dataFileRetainTextDays": 1, 
  "forceFileEncryption": true, 
  "logFileRetainDays": 30 
} 

The output from the creation command will display the ID and other details that can be used in subsequent commands to list or edit database connections within this monitoring configuration. 

List all database connections defined for a specific monitoring configuration 

Using the ID from the command specified above, the following command can be used to list all database configurations within that monitoring configuration: 

opscompass@testvm:~$ opscompass license-manager list-oracle-database-configurations --monitoring-configuration-id 9565 
{ 
  "oracleDatabaseConfigurations": [ 
    { 
      "id": 12134, 
      "actions": [ 
        "dblicensing", 
        "dbcompliance" 
      ], 
      "configurationName": "dwh12ctestvm_dwhtest", 
      "user": "dblicmon", 
      "password": "271e7e25-e70d-4701-8aa6-6ccd5276c2e5", 
      "server": "dwh12ctestvm.acmetesting.org:1527/dwhtest", 
      "monitoringConfigurationId": 9565, 
      "companyId": 2382 
    }, 
    { 
      "id": 12133, 
      "actions": [ 
        "dblicensing", 
        "dbcompliance" 
      ], 
      "configurationName": "dwhprodvm_dwhprod", 
      "user": "dblicmon", 
      "password": "33fb568a-ca4a-4b12-8460-da4ab6b89efb", 
      "server": "dwhprodvm.acmetesting.org:1528/dwhprod", 
      "monitoringConfigurationId": 9565, 
      "companyId": 2382 
    }, 
    { 
      "id": 12131, 
      "actions": [ 
        "dblicensing", 
        "dbcompliance" 
      ], 
      "configurationName": "ebsprodvm_prod", 
      "user": "dblicmon", 
      "password": "96ab76b3-4050-442c-8fee-6fbfe8f50bf2", 
      "server": "ebsprodvm.acmetesting.org:1521/prod", 
      "monitoringConfigurationId": 9565, 
      "companyId": 2382 
    }, 
    { 
      "id": 12132, 
      "actions": [ 
        "dblicensing", 
        "dbcompliance" 
      ], 
      "configurationName": "ebstestvm_test", 
      "user": "dblicmon", 
      "password": "a1e2d360-239d-4482-843a-0e3f7df23401", 
      "server": "ebstestvm.acmetesting.org:1521/test", 
      "monitoringConfigurationId": 9565, 
      "companyId": 2382 
    } 
  ] 
}

The details of the four database connections associated with the monitoring configuration with ID 9565 are listed in JSON format.  Each database connection also has its own “id” that will be used for subsequent commands against specific connections. 

The “jq” utility can be used in combination with the Opscompass CLI commands to parse specific values from the JSON output.  For example, to list just the “id” and “server” values from the database configurations, the following command could be used: 

opscompass@testvm:~$ opscompass license-manager list-oracle-database-configurations --monitoring-configuration-id 9565 | jq '.oracleDatabaseConfigurations[] | {id,server}' 
{ 
  "id": 12134, 
  "server": "dwh12ctestvm.acmetesting.org:1527/dwhtest" 
} 
{ 
  "id": 12133, 
  "server": "dwhprodvm.acmetesting.org:1528/dwhprod" 
} 
{ 
  "id": 12131, 
  "server": "eebsprodvm.acmetesting.org:1521/prod" 
} 
{ 
  "id": 12132, 
  "server": "ebstestvm.acmetesting.org:1521/test" 
} 

With some additional scripting, the “server” information could be parsed and compared to connection information in a tnsnames.ora file, for example.  The following commands can be used to create new connections, update existing connections, or delete connections that no longer exist. 

Create a new Oracle Database Connection 

The following command can be used to create a new database connection in the specified monitoring configuration. 

opscompass@testvm:~$ opscompass license-manager create-oracle-database-configuration --monitoring-configuration-id 9565 --configuration-name "dwh19testvm_dwhtest" --action "dblicensing" --action "dbcompliance" --server "dwh19testvm.acmetesting.org:1521/dwhtest" 
{ 
  "id": 15111, 
  "configurationName": "dwh19testvm_dwhtest" 
}

To get the details of the database connection that was just created, use the following: 


opscompass@testvm:~$ opscompass license-manager get-oracle-database-configuration --id 15111  
{ 
  "oracleDatabaseConfiguration": { 
    "id": 15111, 
    "actions": [ 
      "dbcompliance", 
      "dblicensing" 
    ], 
    "configurationName": "dwh19testvm_dwhtest", 
    "user": null, 
    "password": null, 
    "server": "dwh19testvm.acmetesting.org:1521/dwhtest", 
    "monitoringConfigurationId": 9565, 
    "companyId": 2382 
  } 
}

Update an existing Oracle Database Connection 

If the details of an existing database connection needs to be modified, the “update-oracle-database-configuration” command can be used.  The example below modifies the port to 1528 for the database connection created above. 

opscompass@testvm:~$ opscompass license-manager update-oracle-database-configuration --id 15111 --server "dwh19testvm.acmetesting.org:1528/dwhtest" 
{ 
  "id": 15111, 
  "configurationName": "dwh19testvm_dwhtest", 
  "actions": [ 
    "dbcompliance", 
    "dblicensing" 
  ], 
  "user": null, 
  "password": null, 
  "server": "dwh19testvm.acmetesting.org:1528/dwhtest", 
  "monitoringConfigurationId": 2382 
}

After the command completes, it will display the updated details for the modified database connection. 

Delete an Oracle Database Connection 

If a database is decommissioned and no longer needs to be monitored, the following command can be used to remove the defined database connection from the monitoring configuration.   

opscompass@testvm:~$ opscompass license-manager delete-oracle-database-configuration --id 15111 
{ 
  "id": 15111, 
  "deleted": true 
} 

The output from the command will indicate if the database connection was successfully deleted. 

Default Credentials 

Each monitoring configuration defined in Opscompass allows for a default user and password to be defined for the database connections.  If a database connection does not specify its own user and/or password, then the missing value(s) will be provided from the global credentials, if defined. 

Get the default Oracle global credentials 

The following command can be used to display the default credentials for a monitoring configuration: 

opscompass@testvm:~$ opscompass license-manager get-oracle-default-credential --monitoring-configuration-id 2382 
"" 

In this example, there were no default credentials defined for the configuration with ID 2382.  The steps to add those credentials with the CLI are below. 

Create default Oracle global credentials 

If the global (default) credentials do not exist for a monitoring configuration, the following command can be used to create them: 

opscompass@testvm:~$ opscompass license-manager create-oracle-shared-credential --monitoring-configuration-id 2382 --user dblicmon --password "TheSecretPassword" 
{ 
  "id": 7150, 
  "credentialName": "DEFAULT" 
}

Another listing of the default credentials for the specified monitoring configuration ID shows the defaults that were just created: 

opscompass@testvm:~$ opscompass license-manager get-oracle-default-credential --monitoring-configuration-id 2382 
{ 
  "id": 7150, 
  "credential_name": "DEFAULT", 
  "user": "dblicmon", 
  "password": "fb0954c0-4009-4382-81b6-b75c92e835b9" 
}

Update Default Oracle global credentials 

The user and/or password for an existing default credential can be modified with the CLI as follows: 

opscompass@testvm:~$ opscompass license-manager update-oracle-shared-credential --id 7150 --password "ANewSecretPassword" 
{ 
  "id": 7150, 
  "credentialName": "DEFAULT", 
  "monitoringConfigurationId": 2382 
}

Note that the update command requires the ID of the specific default credential.  This ID is shown in the output of the “create-oracle-shared-credential” and “get-oracle-default-credential” commands. 

Delete Default Oracle global credentials 

If the default credentials for a monitoring configuration need to be deleted, the following command can be used to remove them: 

opscompass@testvm:~$ opscompass license-manager delete-oracle-shared-credential --id 7150 
{ 
  "id": 7150, 
  "deleted": true 
}

Another listing of the default credential for the monitoring configuration should show that no defaults are defined: 

opscompass@testvm:~$ opscompass license-manager get-oracle-default-credential --monitoring-configuration-id 2382 

""

What about SQL Server database connections? 

The example commands listed above are all related to maintaining the Oracle database connections.  The same set of commands are also available for maintaining the SQL Server database connections.  To use the above examples to maintain SQL Server database connections, replace “oracle” with “msql” in the CLI commands being called. 

Use the following command to get a full listing of the commands and input parameters within the “license-manager” CLI option: 

opscompass@testvm:~$ opscompass license-manager