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