Discovering content from databases

You can discover content from databases by using data sources. You write patterns that specify the target database, including a driver, and provide queries to extract the information required. You can use data sources to query an asset database to find the location of Hosts as they are discovered.

The following database data sources are provided. Additional database data sources may be added in subsequent TKUs:

• IBM Db2

• Ingres

• Microsoft SQL Server

• MySQL

• Oracle

• PostgreSQL

• Sybase

• Other Database

To discover content from databases

The basic method for discovering content from databases is the same for each database data source type:

1. Configure the database data source, including:
   1. The database driver.
   2. The database to query.
2. Write a pattern which specifies: 
   1. The data source by name.
   2. A query to access the required information.
3. Upload and activate the pattern.
4. Scan the endpoint.

Queries

The query is an SQL query for the database, specified by using the discovery.sqlQuery TPL function.

To retrieve data from a database by using an SQL query

This procedure shows how to perform an SQL query by using a database data source. The method is the same for all databases, though the parameters used to configure the data source differ for each database type.

1. To create a database data source, from the main menu, click Manage > Data Sources.

2. Click Add and select a database from the menu.
   In this example, IBM Db2 is selected from the menu.

3. For details on how to add a data source, see Adding data sources.
   The following are example parameters:
   • Name—the name of the data source. For example, "Db2 tideway DS". This is how patterns refer to and use the data source. 
   • Endpoint—the address (IPv4 or IPv6) of the host, or a resolvable hostname.
   • Description—a free text description that is displayed on the Data Sources page.
   • Username—the user name with which to access the host.
   • Password—the corresponding password.
   You also need to add parameters specific to the type of database you are accessing. For example, for IBM Db2, you need the following parameters. They are described fully, for each database type, in Adding data sources:

   • Driver 

   • Port 

   • Database Name 

   • Timeout

   • Credential Group 

   • Additional Properties

   
4. Write a pattern that uses the data source you created and a query:

   1. Specify the data source:

      ds_base := discovery.dataSource("Db2 tideway DS");
      

   2. Specify the query: 

      \\ returns the result of the specified SQL query
      result := discovery.sqlQuery(ds_base, raw "select hostname, address, location, owner from HOSTS where hostname = %hostname%", hostname := host.name); 
      

5. Upload and activate the pattern.

6. Scan the host.

To view the information discovered

You can see the data source accesses for each discovery run that uses data sources. To do this:

1. From the Discovery Access section in the Host page, click the latest Discovery Access.
2. From the top row of the Endpoint section, select the Discovery Run.
3. From the Status section, select  n Data Source Accesses Related.  
   The Data Source Access List page is displayed. If there is only one, the Data Source Access page is displayed.
4. Select the Data Source Access you want to view.