out there and back ( lcoal_data_id <-> local_graph_id)

Discussions on developing plugins for the Cacti Plugin Architecture

Moderators: Moderators, Developers

Post Reply
Author
Message
User avatar
johnrembo
Cacti User
Posts: 216
Joined: Mon Apr 24, 2006 3:33 am

out there and back ( lcoal_data_id <-> local_graph_id)

#1 Post by johnrembo » Thu Dec 20, 2007 9:43 am

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

User avatar
TheWitness
Developer
Posts: 14804
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:

#2 Post by TheWitness » Thu Dec 20, 2007 11:23 pm

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
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of MacTrack, Boost, CLog, SpikeKill, Platform RTM, DSStats, maintainer of Spine, lot's of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Central Plugin Repository
Central Templates Repository


I'm still out there people. Getting excited for Cacti 1.2. I think it will be a great release.

User avatar
johnrembo
Cacti User
Posts: 216
Joined: Mon Apr 24, 2006 3:33 am

#3 Post by johnrembo » Fri Dec 21, 2007 4:42 am

TheWitness wrote: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
Posts: 9
Joined: Thu May 08, 2008 5:31 pm
Location: Wellington NZ

graph_local.id or data_local.id and i want rrd_path

#4 Post by brian.nz » Sun Aug 02, 2009 4:54 pm

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

User avatar
johnrembo
Cacti User
Posts: 216
Joined: Mon Apr 24, 2006 3:33 am

#5 Post by johnrembo » Mon Aug 03, 2009 3:53 am

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'");
[url=http://forums.cacti.net/viewtopic.php?t=24934&highlight=zond]zond plugin[/url]
[url=http://forums.cacti.net/viewtopic.php?t=28384]memorize plugin[/url]
[url=http://forums.cacti.net/viewtopic.php?p=141322#141322]transporter plugin[/url]

brian.nz
Posts: 9
Joined: Thu May 08, 2008 5:31 pm
Location: Wellington NZ

#6 Post by brian.nz » Mon Aug 03, 2009 5:31 am

Awesome, much appreciated !!!!!!

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

Fransis
Posts: 4
Joined: Mon Sep 05, 2011 1:59 am
Contact:

Re: out there and back ( lcoal_data_id <-> local_graph_id)

#7 Post by Fransis » Wed Oct 05, 2011 5:08 am

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

frostyritz
Posts: 23
Joined: Wed Apr 03, 2013 8:17 pm

Re: out there and back ( lcoal_data_id <-> local_graph_id)

#8 Post by frostyritz » Tue Mar 22, 2016 1:47 pm

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.

Post Reply