Created
January 23, 2015 11:58
-
-
Save xenji/49911fb50dcd6e17b18a to your computer and use it in GitHub Desktop.
Revisions
-
xenji created this gist
Jan 23, 2015 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,565 @@ #!/usr/bin/env python import os, sys, commands, random, time import pprint try: import MySQLdb except : sys.stderr.write('Cannot load MySQLdb lib.\n') sys.exit(1) COLLECTD_PATH = '/var/lib/collectd/rrd/' CACTI_HOST = 'localhost' CACTI_USER = 'cactiuser' CACTI_PWD = 'cactipass' CACTI_PORT = 3306 CACTI_DBNAME = 'cacti' TIMEOUT=5 RRDTOOL_PATH = "/usr/bin/rrdtool" database = None def main (): global database hosts = [] data_templates = {} host_data_templates = {} host_data_template_values = {} host_data_value_metric_rrd = {} paths = {} hosts = ls(COLLECTD_PATH) hosts.sort() for host in hosts: print ("Getting values for %s." % host) data_values_path = os.path.normpath(COLLECTD_PATH) + os.path.sep + host data_values = ls(data_values_path) for data_value in data_values: data_value_split = data_value.split('-') data = data_value_split[0] ###### BIG FILTER if data in ('irq','df'): continue ###### // BIG FILTER add_or_append(host_data_templates, host, data) if len(data_value_split)>1: value = '-'.join(data_value_split[1:]) add_or_append2(host_data_template_values, host, data, value) else: value = '' metrics_path = os.path.normpath(data_values_path) + os.path.sep + data_value metrics_rrd = ls(metrics_path) for i in range(len(metrics_rrd)-1,-1,-1): if not metrics_rrd[i].endswith('.rrd'): metrics_rrd.pop(i) for metric_rrd in metrics_rrd: filename = metrics_path+ os.path.sep +metric_rrd ds_names = get_ds_from_rrd(filename) metric = metric_rrd[:-4] if data_value == 'interface': metric_split = metric.split('-') value = metric_split[-1] metric = '-'.join(metric_split[:-1]) add_or_append2(host_data_template_values, host, data, value) paths[(host, data, value, metric)] = (filename, ds_names) add_or_append(data_templates, data, metric) data_templates[data].sort() if host_data_template_values.has_key(host): if host_data_template_values[host].has_key(data): host_data_template_values[host][data].sort() host_data_templates[host].sort() ######## DEBUG STUFF # print ("hosts : ") # pprint.pprint(hosts) # [host1, host2, ..] # print ("data_templates : ") # pprint.pprint(data_templates) # data -> [metric1, metric2, ...] # print ("host_data_templates : ") # pprint.pprint(host_data_templates) # host -> [data1, data2, ...] # print ("host_data_template_values : ") # pprint.pprint(host_data_template_values) # host -> data -> [value1, value2, ...] # print ("paths : ") # pprint.pprint(paths) print ("\n" + "#"*30 + "\n") database = connect() print ("Database stuff\n") for host in hosts: print ("Host : %s." % host) host_id = do_host(host) graph_tree_id = do_graph_tree() graph_tree_items_id = do_graph_tree_items(graph_tree_id, host_id) for data_template_name in data_templates.keys(): print ("\t" + data_template_name) if host in host_data_template_values.keys() and host_data_template_values[host].has_key(data_template_name): value_list = host_data_template_values[host][data_template_name] else: value_list = ['',] data_input_id = do_data_input(data_template_name) snmp_query_id = do_snmp_query(data_input_id, data_template_name) do_host_snmp_query(host_id, snmp_query_id) graph_template_id = do_graph_templates(data_template_name) for metric in data_templates[data_template_name]: if metric == "ps_stacksize": print "skip metric " + metric continue data_template_id = do_data_template(data_template_name, metric) data_input_field_id = do_data_input_field(data_input_id, metric) index=1 for value in value_list: if not (host, data_template_name, value, metric) in paths.keys(): index += 1 continue do_host_snmp_cache(host_id, snmp_query_id, value, index) data_local_id = do_data_local(host_id, data_template_id, snmp_query_id, index) local_graph_id = do_graph_local(graph_template_id, host_id, data_template_name, metric, snmp_query_id, index) graph_templates_graph_id = do_graph_templates_graph(local_graph_id, graph_template_id, data_template_name, host, metric, value) filename, ds_names = paths[(host, data_template_name, value, metric)] data_template_data_id = do_data_template_data(host_id, data_local_id, data_template_id, data_input_id, data_template_name, value, metric, filename) for ds_name in ds_names: graph_template_input_id = do_graph_template_input(graph_template_id, metric, ds_name) data_template_rrd_id = do_data_template_rrd(data_local_id, data_template_id, data_input_id, data_input_field_id , metric, ds_name) local_data_template_rrd_id = get_local_data_template_rrd(data_template_id, ds_name, data_input_field_id) if len(ds_names)>1: tmp_metric = "%s - %s" % (metric, ds_name) else: tmp_metric = metric graph_template_item_id = do_graph_templates_item(local_graph_id, graph_template_id, local_data_template_rrd_id, data_template_rrd_id, tmp_metric) local_graph_template_item_id = get_local_graph_templates_item(graph_template_id, local_data_template_rrd_id, metric) do_graph_template_input_defs(graph_template_input_id, local_graph_template_item_id) do_data_template_data_rra(data_template_data_id) index += 1 database.close() def get_ds_from_rrd(filename): cmd = "%s info %s" % (RRDTOOL_PATH, filename) status, output = commands.getstatusoutput(cmd) if status != 0: print ("Error with command '%s' (ret='%d', output='%s')." % (cmd, status, output)) sys.exit(2) else: lines = output.split('\n') ds_names = [] for line in lines: if not line.startswith("ds["): continue ds = line[3:].split('.')[0].strip(']') if ds not in ds_names: ds_names.append(ds) return ds_names def do_host(host): columns_values = { 'host_template_id':3, # totally random... 'description':host, 'hostname':host, 'disabled':'on', } table = 'host' return select_or_insert(table, columns_values) def do_data_input(data_template_name): table = 'data_input' columns_values = { 'name': "Collectd - %s" % data_template_name, 'type_id' : 1 } return select_or_insert(table, columns_values) def do_data_input_field(data_input_id, metric): table = 'data_input_fields' columns_values = { 'data_input_id':data_input_id, 'name':metric, 'data_name':metric, 'input_output':'out', 'update_rra':'on', 'sequence':0 } return select_or_insert(table, columns_values) def do_data_template(data_template_name, metric): table = 'data_template' columns_values = { 'name':"Collectd - %s - %s" % (data_template_name, metric), } return select_or_insert(table, columns_values) def do_data_local(host_id, data_template_id, snmp_query_id, value_index): table = 'data_local' columns_values = { 'host_id':host_id, 'data_template_id':data_template_id, } if snmp_query_id and value_index: columns_values['snmp_query_id']=1 columns_values['snmp_index']=value_index return select_or_insert(table, columns_values) def do_snmp_query(data_input_id, data_template_name): ## ?? table = 'snmp_query' columns_values = { 'data_input_id':data_input_id, 'xml_path':'<path_cacti>/resource/lolilol.xml', 'name':'Collectd - Get %s values' % data_template_name, } return select_or_insert(table, columns_values) def do_host_snmp_query(host_id, snmp_query_id): table = 'host_snmp_query' columns_values = { 'host_id':host_id, 'snmp_query_id':snmp_query_id, 'sort_field':'CollectdValue', 'title_format':'|query_CollectdValue|', 'reindex_method':1 } return select_or_insert(table, columns_values, no_id=True) def do_host_snmp_cache(host_id, snmp_query_id, value, value_index): table = 'host_snmp_cache' columns_values = { 'host_id':host_id, 'snmp_query_id':snmp_query_id, 'field_name':'CollectdValue', 'field_value':value, 'snmp_index':value_index, 'oid':'' } return select_or_insert(table, columns_values, no_id=True) def do_data_template_data(host_id, local_data_id, data_template_id, data_input_id, data_template_name, value, metric, rrd_path): table = 'data_template_data' name = '|host_description| - %s - %s ' % (data_template_name, metric) if value: name += ' - |query_CollectdValue|' columns_values = { 'local_data_template_data_id':0, 'local_data_id':0, 'data_template_id':data_template_id, 'data_input_id':data_input_id, 'name':name, 'active':'on', 'rrd_step':300 } local_data_template_data_id = select_or_insert(table, columns_values) columns_values['local_data_template_data_id']=local_data_template_data_id columns_values['local_data_id']=local_data_id host = runone("select hostname from host where id=%d" % host_id)[0] name= '%s - %s - %s ' % (host, data_template_name, metric) if value: name += ' - %s' % value columns_values['name_cache']= name columns_values['data_source_path']=rrd_path return select_or_insert(table, columns_values) def do_data_template_rrd(local_data_id, data_template_id, data_input_id, data_input_field_id , metric, ds_name): table = 'data_template_rrd' columns_values = { 'local_data_template_rrd_id':0, 'local_data_id':0, 'data_template_id':data_template_id, 'data_source_name':ds_name, 'data_input_field_id':data_input_field_id, } local_data_template_rrd_id = select_or_insert(table, columns_values) columns_values['local_data_template_rrd_id']=local_data_template_rrd_id columns_values['local_data_id']=local_data_id return select_or_insert(table, columns_values) def get_local_data_template_rrd(data_template_id, ds_name, data_input_field_id): sql = "select id from data_template_rrd where data_template_id=%s and data_source_name='%s' and data_input_field_id=%s" % (data_template_id, ds_name, data_input_field_id) raw_id = runone(sql) if raw_id and raw_id[0]: return long(raw_id[0]) else: return -1 def do_data_template_data_rra(data_template_data_id): table = 'data_template_data_rra' columns_values = { 'data_template_data_id':data_template_data_id, } for rra_id in runall("select id from rra"): columns_values['rra_id']=rra_id[0] select_or_insert(table, columns_values, no_id=True) def do_graph_tree(): table = 'graph_tree' columns_values = { 'sort_type':1, 'name':'Collectd' } return select_or_insert(table, columns_values) def do_graph_tree_items(graph_tree_id, host_id): table = 'graph_tree_items' ret = runone('select id from %s where graph_tree_id="%s" and host_id="%s";' % (table, graph_tree_id, host_id)) if ret: return ret[0] last_order_key = runone("select order_key from %s order by order_key desc limit 1;" % table) l = 90 if last_order_key: last_order_key = repr(int(last_order_key[0][:3])+1).zfill(3) else: last_order_key = '001' columns_values = { 'graph_tree_id':graph_tree_id, 'host_id':host_id, 'order_key':last_order_key+('0'*87), 'host_grouping_type':1, 'sort_children_type':1, } return select_or_insert(table, columns_values) def do_graph_templates(dataname): table = 'graph_templates' columns_values = { 'name':'Collectd - %s' % dataname } return select_or_insert(table, columns_values) def do_graph_template_input(graph_template_id, metric, ds_name): table = 'graph_template_input' columns_values = { 'graph_template_id':graph_template_id, 'name':'%s - %s' % (metric, ds_name), 'column_name':'task_item_id' } return select_or_insert(table, columns_values) def do_graph_local(graph_template_id, host_id, dataname, metric, snmp_query_id, snmp_index): table = 'graph_local' columns_values = { 'graph_template_id':graph_template_id, 'host_id':host_id, 'snmp_query_id':snmp_query_id, 'snmp_index':snmp_index, } return select_or_insert(table, columns_values) def do_graph_templates_graph(local_graph_id, graph_template_id, dataname, hostname, metric, snmp_index): table = 'graph_templates_graph' columns_values = { 'title':'|host_description| - Collectd - %s' % dataname, 'graph_template_id':graph_template_id, 'image_format_id':1, 'height':120, 'width':500, 'upper_limit':0, 'vertical_label':'vertical_label', 'auto_scale':'on', 'base_value':1000, 'auto_scale_rigid':'on', 'auto_padding':'on', 'auto_scale_opts':2, 'export':'on', 'auto_scale_log':'', 'unit_value':'', 'scale_log_units':'' } local_graph_template_graph_id = select_or_insert(table, columns_values) columns_values['local_graph_template_graph_id']=local_graph_template_graph_id columns_values['local_graph_id']=local_graph_id title = '%s - Collectd - %s' % (hostname, dataname) if snmp_index: title += ' - %s' % snmp_index columns_values['title_cache']=title return select_or_insert(table, columns_values) def do_graph_templates_item(local_graph_id, graph_template_id, local_data_template_rrd_id, data_template_rrd_id, metric): table = 'graph_templates_item' sql = 'select sequence from graph_templates_item where graph_template_id=%d and task_item_id=%d' % (graph_template_id, local_data_template_rrd_id) raw_sequence = runone(sql) if (len(raw_sequence)>0 and raw_sequence[0]): sequence = raw_sequence[0] else: sql = "select max(sequence) from graph_templates_item where graph_template_id=%d and local_graph_template_item_id=0 and local_graph_id=0;" % graph_template_id raw_sequence = runone(sql) if raw_sequence and raw_sequence[0]: sequence = long(raw_sequence[0])+1 else: sequence = 1 sql = "select color_id from graph_templates_item where graph_template_id=%d and sequence=%d and local_graph_template_item_id=0 and local_graph_id=0;" % (graph_template_id, sequence) raw_color_id = runone(sql) if raw_color_id and raw_color_id[0]: color_id = long(raw_color_id[0]) else: sql = 'select id from colors' colors = runall(sql) color_nb = random.choice(range(1,len(colors)-1)) color_id = colors[color_nb][0] columns_values = { 'graph_template_id':graph_template_id, 'task_item_id':local_data_template_rrd_id, 'alpha':'FF', 'graph_type_id':5, 'cdef_id':0, # 2 ? 'consolidation_function_id':1, 'text_format':metric, 'gprint_id':3 } local_graph_template_item_id = select_or_insert(table, columns_values) sql = "update graph_templates_item set sequence=%d, color_id=%d where id=%d" % (sequence , color_id, local_graph_template_item_id) runone(sql) columns_values['local_graph_template_item_id']=local_graph_template_item_id columns_values['local_graph_id'] = local_graph_id columns_values['task_item_id'] = data_template_rrd_id graph_template_item_id = select_or_insert(table, columns_values) sql = "update graph_templates_item set color_id=%d, sequence=%d where id=%s" % (color_id, sequence, graph_template_item_id) runone(sql) return graph_template_item_id # graph_type_id # 1 comment # 2 hrule # 3 vrule # 4 line1 # 5 line2 # 6 line3 # 7 area # 8 stack # 9 grpint # 10 legend def get_local_graph_templates_item(graph_template_id, data_template_rrd_id, metric): sql = 'select id from graph_templates_item where local_graph_template_item_id=0 and graph_template_id=%s and task_item_id=%s and text_format="%s"' % (graph_template_id, data_template_rrd_id, metric ) res = runone(sql) if res and res[0]: return int(res[0]) else: return 0 def do_graph_template_input_defs(graph_template_input_id, graph_template_item_id): table = 'graph_template_input_defs' columns_values = { 'graph_template_input_id':graph_template_input_id, 'graph_template_item_id':graph_template_item_id } return select_or_insert(table, columns_values, no_id=True) def add_or_append(tab,key,value): if tab.has_key(key): if not value in tab[key]: tab[key].append(value) else: tab[key] = [value,] def add_or_append2(tab, key1, key2, value): if tab.has_key(key1): add_or_append(tab[key1], key2, value) else: tab[key1] = {key2: [value,]} def select_or_insert(table, columns_values, no_id=False): global database if not database: print ("Error : no connection to the database.") sys.exit(3) # try a select first conditions = [] for key in columns_values: conditions.append("%s='%s'" % (key, columns_values[key])) if no_id: col = "*" else: col = "id" sql = "select %s from %s where %s" % (col, table, " and ".join(conditions)) res = runone(sql) if len(res)>0: return res[0] # looks like it doesn't exist -> insert columns_str = ', '.join(columns_values.keys()) values_tab = [] for value in columns_values.values(): values_tab.append("'%s'" % value) values_str = ', '.join(values_tab) sql = "insert into %s (%s) values (%s)" % (table, columns_str, values_str) runone(sql) if not no_id: return runone("select last_insert_id();")[0] else: return -1 def runone(sql): res = runall(sql) if len(res)>0: return res[0] else: return () def runall(sql): global database if not database: print ("Error : no connection to the database.") sys.exit(4) curs = database.cursor() try: curs.execute(sql) except Exception, error: print ("Error : %s" % error) print ("Request : %s " % sql) sys.exit(-1) res = curs.fetchall() curs.close() return res def connect(): database = MySQLdb.connect(user=CACTI_USER, passwd=CACTI_PWD, host=CACTI_HOST, db=CACTI_DBNAME, port=CACTI_PORT, connect_timeout=TIMEOUT) return database def ls(path) : list = os.listdir(os.path.normpath(path) + os.path.sep) for i in range(len(list)-1,-1,-1): if list[i].startswith('.'): list.pop(i) return list if __name__ == "__main__": main()