Complex SQL Query

Question

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!

Answer 1

It is possible in one query. The trick is to use the same table more than once in the queury:

SELECT 
    host.description AS Hostname,
    host.hostname AS 'Ip Address',
    ifIdx.snmp_index AS 'SNMP Index',
    ifIdx.field_value AS 'Interface',
    ifStatus.field_value AS Status,
    ifDesc.field_value AS Description
FROM host
JOIN host_snmp_cache AS ifIdx
    ON host.id = ifIdx.host_id
JOIN host_snmp_cache as ifStatus
    ON host.id=ifStatus.host_id
JOIN host_snmp_cache as ifDesc
    ON host.id=ifDesc.host_id
where 
    host.description='MyHost' 
    and ifIdx.field_name='ifDescr'
    and ifIdx.snmp_index='527'
    and ifStatus.field_name='ifOperStatus'
    and ifStatus.snmp_index='527'
    and ifDesc.field_name='ifAlias'
    and ifDesc.snmp_index='527'

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