def update_silence_time(cls, alert_rules_id, silence_time):
sql = (
"update {table} set silence_time=:silence_time where alert_rules_id = :alert_rules_id and deleted = 0"
).format(table=cls._table_ar)
params = dict(alert_rules_id=alert_rules_id, silence_time=silence_time)
db.execute(sql, params=params)
db.commit()
def update_status(cls, alert_rules_id, status):
sql = (
"update {table} set status=:status where alert_rules_id = :alert_rules_id and deleted = 0"
).format(table=cls._table_ar)
params = dict(alert_rules_id=alert_rules_id, status=status)
db.execute(sql, params=params)
db.commit()
def delete_alert_rule(cls, alert_rules_id):
sql = (
"update {table} set deleted = 1 where alert_rules_id = :alert_rules_id and deleted = 0"
).format(table=cls._table_ar)
params = dict(alert_rules_id=alert_rules_id)
db.execute(sql, params=params)
db.commit()
def delete_hostpool_by_host_id(cls, host_id):
sql = ("delete from {table} where host_id=:host_id").format(
table=cls._table_hp)
params = dict(host_id=host_id)
db.execute(sql, params=params)
db.commit()
return host_id
def add(cls, alert_rules_id, host_id, port, service, monitor_items,
alert_time, current_value, last_time, state, contact_groups,
status):
sql = (
"insert into {table} "
"(alert_rules_id, host_id, port, service, monitor_items, alert_time, current_value, last_time, state, contact_groups, status) values "
"(:alert_rules_id, :host_id, :port, :service, :monitor_items, :alert_time, "
":current_value, :last_time, :state, :contact_groups, :status)"
).format(table=cls._table_ah)
params = dict(host_id=host_id,
port=port,
alert_rules_id=alert_rules_id,
service=service,
monitor_items=monitor_items,
alert_time=alert_time,
current_value=current_value,
last_time=last_time,
state=state,
contact_groups=contact_groups,
status=status)
r = db.execute(sql, params=params)
if r.lastrowid:
db.commit()
return r.lastrowid
db.rollback()
def add(cls, alert_rules_id, host_id, port, service, temp_name,
monitor_items, statistical_period, statistical_approach,
compute_mode, threshold_value, contact_groups, notify_type,
status):
sql = (
"insert into {table} "
"(alert_rules_id, host_id, port, service, temp_name, monitor_items, statistical_period, statistical_approach, compute_mode, threshold_value, contact_groups, notify_type, status) values "
"(:alert_rules_id, :host_id, :port, :service, :monitor_items, :statistical_period, "
":statistical_approach, :compute_mode, :threshold_value, :contact_groups, :notify_type, :status)"
).format(table=cls._table_ar)
params = dict(host_id=host_id,
port=port,
alert_rules_id=alert_rules_id,
service=service,
temp_name=temp_name,
monitor_items=monitor_items,
statistical_period=statistical_period,
statistical_approach=statistical_approach,
compute_mode=compute_mode,
threshold_value=threshold_value,
contact_groups=contact_groups,
notify_type=notify_type,
status=status)
r = db.execute(sql, params=params)
if r.lastrowid:
db.commit()
return r.lastrowid
db.rollback()
def add_hostpool(cls, host_id, name, description, device_key, privateip,
os_type, state, attribute, region, remark, dns,
project_id):
sql = (
"insert into {table} "
"(host_id, name, description, device_key, privateip, os_type, state, attribute, region, dns, project_id, remark) values "
"(:host_id, :name, :description, :device_key, :privateip, "
":os_type, :state, :attribute, :region, :dns, :project_id, :remark)"
).format(table=cls._table_hp)
params = dict(host_id=host_id,
name=name,
description=description,
device_key=device_key,
privateip=privateip,
os_type=os_type,
state=state,
attribute=attribute,
region=region,
dns=dns,
project_id=project_id,
remark=remark)
r = db.execute(sql, params=params)
if r.lastrowid:
db.commit()
return r.lastrowid
db.rollback()
def get_hostpool_by_host_id(cls, host_id):
sql = ("select id from {table} where host_id=:host_id").format(
table=cls._table_hp)
params = dict(host_id=host_id)
rs = db.execute(sql, params=params).fetchone()
db.commit()
return rs[0] if rs else ''
def update_hostpool_by_host_id(cls, host_id, name, description, device_key, state, region, remark, dns, project_id):
sql = ("update {table} set name=:name, description=:description, "
"device_key=:device_key, state=:state, region=:region, remark:=remark, "
"dns=:dns, project_id=:project_id where host_id=:host_id").format(table=cls._table_hp)
params = dict(
host_id=host_id,
name=name,
description=description,
device_key=device_key,
state=state,
region=region,
dns=dns,
project_id=project_id,
remark=remark)
db.execute(sql, params=params)
db.commit()
return host_id
def get_monitor_item_by_category(cls, category):
sql = (
"select id, name, nick_name, category, query, aggregation, unit "
"from {table} where category=:category").format(table=cls._table)
params = dict(category=category)
rs = db.execute(sql, params=params).fetchall()
db.commit()
return [cls(*line) for line in rs] if rs else []
def get_alert_rules_list(cls):
sql = (
" select id, alert_rules_id, host_id, port, service, monitor_items, statistical_period,"
" statistical_approach, compute_mode, threshold_value, silence_time, contact_groups, notify_type, status, deleted, deleted_time, create_time, update_time"
" from {table} where deleted = 0").format(table=cls._table_ar)
rs = db.execute(sql).fetchall()
db.commit()
return [cls(*line) for line in rs] if rs else []
def get_alerts_list(cls):
sql = (
" select id, alert_rules_id, monitor_items, host_id, port, service, alert_time,"
" current_value, last_time, state, contact_groups, status"
" from {table}").format(table=cls._table_ah)
rs = db.execute(sql).fetchall()
db.commit()
return [cls(*line) for line in rs] if rs else []
def get_query_metric_by_name(cls, category, name):
sql = ("select query_metric from {table} "
"where category=:category and name=:name").format(
table=cls._table)
params = dict(category=category, name=name)
rs = db.execute(sql, params=params).fetchone()
db.commit()
return rs[0] if rs else ''
def getlist(cls):
sql = (
"select id, host_key, user_id, name, description, privateip, "
"cpu_process, cpu_physical, memory_total, disk_usage, monitor_status, "
"os_type, state, project_id, remark, create_time, update_time "
"from {table_hp}").format(table_hp=cls._table_hp)
rs = db.execute(sql).fetchall()
db.commit()
return [ cls(*line) for line in rs ] if rs else []
def get_alert_rules_by_name(cls, host_id, port, monitor_items):
sql = (
" select id, alert_rules_id, host_id, port, service, monitor_items, statistical_period,"
" statistical_approach, compute_mode, threshold_value, silence_time, contact_groups, notify_type, status, deleted, deleted_time, create_time, update_time"
" from {table} where host_id = :host_id and port = :port and monitor_items = :monitor_items and deleted = 0"
).format(table=cls._table_ar)
params = dict(monitor_items=monitor_items, host_id=host_id, port=port)
line = db.execute(sql, params=params).fetchone()
db.commit()
return line if line else ''
def get_alert_rules(cls, alert_rules_id):
sql = (
" select host_id, service, monitor_items, statistical_period,"
" statistical_approach, statistical_approach, compute_mode, threshold_value, status"
" from {table} where alert_rules_id := alert_rules_id ").format(
table=cls._table_ar)
params = dict(alert_rules_id=alert_rules_id)
line = db.execute(sql, params=params).fetchone()
db.commit()
return line if line else ''
def get_alerts(cls, alert_rules_id):
sql = (
" select id, alert_rules_id, monitor_items, host_id, port, service, alert_time,"
" current_value, last_time, state, contact_groups, status"
" from {table} where alert_rules_id := alert_rules_id ").format(
table=cls._table_ah)
params = dict(alert_rules_id=alert_rules_id)
line = db.execute(sql, params=params).fetchone()
db.commit()
return line if line else ''
def getlist(cls):
sql = (
"select hp.id, hp.host_id, hp.name, hp.description, hp.device_key, "
"hp.os_type, hp.status, hp.monitor_status, hp.state, hp.attribute, hp.region, "
"hp.privateip, hpe.privateip_extend, hpe.publicip, "
"hpe.publicip_extend, hpe.cpu, hpe.cpu_process, hpe.memory, "
"hpe.disk_space, hpe.disk_type, hp.dns, hp.project_id, "
"p.project_name, p.project_description, hp.remark, hp.create_time, hp.update_time "
"from {table_hp} hp left join {table_hpe} hpe using(host_id) "
"left join {table_p} p on p.pid=hp.project_id").format(
table_hp=cls._table_hp,
table_hpe=cls._table_hpe,
table_p=cls._table_p)
rs = db.execute(sql).fetchall()
db.commit()
return [cls(*line) for line in rs] if rs else []
def update(cls, alert_rules_id, statistical_period, compute_mode,
threshold_value, contact_groups, notify_type):
sql = (
"update {table} set statistical_period=:statistical_period, compute_mode=:compute_mode, threshold_value=:threshold_value, contact_groups=:contact_groups, notify_type=:notify_type where alert_rules_id = :alert_rules_id and deleted = 0"
).format(table=cls._table_ar)
params = dict(statistical_period=statistical_period,
compute_mode=compute_mode,
threshold_value=threshold_value,
contact_groups=contact_groups,
notify_type=notify_type,
alert_rules_id=alert_rules_id)
r = db.execute(sql, params=params)
if r.lastrowid:
db.commit()
return r.lastrowid
db.rollback()
def update(cls, alert_rules_id, statistical_period, statistical_approach,
compute_mode, threshold_value, status):
sql = (
"update {table} set "
"statistical_period=:statistical_period, statistical_approach=:statistical_approach, compute_mode=:compute_mode, threshold_value=:threshold_value, status=:status"
"where alert_rules_id = :alert_rules_id").format(
table=cls._table_ar)
params = dict(statistical_period=statistical_period,
statistical_approach=statistical_approach,
compute_mode=compute_mode,
threshold_value=threshold_value,
alert_rules_id=alert_rules_id,
status=status)
r = db.execute(sql, params=params)
if r.lastrowid:
db.commit()
return r.lastrowid
db.rollback()
def get_privateip_by_host_key(cls, host_key):
sql = ("select privateip from {table} where host_key=:host_key").format(table=cls._table_hp)
params = dict(host_key=host_key)
rs = db.execute(sql, params=params).fetchone()
db.commit()
return rs[0] if rs else ''
