discovery.sqlQuery

discovery.sqlQuery(target, query[, database, db_type, port, address, query_parameters...])

Performs an SQL query on a relational database, returning a list of SQLResultRow nodes.

The function always takes the following parameters:

• target – the device or data source against which the request is performed. See also, discovery.dataSource.
• query – the SQL query to execute, potentially containing query parameters of the form %parameter%
• database – the name of the database to access
• db_type – the type of database: "db2", "ingres", "mysql", "oracle", "postgres", "sqlserver" or "sybase". If not specified, all database types for which there are matching credentials are attempted.
• port – the port number to connect to. If not specified, uses a database-specific default
• address – the particular IP address to connect to. If not specified, uses the address of the target device that is being scanned

In addition, if the query contains any query parameters, those parameters must be specified in the call. For example:

lookup_datatype := "example type";
rows := discovery.sqlQuery(host, raw "select Name, Size from datasizes where type=%datatype%",
                           database := "example",
                           datatype := lookup_datatype);

Note how the query is specified as a raw string, so the % interpolation is not performed by the TPL evaluation, but rather is passed through to the underlying database as a query parameter. To avoid mistaken use of TPL string interpolation, it is an error to perform a TPL string interpolation within the query parameter. In rare cases where the SQL query itself must be constructed dynamically, for example to dynamically choose a table name, the query string must be constructed and assigned to a variable outside the discovery.sqlQuery call:

table_name := // table name from somewhere

// Construct the query. table_name is interpolated here by TPL;
// the type will be passed as a query parameter and handled by the database.
dynamic_table_query := "select value from %table_name% where type=%%item_type%%";

rows := discovery.sqlQuery(host, dynamic_table_query,
                           database := "example",
                           type     := lookup_type);

The return value is a list of SQLResultRow nodes, or none if the query failed. Each node has attributes named after the selected columns, converted to lower case. So in the example above, each row node has attributes "name" and "size".