def get_data(id, start, end): def _get_data(model): data = [] for row in session.query(model.data).filter_by(ds_id=id).filter(model.time >= start).filter(model.time < end): data.append(row.data) return data start_day = start.strftime("%Y%m%d") data = _get_data(ChartdataTmpl.gen(start_day)) end_day = end.strftime("%Y%m%d") if end_day != start_day: data.extend(_get_data(ChartdataTmpl.gen(end_day))) return data
def stats_extra(self): rows = { # prod chart channel (1, 1, 3): [0, 4254], (1, 2, 3): [0, 4255], (1, 1, 4): [0, 4256], (1, 2, 4): [0, 4257], (2, 2, 3): [0, 4258], (2, 2, 4): [0, 4259], (2, 2, 5): [0, 4440], (2, 2, 6): [0, 4441] } try: for i in self.data: k = (i[1], i[2], i[4]) if k in rows: rows[k][0] += i[0] for k, v in rows.iteritems(): row = ChartdataTmpl.gen(self.next_dt.strftime('%Y%m%d'))() row.ds_id = v[1] row.time = self.next_dt row.data = v[0] self.sakuya_db.add(row) self.sakuya_db.commit() logging.info('%d extra rows inserted.' % len(rows)) except Exception, e: self.sakuya_db.rollback() logging.exception('Fail to stats extra.')
def stats_extra(self): rows = { # prod chart channel (1, 1, 3): [0, 4254], (1, 2, 3): [0, 4255], (1, 1, 4): [0, 4256], (1, 2, 4): [0, 4257], (2, 2, 3): [0, 4258], (2, 2, 4): [0, 4259], (2, 2, 5): [0, 4440], (2, 2, 6): [0, 4441] } try: for i in self.data: k = (i[1], i[2], i[4]) if k in rows: rows[k][0] += i[0] for k, v in rows.iteritems(): row = ChartdataTmpl.gen(self.next_dt.strftime('%Y%m%d'))() row.ds_id = v[1] row.time = self.next_dt row.data = v[0] self.sakuya_db.add(row) self.sakuya_db.commit() logging.info('%d extra rows inserted.' % len(rows)) except Exception, e: self.sakuya_db.rollback() logging.exception('Fail to stats extra.')
def run(self): util.setup_logging('aggregate_daily', False, self.args.verbose) logging.info('Aggregating chart data of %s.' % self.args.date.strftime('%Y-%m-%d')) self.session = sessionmaker(bind=engine_sakuya_db)() self.Chartdata = ChartdataTmpl.gen(self.args.date.strftime('%Y%m%d')) last_id = 0 while True: charts = self.session.\ query(Charts).\ filter(Charts.id > last_id).\ order_by(Charts.id).\ limit(PER_FETCH).\ all() self.session.expunge_all() for chart in charts: try: self.process_chart(chart) except Exception, e: logging.exception('Fail to process chart %d' % chart.id) self.session.rollback() if len(charts) < PER_FETCH: break last_id = charts[-1].id
def get_data(id, start, end): def _get_data(model): data = [] for row in session.\ query(model.data).\ filter_by(ds_id=id).\ filter(model.time >= start).\ filter(model.time < end): data.append(row.data) return data start_day = start.strftime('%Y%m%d') data = _get_data(ChartdataTmpl.gen(start_day)) end_day = end.strftime('%Y%m%d') if end_day != start_day: data.extend(_get_data(ChartdataTmpl.gen(end_day))) return data
def check(self): Chartdata = ChartdataTmpl.gen(time.strftime('%Y%m%d')) for id in (839, 981, 3503, 4426): row = self.session.\ query(Chartdata).\ filter_by(ds_id=id).\ order_by(Chartdata.time.desc()).\ first() if row is None: logging.info('id %d row is none' % id) else: logging.info('id %d time is %s' % (id, row.time.strftime('%Y-%m-%d %H:%M:%S'))) self.session.close()
def check(self): Chartdata = ChartdataTmpl.gen(time.strftime('%Y%m%d')) for id in (839, 981, 3503, 4426): row = self.session.\ query(Chartdata).\ filter_by(ds_id=id).\ order_by(Chartdata.time.desc()).\ first() if row is None: logging.info('id %d row is none' % id) else: logging.info('id %d time is %s' % (id, row.time.strftime('%Y-%m-%d %H:%M:%S'))) self.session.close()
def main(): yesterday = datetime.datetime.now() - datetime.timedelta(days=1) Chartdata = ChartdataTmpl.gen(yesterday.strftime('%Y%m%d')) start_time = yesterday.replace(hour=10, minute=0, second=0, microsecond=0) end_time = yesterday.replace(hour=10, minute=59, second=59, microsecond=999) session = sessionmaker(bind=engine_sakuya_db)() total = {'access_log': 0, 'soj': 0} useless = {'access_log': [], 'soj': []} for chart in session.query(Charts).order_by(Charts.id): ext = chart.get_ext() if 'rule' not in ext: continue datasource = ext['rule']['datasource'] total[datasource] += 1 if chart.cate_id == 200: continue peak_sum = session.query(func.sum(Chartdata.data)).\ filter_by(ds_id=chart.id).\ filter(Chartdata.time >= start_time, Chartdata.time <= end_time).\ scalar() if peak_sum is None: peak_sum = 0 if peak_sum < 50: useless[datasource].append((chart.id, chart.name, peak_sum)) print 'Total - access_log: %d, soj: %d' % (total['access_log'], total['soj']) for datasource, charts in useless.iteritems(): print 'Useless Charts - %s:' % datasource for chart in charts: print '%7d, %s, %d' % (chart[0], chart[1].encode('utf-8'), chart[2])
def stats(self): if not self.data: return try: rows = haopan.fill_data(self.data) logging.info('stats time: %s' % self.next_dt.strftime('%Y-%m-%d %H:%M')) d_charts = haopan.get_charts(True) stats = [0, 0] for k, v in rows.iteritems(): id = d_charts.get(k) if id is None: logging.info('key %s not found.' % str(k)) stats[1] += 1 continue row = ChartdataTmpl.gen(self.next_dt.strftime('%Y%m%d'))() row.ds_id = id row.time = self.next_dt row.data = v self.sakuya_db.add(row) stats[0] += 1 logging.info('key: %s, id: %d, data: %d' % (str(k), id, v)) self.sakuya_db.commit() logging.info('%d rows inserted, %d failed.' % tuple(stats)) except Exception: self.sakuya_db.rollback() logging.exception('Fail to stats') self.stats_extra()
def stats(self): if not self.data: return try: rows = haopan.fill_data(self.data) logging.info('stats time: %s' % self.next_dt.strftime('%Y-%m-%d %H:%M')) d_charts = haopan.get_charts(True) stats = [0, 0] for k, v in rows.iteritems(): id = d_charts.get(k) if id is None: logging.info('key %s not found.' % str(k)) stats[1] += 1 continue row = ChartdataTmpl.gen(self.next_dt.strftime('%Y%m%d'))() row.ds_id = id row.time = self.next_dt row.data = v self.sakuya_db.add(row) stats[0] += 1 logging.info('key: %s, id: %d, data: %d' % (str(k), id, v)) self.sakuya_db.commit() logging.info('%d rows inserted, %d failed.' % tuple(stats)) except Exception: self.sakuya_db.rollback() logging.exception('Fail to stats') self.stats_extra()
def haopan_list(sakuya_db, id, id2=None): def get_data(id, model, data, k, start, end, delta=None): for row in sakuya_db.\ query(model).\ filter_by(ds_id=id).\ filter(model.time >= start).\ filter(model.time <= end): if delta is None: tmp_time = row.time else: tmp_time = row.time + delta dtstr = tmp_time.strftime('%Y%m%d%H%M') if dtstr in data: data[dtstr][k] = row.data # parse start and end date try: dt_start = datetime.datetime.strptime(request.params['start'], '%Y-%m-%d %H:%M') dt_end = datetime.datetime.strptime(request.params['end'], '%Y-%m-%d %H:%M') if dt_start >= dt_end: raise ValueError except Exception: now = datetime.datetime.now() dt_end = datetime.datetime(now.year, now.month, now.day, now.hour, now.minute) dt_start = dt_end - datetime.timedelta(hours=1) dt_end += datetime.timedelta(minutes=5) data = OrderedDict() delta = datetime.timedelta(days=7) dt_start_prev = dt_start - delta dt_end_prev = dt_end - delta dt_delta = dt_end - dt_start if dt_delta <= datetime.timedelta(days=1): curdt = dt_start while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') init_num = 2 if id2 is None else 4 if request.params.get('compzero') == '1': data[dt_key] = [0] * init_num else: data[dt_key] = [None] * init_num curdt += datetime.timedelta(minutes=1) # current data get_data(id, ChartdataTmpl.gen(dt_start.strftime('%Y%m%d')), data, 0, dt_start, dt_end) get_data(id, ChartdataTmpl.gen(dt_end.strftime('%Y%m%d')), data, 0, dt_start, dt_end) if id2 is not None: get_data(id2, ChartdataTmpl.gen(dt_start.strftime('%Y%m%d')), data, 2, dt_start, dt_end) get_data(id2, ChartdataTmpl.gen(dt_end.strftime('%Y%m%d')), data, 2, dt_start, dt_end) get_data(id, ChartdataTmpl.gen(dt_start_prev.strftime('%Y%m%d')), data, 1, dt_start_prev, dt_end_prev, delta) get_data(id, ChartdataTmpl.gen(dt_end_prev.strftime('%Y%m%d')), data, 1, dt_start_prev, dt_end_prev, delta) if id2 is not None: get_data(id2, ChartdataTmpl.gen(dt_start_prev.strftime('%Y%m%d')), data, 3, dt_start_prev, dt_end_prev, delta) get_data(id2, ChartdataTmpl.gen(dt_end_prev.strftime('%Y%m%d')), data, 3, dt_start_prev, dt_end_prev, delta) else: if dt_delta <= datetime.timedelta(days=5): minutes = 5 model = ChartdataWeekly elif dt_delta <= datetime.timedelta(days=15): minutes = 15 model = ChartdataMonthly elif dt_delta <= datetime.timedelta(days=30): minutes = 30 model = ChartdataQuarterly else: abort(400) curdt = dt_start while curdt.minute % minutes != 0: curdt += datetime.timedelta(minutes=1) while curdt <= dt_end: data[curdt.strftime('%Y%m%d%H%M')] = [0, 0] if id2 is None else [ 0, 0, 0, 0 ] curdt += datetime.timedelta(minutes=minutes) get_data(id, model, data, 0, dt_start, dt_end) get_data(id, model, data, 1, dt_start_prev, dt_end_prev, delta) if id2 is not None: get_data(id2, model, data, 2, dt_start, dt_end) get_data(id2, model, data, 3, dt_start_prev, dt_end_prev, delta) # cleanup newest data for k in reversed(data.keys()): if data[k][0] == 0: data[k][0] = None else: break if id2 is not None: for k in reversed(data.keys()): if data[k][2] == 0: data[k][2] = None else: break # gen data ys = [[y[0] for y in data.values()], [y[1] for y in data.values()]] if id2 is not None: ys.extend([[y[2] for y in data.values()], [y[3] for y in data.values()]]) # size try: size = (int(request.params['width']), int(request.params['height'])) if size <= (0, 0) or size > (1200, 500): raise ValueError except Exception: size = (300, 180) # ylim ylim_min, ylim_max = None, None if 'ylim_min' in request.params: try: ylim_min = int(request.params['ylim_min']) except Exception: pass if 'ylim_max' in request.params: try: ylim_max = int(request.params['ylim_max']) except Exception: pass # auto date formatter autodt = bool(request.params.get('autodt')) image = libgraph.draw_list( [datetime.datetime.strptime(x, '%Y%m%d%H%M') for x in data.keys()], ys, xlim=(dt_start, dt_end), size=size, ylim=(ylim_min, ylim_max), autodt=autodt) response.content_type = 'image/png' return image
def get_chart_data(sakuya_db, dt_start, dt_end, charts, compzero=False, daily=False): ''' charts: - [id, delta] return: - OrderedDict('%Y-%m-%d %H:%M'=[series1, series2, ...]) ''' # fill data into datetime bucket def get_data(id, model, data, index, start, end, delta=None): for row in sakuya_db.\ query(model).\ filter_by(ds_id=id).\ filter(model.time >= start).\ filter(model.time <= end): if delta is None: tmp_time = row.time else: tmp_time = row.time - delta dt_key = tmp_time.strftime('%Y%m%d%H%M') if dt_key in data: data[dt_key][index] = row.data # datetime bucket data = OrderedDict() # based on different time delta if daily: dt_delta = datetime.timedelta(days=31) else: dt_delta = dt_end - dt_start if dt_delta <= datetime.timedelta(days=1): # fill datatime bucket curdt = dt_start while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') if compzero: data[dt_key] = [0] * len(charts) else: data[dt_key] = [None] * len(charts) curdt += datetime.timedelta(minutes=1) # fill data for index, chart in enumerate(charts): if chart[1] is None: tmp_dt_start = dt_start tmp_dt_end = dt_end else: tmp_dt_start = dt_start + chart[1] tmp_dt_end = dt_end + chart[1] get_data(chart[0], ChartdataTmpl.gen(tmp_dt_start.strftime('%Y%m%d')), data, index, tmp_dt_start, tmp_dt_end, chart[1]) get_data(chart[0], ChartdataTmpl.gen(tmp_dt_end.strftime('%Y%m%d')), data, index, tmp_dt_start, tmp_dt_end, chart[1]) else: if dt_delta <= datetime.timedelta(days=5): minutes = 5 model = ChartdataWeekly elif dt_delta <= datetime.timedelta(days=15): minutes = 15 model = ChartdataMonthly elif dt_delta <= datetime.timedelta(days=30): minutes = 30 model = ChartdataQuarterly else: minutes = 1440 model = ChartdataDaily curdt = dt_start if minutes < 60: adjust_min = curdt.minute % minutes if adjust_min > 0: curdt -= datetime.timedelta(minutes=adjust_min) else: curdt = curdt.replace(hour=0, minute=0, second=0, microsecond=0) while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') if compzero: data[dt_key] = [0] * len(charts) else: data[dt_key] = [None] * len(charts) curdt += datetime.timedelta(minutes=minutes) for index, chart in enumerate(charts): if chart[1] is None: tmp_dt_start = dt_start tmp_dt_end = dt_end else: tmp_dt_start = dt_start + chart[1] tmp_dt_end = dt_end + chart[1] get_data(chart[0], model, data, index, tmp_dt_start, tmp_dt_end, chart[1]) # cleanup newest data for index in xrange(len(charts)): for dt_key in reversed(data.keys()): if data[dt_key][index] == 0: data[dt_key][index] = None else: break return data
def get_chart_data(sakuya_db, dt_start, dt_end, charts, compzero=False, daily=False): ''' charts: - [id, delta] return: - OrderedDict('%Y-%m-%d %H:%M'=[series1, series2, ...]) ''' # fill data into datetime bucket def get_data(id, model, data, index, start, end, delta=None): for row in sakuya_db.\ query(model).\ filter_by(ds_id=id).\ filter(model.time >= start).\ filter(model.time <= end): if delta is None: tmp_time = row.time else: tmp_time = row.time - delta dt_key = tmp_time.strftime('%Y%m%d%H%M') if dt_key in data: data[dt_key][index] = row.data # datetime bucket data = OrderedDict() # based on different time delta if daily: dt_delta = datetime.timedelta(days=31) else: dt_delta = dt_end - dt_start if dt_delta <= datetime.timedelta(days=1): # fill datatime bucket curdt = dt_start while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') if compzero: data[dt_key] = [0] * len(charts) else: data[dt_key] = [None] * len(charts) curdt += datetime.timedelta(minutes=1) # fill data for index, chart in enumerate(charts): if chart[1] is None: tmp_dt_start = dt_start tmp_dt_end = dt_end else: tmp_dt_start = dt_start + chart[1] tmp_dt_end = dt_end + chart[1] get_data(chart[0], ChartdataTmpl.gen(tmp_dt_start.strftime('%Y%m%d')), data, index, tmp_dt_start, tmp_dt_end, chart[1]) get_data(chart[0], ChartdataTmpl.gen(tmp_dt_end.strftime('%Y%m%d')), data, index, tmp_dt_start, tmp_dt_end, chart[1]) else: if dt_delta <= datetime.timedelta(days=5): minutes = 5 model = ChartdataWeekly elif dt_delta <= datetime.timedelta(days=15): minutes = 15 model = ChartdataMonthly elif dt_delta <= datetime.timedelta(days=30): minutes = 30 model = ChartdataQuarterly else: minutes = 1440 model = ChartdataDaily curdt = dt_start if minutes < 60: adjust_min = curdt.minute % minutes if adjust_min > 0: curdt -= datetime.timedelta(minutes=adjust_min) else: curdt = curdt.replace(hour=0, minute=0, second=0, microsecond=0) while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') if compzero: data[dt_key] = [0] * len(charts) else: data[dt_key] = [None] * len(charts) curdt += datetime.timedelta(minutes=minutes) for index, chart in enumerate(charts): if chart[1] is None: tmp_dt_start = dt_start tmp_dt_end = dt_end else: tmp_dt_start = dt_start + chart[1] tmp_dt_end = dt_end + chart[1] get_data(chart[0], model, data, index, tmp_dt_start, tmp_dt_end, chart[1]) # cleanup newest data for index in xrange(len(charts)): for dt_key in reversed(data.keys()): if data[dt_key][index] == 0: data[dt_key][index] = None else: break return data
detail = request.forms.get('detail', None) if data is None and detail is None: raise ValueError if data is not None: data = int(data) if detail is not None and len(detail) == 0: detail = None except Exception, e: return util.output('error', msg='Invalid params.') try: if data is not None: row = ChartdataTmpl.gen(dt.strftime('%Y%m%d'))() row.ds_id = tid row.time = dt row.data = data sakuya_db.add(row) if detail is not None: row = LogstuffTmpl.gen(dt.strftime('%Y%m%d'))() row.ds_id = tid row.time = dt row.detail = detail sakuya_db.add(row) sakuya_db.commit() except Exception, e:
def haopan_list(sakuya_db, id, id2=None): def get_data(id, model, data, k, start, end, delta=None): for row in sakuya_db.\ query(model).\ filter_by(ds_id=id).\ filter(model.time >= start).\ filter(model.time <= end): if delta is None: tmp_time = row.time else: tmp_time = row.time + delta dtstr = tmp_time.strftime('%Y%m%d%H%M') if dtstr in data: data[dtstr][k] = row.data # parse start and end date try: dt_start = datetime.datetime.strptime(request.params['start'], '%Y-%m-%d %H:%M') dt_end = datetime.datetime.strptime(request.params['end'], '%Y-%m-%d %H:%M') if dt_start >= dt_end: raise ValueError except Exception: now = datetime.datetime.now() dt_end = datetime.datetime(now.year, now.month, now.day, now.hour, now.minute) dt_start = dt_end - datetime.timedelta(hours=1) dt_end += datetime.timedelta(minutes=5) data = OrderedDict() delta = datetime.timedelta(days=7) dt_start_prev = dt_start - delta dt_end_prev = dt_end - delta dt_delta = dt_end - dt_start if dt_delta <= datetime.timedelta(days=1): curdt = dt_start while curdt <= dt_end: dt_key = curdt.strftime('%Y%m%d%H%M') init_num = 2 if id2 is None else 4 if request.params.get('compzero') == '1': data[dt_key] = [0] * init_num else: data[dt_key] = [None] * init_num curdt += datetime.timedelta(minutes=1) # current data get_data(id, ChartdataTmpl.gen(dt_start.strftime('%Y%m%d')), data, 0, dt_start, dt_end) get_data(id, ChartdataTmpl.gen(dt_end.strftime('%Y%m%d')), data, 0, dt_start, dt_end) if id2 is not None: get_data(id2, ChartdataTmpl.gen(dt_start.strftime('%Y%m%d')), data, 2, dt_start, dt_end) get_data(id2, ChartdataTmpl.gen(dt_end.strftime('%Y%m%d')), data, 2, dt_start, dt_end) get_data(id, ChartdataTmpl.gen(dt_start_prev.strftime('%Y%m%d')), data, 1, dt_start_prev, dt_end_prev, delta) get_data(id, ChartdataTmpl.gen(dt_end_prev.strftime('%Y%m%d')), data, 1, dt_start_prev, dt_end_prev, delta) if id2 is not None: get_data(id2, ChartdataTmpl.gen(dt_start_prev.strftime('%Y%m%d')), data, 3, dt_start_prev, dt_end_prev, delta) get_data(id2, ChartdataTmpl.gen(dt_end_prev.strftime('%Y%m%d')), data, 3, dt_start_prev, dt_end_prev, delta) else: if dt_delta <= datetime.timedelta(days=5): minutes = 5 model = ChartdataWeekly elif dt_delta <= datetime.timedelta(days=15): minutes = 15 model = ChartdataMonthly elif dt_delta <= datetime.timedelta(days=30): minutes = 30 model = ChartdataQuarterly else: abort(400) curdt = dt_start while curdt.minute % minutes != 0: curdt += datetime.timedelta(minutes=1) while curdt <= dt_end: data[curdt.strftime('%Y%m%d%H%M')] = [0, 0] if id2 is None else [0, 0, 0, 0] curdt += datetime.timedelta(minutes=minutes) get_data(id, model, data, 0, dt_start, dt_end) get_data(id, model, data, 1, dt_start_prev, dt_end_prev, delta) if id2 is not None: get_data(id2, model, data, 2, dt_start, dt_end) get_data(id2, model, data, 3, dt_start_prev, dt_end_prev, delta) # cleanup newest data for k in reversed(data.keys()): if data[k][0] == 0: data[k][0] = None else: break if id2 is not None: for k in reversed(data.keys()): if data[k][2] == 0: data[k][2] = None else: break # gen data ys = [[y[0] for y in data.values()], [y[1] for y in data.values()]] if id2 is not None: ys.extend([[y[2] for y in data.values()], [y[3] for y in data.values()]]) # size try: size = (int(request.params['width']), int(request.params['height'])) if size <= (0, 0) or size > (1200, 500): raise ValueError except Exception: size = (300, 180) # ylim ylim_min, ylim_max = None, None if 'ylim_min' in request.params: try: ylim_min = int(request.params['ylim_min']) except Exception: pass if 'ylim_max' in request.params: try: ylim_max = int(request.params['ylim_max']) except Exception: pass # auto date formatter autodt = bool(request.params.get('autodt')) image = libgraph.draw_list([datetime.datetime.strptime(x, '%Y%m%d%H%M') for x in data.keys()], ys, xlim=(dt_start, dt_end), size=size, ylim=(ylim_min, ylim_max), autodt=autodt) response.content_type = 'image/png' return image
detail = request.forms.get('detail', None) if data is None and detail is None: raise ValueError if data is not None: data = int(data) if detail is not None and len(detail) == 0: detail = None except Exception, e: return util.output('error', msg='Invalid params.') try: if data is not None: row = ChartdataTmpl.gen(dt.strftime('%Y%m%d'))() row.ds_id = tid row.time = dt row.data = data sakuya_db.add(row) if detail is not None: row = LogstuffTmpl.gen(dt.strftime('%Y%m%d'))() row.ds_id = tid row.time = dt row.detail = detail sakuya_db.add(row) sakuya_db.commit() except Exception, e: