Skip to main content

Database Discovery

About Database Discovery

Database discovery was introduced in v16.02.00 (ensure you are running v16.02+), and currently supports Windows and *nix-based discovery jobs to discover Microsoft SQL (aka MSSQL), Oracle, Postgres SQL, and DB2 databases.

note

Database discovery requires an Application Dependency Mapping license. Go to Tools > Settings > Licensing to see if the license is enabled. Contact support@device42.com for licensing assistance.

By default, the target machine ports are defined by different database vendors as follows:

  • MS SQL: 1433
  • DB2: 50000
  • Postgres: 5432
  • Oracle: 1521

Dynamic discovery can discover different ports to use. As of v16.17.00, Device42 supports MSSQL and Oracle database discovery for customers that have database instances configured to listen on non-standard ports, especially on a shared database server hosting multiple instances. During database discovery, Device42 will identify and connect through the discovered active listening port.

If you want to specify DB discovery details yourself, including the DB server port, server IP address, and DB access credentials, use Database Connections Discovery jobs (see below) to discover databases.

MS SQL Server DB Discovery (on Windows targets)

Microsoft SQL Server (MSSQL) discovery is supported on discovery targets running Microsoft Windows and requires a separate set of credentials to authenticate to the database instance itself. Ensure these credentials have appropriate permissions to view the databases you are interested in discovering.

Device42 supports autodiscovery on Windows and *nix platforms for the following MSSQL versions.

  • MSSQL 2005
  • MSSQL 2008
  • MSSQL 11 on SQL Server 2012
  • MSSQL 12 on SQL Server 2014
  • MSSQL 13 on SQL Server 2016
  • MSSQL 14 on SQL Server 2017
  • Cluster MSSQL 12

Minimum Permissions Requirements for MSSQL Discovery

To query the below tables, please ensure you have View Server State permissions. For discovery to return detailed info about your database instance, you will require read permissions to the following system views:

  • sys.dm_exec_connections
  • sys.dm_exec_sessions
  • sys.databases
  • sys.master_files
  • sys.tables
  • sys.dm_os_sys_info
  • sys.dm_os_sys_memory
  • sys.all_objects

The snippet shown below is necessary to get data from some of the preceding views even if the user has read permissions (sys.master_files, for example).

1 use [master] 2 GO 3 grant VIEW any DEFINITION TO [discovery_user] 4 GO

Note: The discovery user must belong to the administrator’s user group to discover databases successfully.

Setting up your MSSQL discovery job

  1. To begin discovering your MSSQL Databases [running on Windows], create a new Windows discovery job and be sure to enable database discovery by checking the Collect database server information checkbox. Select Allow Unencrypted Connections if you want to discover MSSQL 2005 or MSSQL 2008 databases.

Be sure to fill out both sets of credentials – the first set (Database Username / Password) to authenticate to the Microsoft SQL database itself and the second (Discovery Targets Credentials) to authenticate to the Windows server itself (like you’re used to for regular Windows-based discovery targets).

You can enter an ordered list of preferred Discovery Target(s) Credential(s) when you create a database discovery job. When the job runs, it will use the credentials in the order in which you enter them, stopping at the first successful authentication. Subsequent job runs use the last successful credential and then the remaining credentials in the ordered list.


Example job status

  1. Kick off your discovery job to test it (select "Run now" on the Job Screen). As MSSQL databases are detected, discovery will import a list of all instances, databases, and connection details. You can see the status of the discovery job on the job's setup page, as well - Scroll down to the "Job Status" section and look for the bar graphs.

Viewing MSSQL discovery job results

  1. Once the job finishes, you can view the results of your database discovery.

There are a couple of different ways to see the database details; the most direct is via the discovered MSSQL Application components themselves. Head to the Device42 main menu, Apps menu -> Application Components. If you don't see your SQL Server instances right at the top of the list, you can search for "SQL" to narrow the list down.

You can see the newly discovered SQL Server instances in the example below -- Click any one of the Application Component Names for more details:

MSSQL Application Components

Scroll to the bottom, and click the database instance name to view database details:

For a rundown of the database details discovery provides, jump to the "Available SQL database instance information" section.

Another way to view SQL Database details

Results are also available by browsing to the discovered Windows server instance's CI; either search for the device from the dashboard, via "Devices -> All Devices", or view your discovery job's results/score page and jump to the servers from there:

Click any of the "device names" (right-hand side) to jump to each discovered machine's details view. From the details view, you can simply click the "Services" tab to view a list of services, and can then click on the Microsoft SQL Server instance in that list.

Available SQL database instance information

On the "View Database Instance" screen, you will see lists containing the following items:

  • Databases on that instance (each clickable for individual DB connection & size stats)
  • Connections to that DB instance (connection details available for each connection)
  • Database size details, creation date, and path
  • Extra details, including server start time, CPU count, Memory info, and more

If your environment includes SQL cluster databases, discovery returns the following information about the clusters:

  • SQL Cluster name
  • SQL Cluster Node Role
  • Is Node Active or Read-Replica
  • Does Passive Node use Log Ship
  • Does Passive Node use DB Mirroring

A snippet from each section of the page can be seen below:

Oracle DB Discovery (on Windows and *nix targets)

Oracle database discovery (introduced in v16.04.00) is supported for Windows and *nix discovery targets and requires a separate set of credentials to authenticate to the database instance itself. Ensure these credentials have appropriate permissions to view the databases you are interested in discovering.

As of v16.14.01, Device42 now supports autodiscovery on Windows and *nix platforms for the following Oracle database versions.

Oracle 10g

  • Windows 32 bit
  • CentOS 32 bit

Oracle 11g

  • Windows 32 bit
  • Windows 64 bit
  • CentOS 32 bit
  • CentOS 64 bit

Oracle 12c

  • Windows 64 bit
  • CentOS 64 bit

Oracle 18c

  • CentOS 64 bit
  • Windows 64 bit

Oracle 19c

  • Windows 64 bit
  • CentOS 7 64 bit

As of v16.17.00, Device42 database autodiscovery for Windows and *nix targets supports discovery for Oracle RAC clustered database environments. This will help users better assess their cluster databases and will enable users to better understand all IT assets that are tied to critical business applications. Discovery returns data about the RAC configuration, the RAC database, and the nodes (physical servers) running the RAC software. You can run the autodiscovery against one or more nodes in the Oracle RAC and return information about all connected nodes. We require the use of sudo for Oracle discoveries, without it lockout is a risk.

Minimum Permissions Requirements for Oracle Discovery

For discovery to return detailed info about your database instance, you will require read/view permissions for the following system views/tables:

  • V$SESSION
  • V$DATABASE
  • V$CONTAINERS
  • DBA_SEGMENTS
  • DBA_OBJECTS
  • SYS.ALL_USERS
  • DATABASE_COMPATIBLE_LEVEL
  • SYS.PRODUCT_COMPONENT_VERSION

To get information about Pluggable Databases (PDBs) within a Container Database (CDB) in Oracle for non-DBA users, two key permission configurations are required:

  • SELECT permission on the V$CONTAINERS view.

  • Set container_data=all container=current for context configuration. For example:

    ALTER USER c##<username>
    SET
    CONTAINER_DATA = ALL
    CONTAINER = CURRENT;

Setting up your Oracle discovery job

  1. To begin discovering your Oracle databases, create a new discovery job for Windows or *nix (or both) targets, and be sure to check the Collect database server information checkbox.

Be sure to fill out both sets of credentials – the first set (Database Username / Password) to authenticate to the Oracle database itself and the second (Discovery Targets Credentials) to authenticate to the Windows or *nix server itself.

You can enter an ordered list of preferred Discovery Target(s) Credential(s) when you create a database discovery job. When the job runs, it will use the credentials in the order in which you enter them, stopping at the first successful authentication. Subsequent job runs use the last successful credential and then the remaining credentials in the ordered list.

  1. Kick off your new discovery job to test it (select Run Now on the Jobs screen). As Oracle databases are detected, discovery will import a list of all instances, databases, and connection details.

Viewing Oracle DB discovery job results

  1. Once the job finishes, you can view the results of your database discovery. There are a couple of different ways to see the database details; the most direct is via the discovered Oracle Application components themselves. On the Device42 main menu, select Apps -> Application Components. If you don't see your Oracle DB instances right at the top of the list, you can search for Oracle to narrow down the list.

Use the View Database Instances screen to view a list of discovered databases.

Scroll down through the screen to see a list of database connections and sizes.

You can also click on the database name on the View Database Instances screen to see its connection and size data.

On the Application Components screen, click Show to view the database Dependency Chart or an Impact Chart or List.

Oracle CDB/PDB Matching Process and Regular Processing Procedures

An Oracle database instance is created for each Container Database (CDB) and Pluggable Database (PDB), even in cases where they share the same endpoint listener.

We attempt to match any root database instance (CDB) with the same endpoint as our incoming resource (CDB or PDB). If we find a root database instance (CDB), we try to locate all related child databases (PDBs) for it and iterate through all of the child records. If any child database name matches the name of the incoming resource, we identify this database instance as the same as the incoming one and update the existing record with the new incoming resource data.

If we can't find any child database instance resources, we match the resource to the root resource. If there's no match to the root resource, we proceed with regular processing.

We don't aim to match orphaned PDBs to the CDB if a fresh CDB is incoming.

If a candidate database instance is detected to be a child of a CDB we skip the matching process

Postgres SQL DB Discovery (on *nix targets)

Postgres SQL database discovery (introduced in v17.11.00) is for *nix discovery targets and requires a separate set of credentials to authenticate to the database instance itself. Ensure these credentials have appropriate permissions to view the databases you are interested in discovering.

Minimum Permissions Requirements for Postgres SQL Discovery

For discovery to return detailed info about your database instance, you will need access to the tables:

  • pg_database
  • pg_tablespace
  • pg_stat_activity
  • init_server_addr (function)

Setting up your Postgres SQL discovery job

  1. To begin discovering your Postgres SQL databases, create a new discovery job for *nix targets, and be sure to enable database discovery by checking the Collect database server information checkbox.

Be sure to fill out both sets of credentials – the first set (Database Username / Password) to authenticate to the Postgres database itself and the second (Discovery Targets Credentials) to authenticate to the *nix server itself.

You can enter an ordered list of preferred Discovery Target(s) Credential(s) when you create a database discovery job. When the job runs, it will use the credentials in the order in which you enter them, stopping at the first successful authentication. Subsequent job runs use the last successful credential and then the remaining credentials in the ordered list.

  1. Kick off your new discovery job to test it (select Run Now on the discovery jobs screen). As Postgres databases are detected, discovery will import a list of all instances, databases, and connection details.

Viewing Postgres SQL DB discovery job results

  1. Once the job finishes, you can view the results of your database discovery. There are a couple of different ways to see the database details; the most direct is via the discovered Postgres application components themselves. On the Device42 main menu, select Applications -> Application Components. If you don’t see your Postgres DB instances right at the top of the list, you can search for Postgres to narrow down the list, or filter the list By Category > Database.

You can also select Resources > Databases > On-Prem Databases to display a list of databases.

DB2 Discovery (on *nix targets)

DB2 database discovery (introduced in v18.02.00) is for *nix discovery targets and requires a separate set of credentials to authenticate to the database instance itself. Ensure these credentials have appropriate permissions to view the databases you are interested in discovering.

Permissions for DB2 Discovery

For discovery to return detailed info about your database instance, you will require the following permissions:

1. Privileges for ENV_SYS_INFO:

  • SELECT or CONTROL privilege on the ENV_SYS_INFO administrative view
  • EXECUTE privilege on the ENV_GET_SYS_INFO table function

2. Privileges for SNAPAPPL_INFO:

  • SELECT or CONTROL privilege on the SNAPAPPL_INFO administrative view
  • EXECUTE privilege on the SNAP_GET_APPL_INFO_V95 table function OR DATAACCESS authority

3. Privileges for ENV_INST_INFO:

  • SELECT or CONTROL privilege on the ENV_INST_INFO administrative view
  • DATAACCESS authority OR DBADM authority OR SQLADM authority OR ACCESSCTRL authority OR SECADM authority

4. Privileges for DBCFG:

  • SELECT or CONTROL privilege on the DBCFG administrative view
  • DATAACCESS authority OR DBADM authority OR SQLADM authority

5. Privileges for CONTAINER_UTILIZATION:

  • SELECT or CONTROL privilege on the CONTAINER_UTILIZATION administrative view
  • DATAACCESS authority OR DBADM authority OR SQLADM authority OR ACCESSCTRL authority OR SECADM authority

6. Privileges for Monitoring:

  • EXECUTE privilege on the MON_GET_CONNECTION
  • DATAACCESS authority OR DBADM authority OR SQLADM authority

7. Privileges for Routines:

  • EXECUTE privilege on the GET_DBSIZE_INFO, WLM_SET_CONN_ENV, MON_GET_CONTAINER, and MON_GET_TABLESPACE routines
  • DATAACCESS authority OR DBADM authority OR SQLADM authority

Additional SELECT Privileges:

  • SELECT privilege on the SYSIBM.SYSTABLESPACES
  • SELECT privilege on the SYSIBM.SYSDUMMY1
  • SELECT privilege on the SYSIBM.SYSVERSIONS

Required Authorities:

  • One of the following authorities is required for overall access:
    • SYSMON
    • SYSMAINT
    • SYSCTRL
    • SYSADM

Setting up your DB2 discovery job

To begin discovering your DB2 databases, create a new discovery job for *nix targets, and be sure to enable database discovery by checking the Collect database server information checkbox.

Be sure to fill out both sets of credentials – the first set (Database Username / Password) to authenticate to the DB2 database itself and the second (Discovery Targets Credentials) to authenticate to the *nix server itself.

You can enter an ordered list of preferred Discovery Target(s) Credential(s) when you create a database discovery job. When the job runs, it will use the credentials in the order in which you enter them, stopping at the first successful authentication. Subsequent job runs use the last successful credential and then the remaining credentials in the ordered list.

Nix discovery jobNix discovery job

Viewing DB2 discovery job results

Once the job finishes, you can view the results of your database discovery. Discovered DB2 databases are added to the On-Prem Databases list page. On the Device42 main menu, select Resources -> Databases > On-Prem Databases to display the page. If you don’t see your DB2 instances, you can search for DB2 to narrow down the list.

Click on your DB2 instance name under the Resource Name column to see more information about that database.

On-prem list pageOn-prem list page

Database Connections Discovery Jobs

Device42 v18.01.00 adds a new Database discovery job type that you can use to discover information about databases in your environment. Database discovery is based on database connection information – including application components and DB server details – you supply for the job. Returned database information is added to the On-Prem Databases list page.

This database discovery job is useful in cases when the main detailed database discovery (in OS jobs) cannot determine the proper connection settings to the database. When this occurs, a database connection string for the discovery job is used instead as the connection settings are provided by the user.

note

If you have already discovered cloud databases using cloud autodiscovery jobs, and you then perform a Database Connections discovery using FQDN, Device42 will not duplicate the databases (which was the previous behavior).

  • Select Discovery > Database from the main menu to display the Database discovery list page.

  • Click on a Database discovery Name to view that job. Click +Add at the top right of the page to add a new Database discovery job.

  • Click Edit at the bottom right of the page to edit an existing job. Click +Add More to add additional Database Connection information for the job.

  • Enter or select the Database Connections information:

    • Name – Connection name
    • Application Component – Application Component to link to
    • Platform – Database type: Oracle, MS SQL, Postgres or DB2
    • Server –  IP address of the DB server
    • Port – DB server port
    • Credential – DB server access credentials
    • Extra – Specific to the database type: 
      • Instance for Oracle (required)
      • Database for Postgres and DB2 (required)
      • Not required for MS SQL
  • Use the trash can icon to remove a Connections line.

  • Scroll down the page to add or edit the Discovery Schedule for the job.

  • Click Save to save the job.

Database Discovery Job Scores

You can view Discovery Scores for Database jobs that have run.

  • Navigate to Analytics > Discovery Status > Discovery Scores to display the Discovery Scores page.

    Database Discovery menu navigationDatabase Discovery menu navigation

  • Click on the job you want to see under the Discovery Target column. You can use the search bar and/or the dropdown menu options next to it to find a specific database or narrow down your results.

    Discovery Scores pageDiscovery Scores page

  • Click on Detailed Discovery Scores to see additional information.

    Database discovery scoresDatabase discovery scores

View Discovered Databases

Discovered databases are added to the On-Prem Databases list page.

  • Select Resources > Databases > On-Prem Databases to display the list page.

  • Click on the name of the database you want to see in the Resource Name column.