def aggregate_data(cluster):
db = SqliteDb(cluster["sqlite_db_path"], cluster["csv_data_path"])
db.create_tables()
schs = db.get_schemas()
delta_day = datetime.timedelta(days=1)
current_day = datetime.datetime.now() - datetime.timedelta(days=7)
while current_day <= datetime.datetime.now():
aggregate_day(db, current_day.strftime("%Y-%m-%d"))
current_day += delta_day
def aggregate_data(cluster):
db = SqliteDb(cluster["sqlite_db_path"], cluster["csv_data_path"])
db.create_tables()
schs = db.get_schemas()
delta_day = datetime.timedelta(days=1)
current_day = datetime.datetime.now() - datetime.timedelta(days=7)
while current_day <= datetime.datetime.now():
aggregate_day(db, current_day.strftime("%Y-%m-%d"))
current_day += delta_day
def api_alerts():
configs = get_config()
alerts_sql = """
SELECT *
FROM alert_rule
WHERE rule_status = 1
"""
all_alerts = []
for config in configs["clusters"]:
db = SqliteDb(config["sqlite_db_path"], config["csv_data_path"])
db.get_schemas()
if flask.request.method == "POST":
if flask.request.form['action'] == "remove":
for the_id in flask.request.form.getlist('id[]'):
id_parts = the_id.split("|")
if id_parts[0] == config["name"]:
sql = "update alert_rule set rule_status=-1 WHERE alert_id in (%s)" % (
id_parts[1], )
db.query(sql)
elif flask.request.form['action'] == "create":
fd = flask.request.form
ins_sql = """INSERT INTO alert_rule
(created_timestamp, alert_type, path, expr, val, recipients, max_send_count, send_count, rule_status)
values
('%s', '%s', '%s', '%s', %s, '%s', %s, %s, 1)
"""
sql = ins_sql % (
datetime.datetime.now().strftime("%Y-%m-%d"),
fd["data[alert_type]"], fd["data[path]"], fd["data[expr]"],
bytes_to_num(fd["data[val]"]),
re.sub("[ \r\n\t]+", "", fd["data[recipients]"]),
fd["data[max_send_count]"], fd["data[send_count]"])
if fd["data[cluster]"] == config["name"]:
db.query(sql)
elif flask.request.form['action'] == "edit":
fd = flask.request.form
id_parts = fd['id'].split("|")
upd_sql = """update alert_rule
set alert_type = '%s'
, path = '%s'
, expr = '%s'
, val = %s
, recipients = '%s'
, max_send_count = %s
, send_count = %s
WHERE alert_id in (%s)""" % (
fd["data[alert_type]"], fd["data[path]"], fd["data[expr]"],
bytes_to_num(fd["data[val]"]),
re.sub("[ \r\n\t]+", "",
fd["data[recipients]"]), fd["data[max_send_count]"],
fd["data[send_count]"], id_parts[1])
if fd["data[cluster]"] == config["name"]:
db.query(upd_sql)
alerts = db.get_results(alerts_sql)
for alert in alerts:
alert["cluster"] = config["name"]
alert["val"] = alert["val"] if alert[
"alert_type"] == "iops" else nice_bytes(alert["val"])
alert["alert_id"] = config["name"] + "|" + str(alert["alert_id"])
all_alerts.append(alert)
the_data = {"data": all_alerts}
return flask.jsonify(the_data)
def get_db(db_name):
if not hasattr(flask.g, 'pg_db'):
flask.g.db = SqliteDb(db_name)
return flask.g.db
def check_alerts():
configs = get_config()
alerts_sql = """
SELECT *
FROM alert_rule
WHERE rule_status = 1
AND send_count < max_send_count
AND COALESCE(last_send_timestamp, datetime('%s', '-7 DAY')) < datetime('%s', '-23 HOUR')
"""
sqls = {}
sqls["iops"] = """
SELECT *
FROM (select path, level, round(sum(file_read+file_write+namespace_read+namespace_write)/60) val
FROM iops_by_path
WHERE timestamp >= datetime('%(now)s', '-1 hour')
group by 1, 2) t
WHERE val %(expr)s %(val)s
AND path = '%(path)s'
ORDER BY level
"""
sqls["used capacity change"] = """
SELECT *
FROM
(
SELECT path
, MAX(COALESCE(CASE WHEN timestamp = '%(today)s' THEN total_used_capacity ELSE 0 END, 0))
- MAX(COALESCE(CASE WHEN timestamp = date('%(today)s', '-1 day') THEN total_used_capacity ELSE 0 END, 0)) val
FROM report_daily_path_metrics
WHERE timestamp in (date('%(today)s', '-1 day'), '%(today)s')
GROUP BY 1
HAVING SUM(CASE WHEN timestamp = '%(today)s' AND COALESCE(total_used_capacity, 0) > 0 THEN 1 ELSE 0 END) > 0
) t
WHERE val %(expr)s %(val)s
AND path = '%(path)s'
"""
sqls["total used capacity"] = """
SELECT *
FROM
(
SELECT path
, MAX(COALESCE(CASE WHEN timestamp = '%(today)s' THEN total_used_capacity ELSE 0 END, 0)) val
, MAX(COALESCE(CASE WHEN timestamp = date('%(today)s', '-1 day') THEN total_used_capacity ELSE 0 END, 0)) val_prior
FROM report_daily_path_metrics
WHERE timestamp in (date('%(today)s', '-1 day'), '%(today)s')
GROUP BY 1
HAVING SUM(CASE WHEN timestamp = '%(today)s' AND COALESCE(total_used_capacity, 0) > 0 THEN 1 ELSE 0 END) > 0
) t
WHERE val %(expr)s %(val)s
AND val_prior %(expr_inv)s %(val)s
AND path = '%(path)s'
"""
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
recipients = {}
for config in configs["clusters"]:
print "Checking alerts for: " + config["name"]
db = SqliteDb(config["sqlite_db_path"], config["csv_data_path"])
db.create_tables()
db.get_schemas()
db.import_table_for_date("iops_by_path",
datetime.datetime.now().strftime("%Y-%m-%d"))
active_alerts_sql = alerts_sql % (now, now)
alerts = db.get_results(active_alerts_sql)
for alert in alerts:
alert["now"] = now
alert["today"] = datetime.datetime.now().strftime("%Y-%m-%d")
alert["expr_inv"] = "<" if alert["expr"] == ">=" else ">"
sql = sqls[alert["alert_type"]] % alert
print re.sub("[\r\n]+", " ", sql)
filtered_rows = db.get_results(sql)
if len(filtered_rows) > 0:
upd_sql = """UPDATE alert_rule
SET send_count = send_count + 1
, last_send_timestamp = '%s'
WHERE alert_id = %s
""" % (now, alert["alert_id"])
db.query(upd_sql)
for email in alert["recipients"].split(","):
if email not in recipients:
recipients[email] = [{
"subject":
alert["alert_type"] + " on " + config["name"],
"body":
build_alert_email(db, config, alert, filtered_rows)
}]
else:
recipients[email].append({
"subject":
alert["alert_type"] + " on " + config["name"],
"body":
build_alert_email(db, config, alert, filtered_rows)
})
for email in recipients:
print "Send email: " + email + " - " + ', '.join(
d["subject"] for d in recipients[email])
mail_it(
configs,
str(email).strip(), '<br/>\r\n'.join(d["body"]
for d in recipients[email]) +
"<br /><br />To manage your alerts, click here: " +
configs["url"] + "/alerts",
"Qumulo Quota Alert: " + ', '.join(d["subject"]
for d in recipients[email]))
# coding:utf-8
from sqlitedb import SqliteDb
sqlite = SqliteDb('/opt/lianjia_scrawler/lianjia-scrawler/lianjiash.db')
sql = "select * from community where bizcircle='静安寺'"
c_lst = sqlite.query(sql)
print(c_lst[0])
sqlite.close()
def api_alerts():
configs = get_config()
alerts_sql = """
SELECT *
FROM alert_rule
WHERE rule_status = 1
"""
all_alerts = []
for config in configs["clusters"]:
db = SqliteDb(config["sqlite_db_path"], config["csv_data_path"])
db.get_schemas()
if flask.request.method == "POST":
if flask.request.form['action'] == "remove":
for the_id in flask.request.form.getlist('id[]'):
id_parts = the_id.split("|")
if id_parts[0] == config["name"]:
sql = "update alert_rule set rule_status=-1 WHERE alert_id in (%s)" % (id_parts[1], )
db.query(sql)
elif flask.request.form['action'] == "create":
fd = flask.request.form
ins_sql = """INSERT INTO alert_rule
(created_timestamp, alert_type, path, expr, val, recipients, max_send_count, send_count, rule_status)
values
('%s', '%s', '%s', '%s', %s, '%s', %s, %s, 1)
"""
sql = ins_sql % (
datetime.datetime.now().strftime("%Y-%m-%d")
, fd["data[alert_type]"]
, fd["data[path]"]
, fd["data[expr]"]
, bytes_to_num(fd["data[val]"])
, re.sub("[ \r\n\t]+", "", fd["data[recipients]"])
, fd["data[max_send_count]"]
, fd["data[send_count]"]
)
if fd["data[cluster]"] == config["name"]:
db.query(sql)
elif flask.request.form['action'] == "edit":
fd = flask.request.form
id_parts = fd['id'].split("|")
upd_sql = """update alert_rule
set alert_type = '%s'
, path = '%s'
, expr = '%s'
, val = %s
, recipients = '%s'
, max_send_count = %s
, send_count = %s
WHERE alert_id in (%s)""" % (
fd["data[alert_type]"]
, fd["data[path]"]
, fd["data[expr]"]
, bytes_to_num(fd["data[val]"])
, re.sub("[ \r\n\t]+", "", fd["data[recipients]"])
, fd["data[max_send_count]"]
, fd["data[send_count]"]
, id_parts[1])
if fd["data[cluster]"] == config["name"]:
db.query(upd_sql)
alerts = db.get_results(alerts_sql)
for alert in alerts:
alert["cluster"] = config["name"]
alert["val"] = alert["val"] if alert["alert_type"] == "iops" else nice_bytes(alert["val"])
alert["alert_id"] = config["name"] + "|" + str(alert["alert_id"])
all_alerts.append(alert)
the_data = {"data":all_alerts}
return flask.jsonify(the_data)
def check_alerts():
configs = get_config()
alerts_sql = """
SELECT *
FROM alert_rule
WHERE rule_status = 1
AND send_count < max_send_count
AND COALESCE(last_send_timestamp, datetime('%s', '-7 DAY')) < datetime('%s', '-23 HOUR')
"""
sqls = {}
sqls["iops"] = """
SELECT *
FROM (select path, level, round(sum(file_read+file_write+namespace_read+namespace_write)/60) val
FROM iops_by_path
WHERE timestamp >= datetime('%(now)s', '-1 hour')
group by 1, 2) t
WHERE val %(expr)s %(val)s
AND path = '%(path)s'
ORDER BY level
"""
sqls["used capacity change"] = """
SELECT *
FROM
(
SELECT path
, MAX(COALESCE(CASE WHEN timestamp = '%(today)s' THEN total_used_capacity ELSE 0 END, 0))
- MAX(COALESCE(CASE WHEN timestamp = date('%(today)s', '-1 day') THEN total_used_capacity ELSE 0 END, 0)) val
FROM report_daily_path_metrics
WHERE timestamp in (date('%(today)s', '-1 day'), '%(today)s')
GROUP BY 1
HAVING SUM(CASE WHEN timestamp = '%(today)s' AND COALESCE(total_used_capacity, 0) > 0 THEN 1 ELSE 0 END) > 0
) t
WHERE val %(expr)s %(val)s
AND path = '%(path)s'
"""
sqls["total used capacity"] = """
SELECT *
FROM
(
SELECT path
, MAX(COALESCE(CASE WHEN timestamp = '%(today)s' THEN total_used_capacity ELSE 0 END, 0)) val
, MAX(COALESCE(CASE WHEN timestamp = date('%(today)s', '-1 day') THEN total_used_capacity ELSE 0 END, 0)) val_prior
FROM report_daily_path_metrics
WHERE timestamp in (date('%(today)s', '-1 day'), '%(today)s')
GROUP BY 1
HAVING SUM(CASE WHEN timestamp = '%(today)s' AND COALESCE(total_used_capacity, 0) > 0 THEN 1 ELSE 0 END) > 0
) t
WHERE val %(expr)s %(val)s
AND val_prior %(expr_inv)s %(val)s
AND path = '%(path)s'
"""
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
recipients = {}
for config in configs["clusters"]:
print "Checking alerts for: " + config["name"]
db = SqliteDb(config["sqlite_db_path"], config["csv_data_path"])
db.create_tables()
db.get_schemas()
db.import_table_for_date("iops_by_path", datetime.datetime.now().strftime("%Y-%m-%d"))
active_alerts_sql = alerts_sql % (now, now)
alerts = db.get_results( active_alerts_sql )
for alert in alerts:
alert["now"] = now
alert["today"] = datetime.datetime.now().strftime("%Y-%m-%d")
alert["expr_inv"] = "<" if alert["expr"] == ">=" else ">"
sql = sqls[alert["alert_type"]] % alert
print re.sub("[\r\n]+", " ", sql)
filtered_rows = db.get_results(sql)
if len(filtered_rows) > 0:
upd_sql = """UPDATE alert_rule
SET send_count = send_count + 1
, last_send_timestamp = '%s'
WHERE alert_id = %s
""" % (now, alert["alert_id"])
db.query(upd_sql)
for email in alert["recipients"].split(","):
if email not in recipients:
recipients[email] = [{"subject":alert["alert_type"] + " on " + config["name"], "body":build_alert_email(db, config, alert, filtered_rows)}]
else:
recipients[email].append({"subject":alert["alert_type"] + " on " + config["name"], "body":build_alert_email(db, config, alert, filtered_rows)})
for email in recipients:
print "Send email: " + email + " - " + ', '.join(d["subject"] for d in recipients[email])
mail_it(configs, str(email).strip(), '<br/>\r\n'.join(d["body"] for d in recipients[email]) + "<br /><br />To manage your alerts, click here: " + configs["url"] + "/alerts", "Qumulo Quota Alert: " + ', '.join(d["subject"] for d in recipients[email]))