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_id - field_name - field_value - snmp_index


id - description - hostname

And this is the goal output column format:

mysql> some sql wizardry;
| Host   | Ip Address    | Index |  Interface  | Status | Description           |
| MyHost |     |  527  |  ge-1/3/3   |   Up   | Some description here |

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 |   | 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?):

    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

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!

  • Are there different SNMP indexes belonging to the same host? If yes, are there different field_name-field_value pairs between the different indexes belonging to the same host? If yes, which one do you want to choose? Where do you get the IP address from? – dezso Nov 24 '12 at 17:48
  • And, which is just as important, you don't have to apologize - this is a good question. You need to dive into MySQL query syntax though, with focus on what should go into the FROM and WHERE clauses :) – dezso Nov 24 '12 at 18:07
  • Yes, multiple index numbers per host, one per interface via the key/pair table. Ip address is stored in the host.hostname variable, and the actual hostname is in host.description. – Brian Nov 24 '12 at 20:21

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

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

  • That's one hell of a query! It works! Thank you! – Brian Nov 24 '12 at 19:33
  • While it works, it makes a ton of duplicate results. Is there a way to make the snmp_index unique per host? Right now it spits out the same index for every interface in the table, multiplied by every interface. How do you restrict it so: ON host.id = ifIdx.host_id if ifIdx.field_value = whatever value the snmp index is for that table row. – Brian Nov 24 '12 at 20:18
  • Hi, sorry about that. I'd assumed a little too much about your data. I've added some edits to my answer that should solve those issues. I've added a where clause to let you specify the hostname, and then where clauses for each of the 3 joined tables to filter to snmp_index 527 as per your example. – Stuart Moore Nov 24 '12 at 20:29
  • The key-value schema pattern is BAD. Don't do it. Lots of programmers fall into that trap. Here is a discussion of what will go wrong, and a compromise solution: mysql.rjweb.org/doc.php/eav – Rick James Nov 29 '12 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.