Database Monitoring

As an example, consider the following Firebird/Interbase database monitoring task. Say, we want to check charsets and collations of string fields in tables.

The corresponding SQL request is as follows:

select rf.rdb$relation_name relation, rf.rdb$field_name table_field,

      f.rdb$field_name field_domain, f.rdb$field_type field_type,

      cs.rdb$character_set_name character_set,

      c.rdb$collation_name collation_name

from

 rdb$fields f, rdb$character_sets cs,

 rdb$collations c, rdb$relation_fields rf

where

  cs.rdb$character_set_id = f.rdb$character_set_id and

  c.rdb$collation_id = f.rdb$collation_id and

  c.rdb$character_set_id = cs.rdb$character_set_id and

  f.rdb$field_name = rf.rdb$field_source

  and f.rdb$character_set_id is not null

  and rf.rdb$relation_name not starting with 'RDB$'

order by 1, 2

Assume there is a registered SQL Driver device with org.firebirdsql.jdbc.FBDriver as Database Driver, jdbc:firebirdsql:<dbHost>/3050:<dbPath> as Database URL (where <dbHost> and <dbPath> are substituted with your database host and path), and correct Database Username and Database Password values. Then the request can be added in Queries tab providing, say, charsets as Name and Charsets and Collations as Description.

Once the query is configured, database monitoring results (in the Device Configuration table) will have Charsets And Collations property containing the query result. Now you can identify incorrect charsets and collations according to your custom rules and add alerts or process them in some other way.