def get_clock():
data = sql.exec_selsql("select clock from zabbix.clock limit 1;", 0)
if len(data) > 0:
clock = data[0]
else:
clock = 0
sql.exec_updsql("insert into zabbix.clock value(\'0\');")
if clock == 0:
data = sql.exec_selsql("select min(clock) from zabbix.statistics;", 0)
if len(data)>0 and data[0] != None:
clock = data[0]
else:
clock = f.time2epoch(dt.now())
return clock
def arr2table(_arr, _table):
sqlstr = ""
for row in _arr:
i = 0
sqlstr = sqlstr + "REPLACE INTO " + _table + " VALUES("
for val in row:
if i == 0:
sqlstr = sqlstr + "\'" + str(val) + "\'"
else:
sqlstr = sqlstr + ", \'" + str(val) + "\'"
i = i + 1
sqlstr = sqlstr + ");\n"
sqlstr = sqlstr + "COMMIT;\n"
sql.exec_updsql(sqlstr)
def csv2table(_file, _table):
with open(_file, 'r') as f:
reader = csv.reader(f)
sqlstr = ""
for row in reader:
i = 0
sqlstr = sqlstr + "REPLACE INTO " + _table + " VALUES("
for val in row:
if i == 0:
sqlstr = sqlstr + "\'" + str(val) + "\'"
else:
sqlstr = sqlstr + ", \'" + str(val) + "\'"
i = i + 1
sqlstr = sqlstr + ");\n"
sqlstr = sqlstr + "COMMIT;\n"
sql.exec_updsql(sqlstr)
def detect(_logmode="n"):
stat_data = get_statistics()
hist_data = get_history4detect()
start_clock = f.time2epoch(dt.now())
hist_i = 0
for stat_row in stat_data:
stat_hostid = stat_row[0]
stat_itemid = stat_row[1]
avg = stat_row[2]
var = stat_row[3]
while True:
if hist_i >= len(hist_data)-1:
break
hist_row = hist_data[hist_i]
hist_hostid = hist_row[0]
hist_itemid = hist_row[1]
clock = hist_row[2]
value = hist_row[3]
if hist_hostid == stat_hostid and hist_itemid == stat_itemid:
detect_proc(stat_hostid, stat_itemid, value, avg, var, clock)
if hist_hostid > stat_hostid or hist_itemid > stat_itemid or hist_i>=len(hist_data):
break
hist_i = hist_i + 1
sql.exec_updsql("update zabbix.clock set clock=\'%s\'" % start_clock)
sql.exec_updsql("truncate table zabbix.gaussian;")
if _logmode=="y":
strsql = ""
for s in l_gaussian_sql:
strsql = strsql + s + "\n"
if strsql != "":
sql.exec_updsql(strsql)
def hist2stat():
data = get_history4stat()
strsql = ""
old_hostid = ""
old_itemid = ""
i = 0
cnt = 0
valuelist = []
last_clock = 0
sql.exec_updsql("truncate table monitoring.stats_last;")
for row in data:
hostid = row[0]
itemid = row[1]
clock = row[2]
value = row[3]
if (itemid != old_itemid and old_itemid != "") \
or (hostid != old_hostid and old_hostid != "") \
or i >= len(data)-1:
avg = f.get_avg(valuelist)
sqr_avg = f.get_sqr_avg(valuelist)
var = f.get_var(valuelist)
strsql = strsql + "insert into monitoring.stats_last(\
hostid, itemid, clock, sample_num, average, variance, sqr_average) \
values(\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');\n" % \
(str(old_hostid), str(old_itemid), str(clock), str(len(valuelist)), str(avg), str(var), str(sqr_avg))
if cnt >= insert_cnt or i >= len(data)-1:
sql.exec_updsql(strsql)
cnt = 0
strsql = ""
valuelist = []
last_clock = clock
valuelist.append(value)
i = i + 1
cnt = cnt + 1
old_hostid = hostid
old_itemid = itemid
def create_table(_database, _table_name):
sqlstr = sql.get_sql(_database + "/create_table_" + _table_name)
sql.exec_updsql(sqlstr)
def imp_csv2table(_file, _table):
sqlstr = "LOAD DATA LOCAL INFILE \'" + _file + "\' REPLACE INTO TABLE " + _table + " FIELDS TERMINATED BY \',\';"
sql.exec_updsql(sqlstr)
def drop_view(_database, _view_name):
sql.exec_updsql("DROP VIEW IF EXISTS " + _dadtabase + "." + _view_name + ";")
def drop_table(_database, _table_name):
sql.exec_updsql("DROP TABLE IF EXISTS " + _dadtabase + "." + _table_name + ";")
def create_view(_database, _view_name):
sqlstr = sql.get_sql(_database + "/create_view_" + _view_name)
sql.exec_updsql(sqlstr)
