out there and back ( lcoal_data_id <-> local_graph_id)

[johnrembo]

Hi,

could anyone please give me sql select which could return local_graph_id by local_data_id:

short form looks like:

select xxx.local_graph_id
from yyy
where local_data_id=5

cacti sources are full of reverse solutions, when local_data_id is extracted by knowing local_graph_id, but this isn't what I need.

thank you

[TheWitness]

It's a pain in the you know what. I did not write it, so I will not take the blame We hope to "FIX" it one day.

Either way, try this:

Code: Select all

SELECT DISTINCT
graph_local.id, data_local.id
FROM graph_local 
INNER JOIN ((data_template_rrd 
INNER JOIN graph_templates_item 
ON data_template_rrd.id=graph_templates_item.task_item_id) 
INNER JOIN data_local 
ON data_template_rrd.local_data_id=data_local.id) 
ON graph_local.id = graph_templates_item.local_graph_id;

TheWitness

[johnrembo]

It's a pain in the you know what. I did not write it, so I will not take the blame We hope to "FIX" it one day.

Either way, try this:

Code: Select all

SELECT DISTINCT
graph_local.id, data_local.id
FROM graph_local 
INNER JOIN ((data_template_rrd 
INNER JOIN graph_templates_item 
ON data_template_rrd.id=graph_templates_item.task_item_id) 
INNER JOIN data_local 
ON data_template_rrd.local_data_id=data_local.id) 
ON graph_local.id = graph_templates_item.local_graph_id;

TheWitness

merry cristmas and gracias!

waiting for a new cactus plant in santas sock...

[brian.nz]

Ok here is a tricky one. Sorry i have not quite got my head wrapped around the database layout yet.

i have graph_local.id and and i need to find all the rrd's for that graph_id.

i know a good place to get the rrd info from would be poller_item.

Anybody dare give a select statement with all the relative joins to get that info out of poller_item.

Thanx in advance
Brian

[johnrembo]

source from zond_init.php (http://forums.cacti.net/viewtopic.php?t=24934)

Code: Select all

//extracts all local_data_ids associated with current local_graph_id
//COMMENTS - > COMMENT#1
$l_d_i=db_fetch_assoc('select
			data_template_rrd.id as id,
			data_template_rrd.local_data_id,
			data_template_rrd.data_source_type_id,
			data_template_rrd.data_source_name,
			data_template_rrd.data_input_field_id,
			graph_templates_graph.lower_limit,
			graph_templates_graph.upper_limit,
			graph_templates_graph.auto_scale,
			graph_templates_graph.auto_scale_opts
			from
			graph_templates_item
			left join data_template_rrd on (graph_templates_item.task_item_id=data_template_rrd.id)
			left join graph_templates_graph on (graph_templates_graph.local_graph_id = graph_templates_item.local_graph_id)
			where (graph_templates_item.local_graph_id='.$local_graph_id.' and local_data_id <> "")
			group by data_template_rrd.id order by sequence
		    ');
//print_r($l_d_i);die;

//extracts poller_items for each local_data_id
//COMMENTS - > COMMENT#2
foreach ($l_d_i as $ldi) {
    $local_data_id=$ldi['local_data_id'];
    $ds_type=$ldi['data_source_type_id'];

    $poller_items = db_fetch_assoc("select 
				    ".$ldi['id']." as 'id',
				    local_data_id, 
				    host_id,
				    action,
				    hostname,
				    snmp_community, 
				    snmp_version,
				    snmp_username,
				    snmp_password,".
				    (($V=="0.8.7") ? 
				    "snmp_auth_protocol,
				    snmp_priv_passphrase,
				    snmp_priv_protocol,
				    snmp_context," : "").
				    "snmp_port, 
				    snmp_timeout, 
				    rrd_name, 
				    rrd_path, 
				    arg1,
				    graph_templates_item.consolidation_function_id as cf_id
				    from poller_item
				    left join graph_templates_item on graph_templates_item.task_item_id=${ldi['id']}
				    where local_data_id='$local_data_id'");

[brian.nz]

Awesome, much appreciated !!!!!!

Now hopefully i can complete my forecasting plugin !!!!

[Fransis]

Thanks for the really great job!! thought that would never solve the problem!!

[frostyritz]

Anyone know if theres an easier way to get rrd and local_graph_id other then the crazy select distinct lol has it been easier to tag.


I gotta seach from Interface names and its hard to mix/match.