JDBC Data Collection
The JDBC collector allows you to create datasources that query databases, collect and store data from a SQL query, alert on them, and graph them.
If you select a Collector type of jdbc (in the datasource General Information section), the form will display JDBC specific attributes section:
URL is the jdbc url used to connect to the database. As with other fields, token substitutions can be used to make the url generic (Typically, the ##HOSTNAME## token is used to substitute the host that the datasource is associated with; ##WILDVALUE## will be used in Active Discovery datasources, and be replaced with the port or ports that the database was discovered running on; ##DBNAME## can be used to define the database to connect to, and may vary from host to host, or group to group. Of course, any property can be used at any place in the string, or a literal string can be used, too.)
Examples of URLs:
- Please note that SyBase URLs do not require slashes before the hostname.
Username & Password
Username and password are the credentials used to connect to the database. They can be entered as literals, specific to this datasource, but are typically filled with the same tokens that are used by the rest of the LogicMonitor system - ##jdbc.DBTYPE.user## and ##jdbc.DBTYPE.pass##, where DBTYPE is mysql, oracle, postgres or mssql. This allows credentials to be specified individually for each host or group, if desired, simply by setting the property at the appropriate level.
- Microsoft SQL Server offers 2 different authentication modes, Windows and SQL Server authentication. Windows authentication uses the Active Directory user account, while SQL authentication uses an account defined within SQL Server management system. If you want to use Windows authentication, do not define a username or password in the datasource. The collector's Run as credentials are used to access the database. You cannot override this at the host level. If you use SQL Server authentication, the username and password defined in the datasource must have the rights to execute the query you want to run. For more information about authentication modes, see Choose an Authentication Mode on the MSDN site.
- If you are using a MySQL database, ensure the password does not contain the backslash (\) character.
Query is the SQL statement to be run against the database. We support most valid SQL statements. However, due to the Collector's use of the executeQuery method, SQL statements which do not return a result set are not supported for JDBC data collection. Such statements include those using 'INSERT', 'DELETE', or 'UPDATE', as well as 'ALTER', 'CREATE', 'DROP', 'RENAME', or 'TRUNCATE'.
Please note that queries ending in semicolons (as shown in the above image) are commonplace- and sometimes required- when executed in Oracle via the SQL Developer/CLI. However, some JDBC drivers will reject queries that end with a semicolon, resulting in an error. For troubleshooting purposes, if your query returns an error when using JDBC drivers, please verify the compatibility of the driver with the use of semicolons.
Defining JDBC Datapoints
Like all datasources, you must define at least one datapoint for JDBC datasources. See Datapoint Overview for more information on configuring datapoints.
Measuring the Query Response Time
To measure how long a query took to run, add a datapoint and set the source to 'Query execution time in milliseconds':
Interpreting the Query Result
To interpret the query result, set the Use Value Of field to "Output". There are three post processor methods that can be used to interpret SQL results:
- use the value of a specific column of the first row in the result: this post processor is the simplest to use. It expects a column name as the post-processor parameter, and returns the value of the first row for that column.
- a regular expression to extract a value: if you have multiple rows of output in your returned sql, you will probably need the regex postprocessor. It expects a regular expression as its post-processor parameter, with the expression returning a backreference indicating the numeric data to store.
E.g. a query of the form:
select application from applicationevents, count(*) as count where eventdate > NOW() - INTERVAL 1 hour GROUP BY application could return results such as:
When the logicmonitor collector is processing these results, it will prepend the column name to each value, for ease of regular expression construction. So the above result would be processed as:
The values reported for phonesales could be collected by defining a regular expression in the post-processor parameter of "phonesales,count=(\d+)".