Mapping Graph titles to RRD files via SQL

[belsamber]

I've spent a number of hours searching and plugging away at the SQL DB for Cacti, but as yet I can't find a way to generate a list of graph titles mapped to RRD file paths... I think my SQL skills just aren't up to the task... Does anyone with a good handle on the Cacti DB know how to do it?

I can get a mapping from RRD file to data source name:

Code: Select all

mysql> SELECT data_local.id, data_local.host_id, host.description, host.hostname, data_template_data.data_source_path, data_template_data.name_cache
    -> FROM data_local, data_template_data, host
    -> WHERE data_local.id = data_template_data.local_data_id
    -> AND data_local.host_id = host.id
    -> ORDER BY data_local.id;
+-----+---------+---------------------+------------+-----------------------------------------------------+-------------------------------------------------------+
| id  | host_id | description         | hostname   | data_source_path                                    | name_cache                                            |
+-----+---------+---------------------+------------+-----------------------------------------------------+-------------------------------------------------------+
|   1 |       1 | Local Linux Machine | localhost  | <path_rra>/local_linux_machine_proc_1.rrd           | Local Linux Machine - Processes                       |
|   2 |       1 | Local Linux Machine | localhost  | <path_rra>/local_linux_machine_load_1min_2.rrd      | Local Linux Machine - Load Average                    |
|   3 |       1 | Local Linux Machine | localhost  | <path_rra>/local_linux_machine_users_3.rrd          | Local Linux Machine - Logged in Users                 |
|   4 |       1 | Local Linux Machine | localhost  | <path_rra>/local_linux_machine_mem_buffers_4.rrd    | Local Linux Machine - Memory - Free                   |
|   5 |       1 | Local Linux Machine | localhost  | <path_rra>/local_linux_machine_mem_swap_5.rrd       | Local Linux Machine - Memory - Free Swap              |

And also I can find the graph title-cache:

Code: Select all

mysql> select id,local_graph_id,title_cache from graph_templates_graph;
+-----+----------------+---------------------------------------------------------------------------+
| id  | local_graph_id | title_cache                                                               |
+-----+----------------+---------------------------------------------------------------------------+
<cut>
|  34 |              1 | Local Linux Machine - Processes                                           |
|  35 |              2 | Local Linux Machine - Load Average                                        |
|  36 |              3 | Local Linux Machine - Logged in Users                                     |
|  37 |              4 | Local Linux Machine - Memory Usage                                        |

But I can't for the life of me figure out how to relate them to each other...

Note also that while the data source name_cache and the graph title_cache actually match in the example above, they don't match when I'm looking at customer interfaces. Data source name is |host_description| - Traffic - |query_ifIP| - |query_ifName| where graph title is |host_description| - Traffic - |query_ifName| |query_ifAlias| (and I can't change these for historical reasons.)

So does anyone know how to relate data_template_data.data_source_path to graph_templates_graph.title_cache? Or any other way to easily generate a list of graph titles matched to RRD file names?

[cigamit]

This is a bit old (0.8.7 !!) but it will give you a little bit of an idea of how things map.
http://docs.cacti.net/_media/manual:087 ... 0.8.7i.pdf

Also, you should keep in mind that a single graph may link to multiple rrd files depending on the data sources attached to it.

[belsamber]

Thanks - I did actually see that a while back. From what I can tell I have to map through about 4 different tables to link the two items and my SQL-fu taps out at about two tables... Hence the cry for help