Cacti Database schema

[Roke]

Im trying to know how cacti works for build a mini-addon and I am looking at the database schema ( http://www.cacti.net/downloads/docs/sql ... schema.pdf )

The data_template_data table its only linked at data_input.id and data_local.id that are not linked without another tables.

Its thats ok? Im triying to know how to get the rrd files with the User ID , ID of the graphic, and the id of the graphic template.

I think im going to take a look at the plugin report it but i want to inform of that posible mistake in database schema.

Best regards.

[Hyperlord]

Join-Optimized anyway ... looking forward for your Plug, what will it be?

[Roke]

Im trying to do a plugin for show all traffic by month to the users when loggin and see his traffic graphs. And buil a dynamic table with this data, so if the client wants to show the 2008 graphs the plugin show the table of the data of 2008.

I want to show this traffic in a table like:


------------------------------------------------------
| | January | February | March | April |
------------------------------------------------------
| In | 12 GB | 11 GB | 8 GB | 10 GB |
------------------------------------------------------
| Out | 8 GB | 9 GB | 4 GB | 5 GB |
------------------------------------------------------

[Roke]

I find the way to know all rrd files of the user if you know the graphic ID, this is SQL sentence:

"SELECT DISTINCT graph_templates_graph.title_cache AS graph_title, data_template_data.data_source_path AS rrd_path, user_auth.id AS user_id, graph_templates_graph.local_graph_id AS local_graph_id
FROM data_local, data_template_rrd, graph_templates_item, graph_templates_graph, data_template_data, host, user_auth, user_auth_perms
WHERE 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
AND data_local.host_id = host.id
AND user_auth.id = user_auth_perms.user_id
AND graph_templates_graph.local_graph_id = user_auth_perms.item_id
AND user_auth.id !=0
AND graph_templates_graph.local_graph_id =$YOUR_GRAPHIC_ID_NUMERIC
ORDER BY data_local.host_id, data_local.id
LIMIT 0 , 30"


This select is nice if you have only 1 rrd file but if you use pmacct and have 2 rrd files ( one for traffic in and another for traffic out ). How can you get this 2 files of the SQL diference?

The best sentence i get is:

"SELECT DISTINCT graph_templates_graph.title_cache AS graph_title, data_template_data.data_source_path AS rrd_path, user_auth.id AS user_id, graph_templates_graph.local_graph_id AS local_graph_id, graph_templates_item.text_format
FROM data_local, data_template_rrd, graph_templates_item, graph_templates_graph, data_template_data, host, user_auth, user_auth_perms
WHERE 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
AND data_local.host_id = host.id
AND user_auth.id = user_auth_perms.user_id
AND graph_templates_graph.local_graph_id = user_auth_perms.item_id
AND user_auth.id !=0
AND graph_templates_graph.local_graph_id =22
AND graph_templates_item.text_format LIKE '%Traffic%'
ORDER BY data_local.host_id, data_local.id
LIMIT 0 , 30"

That show another text value that says "Traffic In" and "Traffic Out" but if in your template dont have this values you dont get this result. Anyone knows a better way?

best regards.

[BSOD2600]

Are you aware of the ReportIt and/or dsstats plugins?

[Roke]

Yes, i know this plugins but i want it to show when the user are viewing the graphs and i want to show the data with the date that the user enter in ther form.