Desperately trying to build a SQL query for a cacti add on

[JBs]

Hi all, I'm currently desperately trying to build a SQL query which outputs a list of graphs which are using/associated (to) a particular data source.

Known variables that can be used: the data source id (local_data.id) and the host id (local_data.host_id).

Here is an example of a very first query try which fetches all the graphs associated to a particular host:

Code: Select all

SELECT graph_local.id, 
graph_templates_graph.title_cache AS name 
FROM data_local, graph_local, graph_templates_graph 
WHERE data_local.id = '$data_source_id' 
AND data_local.host_id = '$host_id' 
AND data_local.host_id = graph_local.host_id 
AND graph_local.id = graph_templates_graph.local_graph_id 
ORDER BY graph_templates_graph.title_cache

Maybe it's not possible to associate graphs using a data query (this would not arrange me ) but I know that it can be done in the other way (list data sources associated to a graph). As I still don't know very well the cacti database's structure, I'm asking for cacti user's/creators' & Devs' ideas. Do not hesitate!

PS: maybe this thread is in the wrong forum section, sorry for that in this case.

[rony]

You need to use a join, the exact syntax, I will have to figure out when I get home.

[JBs]

Thanks Rony for the information.

I tried with a JOIN but I didn't find any common field between the data_ tables and the graph_ tables, except quite general ones like host_id :-/

I will wait till you come back home

[rony]

Use the following query to gather data based on host_id and data_local_id.

Code: Select all

SELECT DISTINCT
	data_local.host_id as host_id,
	data_local.id as data_source_id,
	graph_templates_graph.title_cache as graph_title,
	graph_templates_graph.local_graph_id as graph_id,
	data_template_data.name_cache as data_source_name,
	data_template_data.data_source_path
FROM
	data_local,
	data_template_rrd,
	graph_templates_item,
	graph_templates_graph,
	data_template_data
WHERE
	data_local.host_id = <<host id>> AND
	data_local.id = <<data source id>> AND
	data_local.id = data_template_rrd.local_data_id AND
	graph_templates_item.task_item_id = data_template_rrd.id AND
	graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id AND
	data_template_data.local_data_id = data_local.id
ORDER BY 
	data_local.host_id,
	data_local.id
;

Replace <<host id>> and <<data source id>> with the correct information.

This query will also tell you the associated RRD's and there names.

Also check out the database schema at http://www.cacti.net/downloads/docs/sql/

Good luck

[rony]

Um, make sure it works with non-templated graphs, I think it will, but I could be wrong.

[JBs]

Thanks a lot Rony!
It seems to be working pretty good at the moment. I don't have many graphs on my dev. machine but I'll test it on the prod machine in the early January.

Thanks also for the URL with the database schema! This is what was missing me