JDBC Data Collection

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:

jdbc:oracle:thin:@//##HOSTNAME##:1521/##DBNAME##

jdbc:postgresql://##HOSTNAME##:##WILDVALUE##/##DBNAME##

jdbc:sqlserver://##HOSTNAME##:##DBPORT##;databaseName=##DBNAME##;integratedSecurity=true

jdbc:mysql://##HOSTNAME##:3306/##DBNAME##?connectTimeout=30000&socketTimeout=30000

jdbc:sybase:Tds:##HOSTNAME##:##DBPORT##/##DBNAME##

Please note that SyBase URLs do not require slashes before the hostname. 

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.

Notes:

• 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. 

To measure how long a query took to run, add a datapoint and set the source to ‘Query execution time in milliseconds’:

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:

1. 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.
2. 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+)”.

e.g.

3.   look for presence of a string: the text match post processor looks for the presence of the text specified in the post-processor parameter in the SQL result, and returns 1 if the text is present, and 0 if it is not: