Data Source Import Script for NON-CACTI created RRD's

[o019676]

My first attemp at a contribution.

BACKUP YOUR CACTI DATABASE FIRST!!!!
THIS IS BETA CODE FOR NOW!!!!!
TREAT IT AS SO!!!

Feel free to email me any changes you might want or bugs in the script.
I will update accordingly and repost!!

webwillow@thewebwillow.com
donald.t.gullett@jpmchase.com

standby for a add_graph script for adding graphs for these as well!!

Make a data template for your RRD's that you want to mass import.
YOU MUST HAVE A DATA TEMPLATE FIRST...VERY IMPORTANT!!

BIG NOTE!! you can only make one data source per host per data template, i made this part so that i dont accidently create the data source twice. and so i can do a mass import and if the datasource for that host and template is already created, it will skip on to the next one. I run the script every morning.

download this script (bash script)

gunzip -dc <filename>|tar xvf -

chmod 755 add_datasource.sh

edit the global file and add your configuration (i.e. where mysql is etc)
i use nagios with this and other scripts to import nagios hosts that use the global file, remove those if you wish, shouldnt hurt it if you leave them there.

use the -h or --help option to get how to use the script.
./add_datasource.sh --list
note the number of the data template you want
./add_datasource.sh --hostlist
note the number of the host you want
note the location of your rrd file
./add_datasource.sh -D <data template number> -H <hostid> --rdd <rrd file>

example:

./add_datasource.sh -D 61 -H 111 -rrd /usr/local/nagios/nagiosgraph/rrd/gti0l012_ROOT_disk.rrd

also can be used to list host id numbers, data template id's and existing data source id's with the correct option

so far works great for me!!!

[o019676]

NOTE: ALWAYS BACKUP YOUR CACTI DATABASE BEFORE USING THIS SCRIPT!!!!!!!

THIS IS BETA SCRIPT AND SHOULD BE USED AS SO!!!!!!

Attached file has an addition script to the above posted file.

Use one file to add the datasource and the other to add the graph to the datasource.

You must have a data template and a graph template created in cacti itself for the adds to work.

Also make sure that you make the data template correctly with the template pointing to the datasource cdef's (i think thats the correct way to say it).

The script dredges the mysql database for the templates to properly add the datasource and the graphs properly.

I just ran 10 test of adding over 1000 graphs that are attached to nagios created RRD's via nagiosgraph with a seperate script which i will provide later. I would include it here as well but some finishing touches and much needed input from other users are need before i post the code with the nagios script.

Always use -h or --help with this script on the command line to get help on the use.

Just like the other script, you must provide a graph template number, host id, and a datasource it.

try using --list --datsourcelist or --hostlist to get the info you need.

GOOD LUCK!!!!

Working great for me so far!

[o019676]

Had to edit the above global file.

same contents with slight change to the global file.

cant upload files with internal passwords...whoops

[ruben]

I haven't gotten around to play with your scripts yet. But I do want to thank you in advance for the effort.
The last few days I spend most my time on creating and auto-updating a 'large' sum of RRD files (8 Class C subnets, 1 RRD per IP). Since I didn't feel like creating 2000+ data sources and yet another 2000+ graphs, these scripts will most likely be of much help to me.

[o019676]

Just wondering if i could get some feedback from anyone else who has used it?

[ruben]

Just wondering if i could get some feedback from anyone else who has used it?

I haven't looked at it for quite some time now. But what I do recall is that your script were less suited for my case then I had hoped for. I don't recall what the issues were exactly, but I think it came down to the fact that the graphs I want to generate are "more unique" than your script can handle. I'll see if I can take another stab at it today and provide more info.

[fiksve]

Exactly what I needed! This is a great tool for us who dont use the cacti poller system, but rely on specialized mechanisms to generate the rrds -- Thanks!

[ralfk]

The add_datasource.sh script worked for me after some modifications (Cacti 0.8.7b). Thanks for that.

1) Set the fields according to T_NAME, T_ACTIVE and T_RRD_STEP to '' (empty) when inserting to database.
2) Removed the TEST_TEST check (I have the same data source type multiple times for a host)

My additional improvements:
3) Added a -N parameter for assigning a proper data source name ("name" and "name_cache", instead of using DNAME).
4) Added a check if the "data_source_path" (RRD file) is already in the database. If so, then exit.

[arturrenato]

Hi ralfk,

Could you please post your script (with your changes)?

Thanks
Artur

[ralfk]

See attached my version of the add datasource script with the changes described above and listed in the script.

[danielbabault]

trying to use add_datasource_ralfk I have mysql error

I use last CactiEZ , mysql version is mysql Ver 14.12 Distrib 5.0.68

can you help me ?

errors :

[root@apollon ]# ./add_datasource_ralfk.sh --list | grep AppsMon
70 AppsMon

[root@apollon ]# ./add_datasource_ralfk.sh --hostlist | grep BA2
6 BA2 P022053

[root@apollon ]# ./add_datasource_ralfk.sh -D 70 -H 6 -N AppsMon_BA2-Annuaire -rrd /var/www/html/rra/AppsMon_BA2-Annuaire.rrd
ERROR 1064 (42000) at line 13: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on , 0, NULL, 0, on, 1800, on, 1, NULL, 'PlayBackStatus', NULL, 0)' at line 2
data source created data-source-id: (53)
[root@apollon ]#

/var/tmp/cacti_add_datasource.tmp contains :


13 INSERT INTO data_template_rrd (id, hash, local_data_template_rrd_id, local_data_id, data_template_id, t_rrd_maximum, rrd_ma ximum, t_rrd_minimum, rrd_minimum, t_rrd_heartbeat, rrd_heartbeat, t_data_source_type_id, data_source_type_id, t_data_sourc e_name, data_source_name, t_data_input_field_id, data_input_field_id) VALUES
14 (202, '', 200, 54, 70, on , 0, NULL, 0, on, 1800, on, 1, NULL, 'PlayBackStatus', NULL, 0);
15 INSERT INTO data_template_rrd (id, hash, local_data_template_rrd_id, local_data_id, data_template_id, t_rrd_maximum, rrd_ma ximum, t_rrd_minimum, rrd_minimum, t_rrd_heartbeat, rrd_heartbeat, t_data_source_type_id, data_source_type_id, t_data_sourc e_name, data_source_name, t_data_input_field_id, data_input_field_id) VALUES
16 (203, '', 201, 54, 70, on , 100, on, 0, on, 1800, on, 1, NULL, 'PlayBackTime', NULL, 0);

[ralfk]

Hm, the same command works for me. Most likely something to do with CactiEZ and different tables ? I use plain Cacti 0.8.7b.

[ralfk]

You should look into the TMP file /var/tmp/cacti_add_datasource.tmp
and manually check the SQL statements. It seems to be line 13 of that TMP file, which causes the error.

[ralfk]

The problem is that the on values are not quoted. My values in these fields are always NULL. So, you need to quote all fields, that are on, within ''.

Quick Fix line 210 of the add script:

echo "($NEXTAID, '', $FIELD1, $NEXTAVAILIDDL, $FIELD5, '$FIELD6' , $FIELD7, '$FIELD8', $FIELD9, '$FIELD10', $FIELD11, '$FIELD12', $FIELD13, $FIELD14, '$FIELD15', $FIELD16, $FIELD17);" >> $TMP

But it's maybe not a good idea to quote fields with NULL values ($FIELD14 and $FIELD16). So, the script would need some more changes to check if the value is NULL or not and then add quotes if it is not NULL. Or just leave the values blank if they are NULL. Therefore, the quick fix might work for you but not for all scenarios.

[danielbabault]

coomming back, I've found my problem cames from my Data Template, where I selected "Use Per-Data Source Value "

Thanks