Thursday, March 8, 2012

Monitoring Custom Data from DB Queries

There is an interesting little feature in RHQ that I thought I would quickly mention here. Specifically, it's a feature of the Postgres plugin that let's you track metrics from any generic query you specify.

Suppose you have data in your database and you want to expose that data as a metric. For example, suppose you want to track the total number of users that are currently logged into your application and that information is tucked away in some database table that you can query.

Import your Postgres database into RHQ and manually add a "Query" resource under your Postgres Database Server resource (see the image below where the "Import" menu provides you with the names of the resource types you can manually add as a child to the database server resource - in this case, the only option is the Query resource type).

When you "import" this Query resource through the manual add feature, you will be asked for, among other things, the query that you want to execute that extracts your metric data.

Once you do, you'll have a new Query resource in your RHQ inventory that is now tracking your metric value like any other metric (e.g. you will be able to see the historical values of your data in the graph on the Monitoring tab; you'll be able to alert on those values; etc.)

The one quirky thing about this is the query needs to return a single row of two columns - the first column must have a value of "metricColumn" (literally) and the second column must be a numeric value. To follow the earlier example (tracking the number of users currently logged in), it could be something like:

SELECT 'metricColumn', count(id) FROM my_application_user WHERE is_logged_in = true

That's it. A pretty simple feature, but it seems like this could have a wide range of uses. Hopefully, this little tidbit can spark an idea in your head about how you can use this feature while monitoring your systems.