Database structure help required

[Polaris75]

I've been pulling my hair out for a couple of days trying to connect the dots here, and I've finally given up trying to figure it out on my own ... help would be GREATLY appreciated!

I need to correlate graphs, based on description, to either local_data_id or, if I've gone that far, data_source_path, and I can't quite seem to connect the dots ...

I need to get from here:

Code: Select all

mysql> select id, local_graph_id, title_cache from graph_templates_graph where title_cache like "%ROUTER1%-%7/0/1%";
+------+----------------+-------------------------------------------------+
| id   | local_graph_id | title_cache                                     |
+------+----------------+-------------------------------------------------+
| 5866 |           5814 | ROUTER1 - PO7/0/1 To ROUTER2 POS7/0/1           |
+------+----------------+-------------------------------------------------+
1 row in set (0.05 sec)

mysql>

to here:

Code: Select all

mysql> select id, local_data_id, name_cache, data_source_path from data_template_data where local_data_id = 5793;
+------+---------------+----------------------------------+---------------------------------------------+
| id   | local_data_id | name_cache                       | data_source_path                            |
+------+---------------+----------------------------------+---------------------------------------------+
| 5864 |          5793 | ROUTER1 - Traffic - PO7/0/1      | <path_rra>/router1_traffic_in_5793.rrd      |
+------+---------------+----------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

and I keep running into dead ends ... if someone could tell me how to it would be greatly appreciated. Thanks!

[Polaris75]

YAY! Nevermind! Found it FINALLY!

Code: Select all

mysql> select id, local_graph_id, title_cache from graph_templates_graph where title_cache like "%ROUTER1%-%7/0/1%";
+------+----------------+-------------------------------------------------+
| id   | local_graph_id | title_cache                                     |
+------+----------------+-------------------------------------------------+
| 5866 |           5814 | ROUTER1 - PO7/0/1 To ROUTER2 POS7/0/1           |
+------+----------------+-------------------------------------------------+
1 row in set (0.05 sec)

mysql>

Code: Select all

mysql> select * from graph_local where id = 5814;
+------+-------------------+---------+---------------+------------+
| id   | graph_template_id | host_id | snmp_query_id | snmp_index |
+------+-------------------+---------+---------------+------------+
| 5814 |                 2 |     143 |             1 | 29         |
+------+-------------------+---------+---------------+------------+
1 row in set (0.00 sec)

mysql>

Code: Select all

mysql> select * from data_local where host_id = 143 and snmp_index = 29;
+------+------------------+---------+---------------+------------+
| id   | data_template_id | host_id | snmp_query_id | snmp_index |
+------+------------------+---------+---------------+------------+
| 5793 |               41 |     143 |             1 | 29         |
+------+------------------+---------+---------------+------------+
1 row in set (0.01 sec)

mysql>

Code: Select all

mysql> select id, local_data_id, name_cache, data_source_path from data_template_data where local_data_id = 5793;
+------+---------------+----------------------------------+---------------------------------------------+
| id   | local_data_id | name_cache                       | data_source_path                            |
+------+---------------+----------------------------------+---------------------------------------------+
| 5864 |          5793 | ROUTER1 - Traffic - PO7/0/1      | <path_rra>/router1_traffic_in_5793.rrd      |
+------+---------------+----------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

[lcano]

Something like this?

http://forums.cacti.net/viewtopic.php?t=37852

[gandalf]

For your convenience, I just uploaded the database design to http://docs.cacti.net/manual:087:99_reference.db_design
R.

[Polaris75]

Something like this?

http://forums.cacti.net/viewtopic.php?t=37852

Yep

I did search first, but for 'database', so I missed that Thanks

[Polaris75]

For your convenience, I just uploaded the database design to http://docs.cacti.net/manual:087:99_reference.db_design
R.

Sweet! Thanks!