More vCenter Operations Manager fun: General SQL Adapter!

I’m continuing my work with vCenter Operations Manager and this time I spent time with another powerful adapter available: the General SQL Data Loader adapter.

As the name implies, it’s a generic connector that enable vCenter Operations Manager to pull data from a Database, internally it uses JDBC and the bundled libraries make it compatible with:

  • Mysql (5.0)
  • Microsoft SQL Server
  • Oracle
  • PostgreSQL
  • DB2

Basically to get the adapter working you need to specify these three things: an SQL query to retrieve the resources, an SQL query to gather the metrics to be collected and, optionally, an SQL query that performs parent-child relationships with the resources you’re collecting from the DB.

Installing the adapter is quite simple, there’s a document called SQL Loader Adapter Installation and Configuration Guide (available on ftp.integrien.com) that explains step-by-step the installation process.

I decided to familiarize myself with the adapter by creating a connector for Zabbix, a popular open source Monitoring solution that is widely used in the enterprise here, I just scratched the surface with these queries, because there’s probably more info to pull (like events and parent-child relationship), but if you’re looking for a quick and dirty solution for Zabbix, or a real-world example you can use to develop your own custom connector using the General SQL Data Loader adapter, I’m sure these SQL queries will be handy.

DISCOVER_DB_QUERY_1.sql

SELECT DISTINCT 
hosts.host as RESOURCENAME, 
hosts.name as RESOURCEKIND, 
"host.name" as IDENTKEY1, 
hosts.name as IDENTVALUE1, 
"host.key" as IDENTKEY2, 
hosts.hostid as IDENTVALUE2 
FROM hosts
WHERE hosts.status = 0

DB_QUERY_1.sql

SELECT 
hosts.host AS RESOURCENAME, 
hosts.name AS RESOURCEKIND, 
history.clock AS "TIMESTAMP", 
"host.name" AS IDENTKEY1, 
hosts.name AS IDENTVALUE1, 
"host.key" AS IDENTKEY2, 
hosts.hostid AS IDENTVALUE2, 
SUBSTRING_INDEX(items.key_, '.', -2) AS METRICNAME1, 
history.value AS VALUE1

FROM history LEFT JOIN (items, hosts)

ON (items.itemid=history.itemid AND hosts.hostid=items.hostid)

WHERE (history.clock >= %f) AND (history.clock < %t)

UNION

SELECT 
hosts.host AS RESOURCENAME, 
hosts.name AS RESOURCEKIND, 
history_uint.clock AS "TIMESTAMP", 
"host.name" AS IDENTKEY1, 
hosts.name AS IDENTVALUE1, 
"host.key" AS IDENTKEY2, 
hosts.hostid AS IDENTVALUE2, 
SUBSTRING_INDEX(items.key_, '.', -2) AS METRICNAME1, 
history_uint.value AS VALUE1

FROM history_uint LEFT JOIN (items, hosts)

ON (items.itemid=history_uint.itemid AND hosts.hostid=items.hostid)

WHERE (history_uint.clock >= %f) AND (history_uint.clock < %t)

Comments and feedback are absolutely welcome!