コード例 #1
0
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
コード例 #2
0
ファイル: app.py プロジェクト: wcbzero/daily_storage_reports
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
コード例 #3
0
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)
コード例 #4
0
def get_db(db_name):
    if not hasattr(flask.g, 'pg_db'):
        flask.g.db = SqliteDb(db_name)
    return flask.g.db
コード例 #5
0
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]))
コード例 #6
0
ファイル: tesetDB.py プロジェクト: tomdev2008/shlianjian
# 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()
コード例 #7
0
ファイル: app.py プロジェクト: wcbzero/daily_storage_reports
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)
コード例 #8
0
ファイル: app.py プロジェクト: wcbzero/daily_storage_reports
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]))