I'm trying to formulate a single query to return a nice list of a host's interfaces and the interfaces attributes from the Cacti MySQL DB. Here are the tables I am referencing:
host_snmp_cache:
host_id - field_name - field_value - snmp_index
host:
id - description - hostname
And this is the goal output column format:
mysql> some sql wizardry;
+--------+---------------+-------+-------------+--------+-----------------------+
| Host | Ip Address | Index | Interface | Status | Description |
+--------+---------------+-------+-------------+--------+-----------------------+
| MyHost | 10.9.3.39 | 527 | ge-1/3/3 | Up | Some description here |
+--------+---------------+-------+-------------+--------+-----------------------+
...snip...
This is the hardest SQL Query I've tackled so far, and I keep running into syntax errors. A big stumbling block is the host_snmp_cache
table that contains key/value pairs in the form of field_name
and field value
.
So, for example, here is some data from the DB:
mysql> select id, hostname, description from host where id = 118;
+-----+-------------+---------------+
| id | hostname | description |
+-----+-------------+---------------+
| 118 | 10.9.3.39 | MyHost |
+-----+-------------+---------------+
1 row in set (0.00 sec)
mysql> select snmp_index, host_id, field_name, field_value
from host_snmp_cache
where host_id = 118 AND snmp_index = 527;
+------------+---------+--------------+----------------------------------------------------+
| snmp_index | host_id | field_name | field_value |
+------------+---------+--------------+----------------------------------------------------+
| 527 | 118 | ifAlias | Some description here |
| 527 | 118 | ifDescr | ge-1/3/3 |
| 527 | 118 | ifHwAddr | XX:XX:XX:XX:XX:XX |
| 527 | 118 | ifIndex | 527 |
| 527 | 118 | ifName | ge-1/3/3 |
| 527 | 118 | ifOperStatus | Up |
| 527 | 118 | ifSpeed | 1000000000 |
| 527 | 118 | ifType | ethernetCsmacd(6) |
+------------+---------+--------------+----------------------------------------------------+
8 rows in set (0.01 sec)
Of those field_name values, the only ones I am interested in is ifAlias
,ifName
and ifOperStatus
.
So, I can get the data with a dozen queries but I wanted to know if it's possible to get the data out in a more structured output with a few JOINs or something like that.
Here is a query I've gotten so far, but I can't figure out how to make the query properly due to the snmp_index
field causing some confusion (what to I equate it to?):
SELECT
host.description AS Hostname,
host.hostname AS 'Ip Address',
ifIdx.field_value AS 'SNMP Index',
ifStatus.field_value AS Status,
ifDesc.field_value AS Desc
FROM host
JOIN host_snmp_cache AS ifIdx
USING (ifIdx.snmp_index)
ON host.id = ifIdx.host_id
WHERE ifIdx.snmp_index = ??
JOIN host_snmp_cache AS ifStatus
...etc...
Here is my MySQL version:
[brian@meow ~]$ mysql -v
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 527176
Server version: 5.0.82sp1 Source distribution
I apologize if I'm asking for too much, or if this query is flat out impossible to do in one step.
Any advice or help is greatly appreciated!
Best Answer
It is possible in one query. The trick is to use the same table more than once in the queury:
(SQL Fiddle - http://sqlfiddle.com/#!2/63ac9/18)
If you look you'll see that I'm joining to the
host_snmp_cache
table 3 times, each time with a different alias (ifIdx
,isStatus
,ifDesc
). Then for each 'table' I use a where clause to filter for the required value/field from that table.