"""PSLA - Perforce Server Log Analyzer """ from flask import Flask, request, redirect, url_for, render_template, flash, Response from flask_wtf.csrf import CSRFProtect from werkzeug import secure_filename import logging import pandas as pd import sqlite3 import traceback from datetime import datetime import glob import textwrap from altair import Chart, X, Y, Axis # Modules from our psla app from config import Config from app import app from app.forms import UploadForm, AnalyzeLog, QueryLog, ChartLog from log2sql import Log2sql import app.queries as queries import os # Default chart dimensions WIDTH = 600 HEIGHT = 300 UPLOAD_FOLDER = os.getenv('PSLA_LOGS', '/logs') # Default is inside docker container ALLOWED_EXTENSIONS = set(['txt']) app.config.from_object(Config) app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER csrf = CSRFProtect(app) def allowed_file(filename): return True return '.' in filename and \ filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS class MyOptions(): "Options class with defaults for Log2sql" def __init__(self, logfile, sql=False, verbosity=logging.INFO, outlog=None): self.dbname = None self.logfile = logfile self.sql = sql self.no_sql = not sql self.verbosity = verbosity self.interval = 10 self.outlog = outlog self.output = None self.interval = None self.reset = True @app.route('/') def index(): """Front page for application""" return render_template('index.html') @app.route('/uploadLog', methods=['GET', 'POST']) def uploadLog(): "Upload log file" form = UploadForm() app.logger.debug("uploadLog: %s" % form.errors) app.logger.debug('------ {0}'.format(request.form)) if form.is_submitted(): app.logger.debug(form.errors) if form.validate(): app.logger.debug(form.errors) app.logger.debug(form.errors) if form.validate_on_submit(): db_folder = app.config['UPLOAD_FOLDER'] filename = secure_filename(form.uploadFile.data.filename) file_path = os.path.join(db_folder, filename) form.uploadFile.data.save(file_path) os.chdir(db_folder) optionsSQL = MyOptions([file_path], sql=True, outlog='log.out') log2sql = Log2sql(optionsSQL) log2sql.processLogs() return redirect(url_for('analyzeLog')) return render_template('uploadLog.html', title='Upload Log', form=form) @app.route('/progress') def progress(): def generate(): x = 0 while x < 100: # print x # x = x + 10 # time.sleep(0.2) yield "data:" + str(x) + "\n\n" return Response(generate(), mimetype= 'text/event-stream') @app.route('/analyzeLog', methods=['GET', 'POST']) def analyzeLog(): "Access previously uploaded log" db_folder = app.config['UPLOAD_FOLDER'] logFiles = glob.glob('%s/*.db' % db_folder) form = AnalyzeLog() form.logFile.choices = [(f, f) for f in logFiles] if not form.validate_on_submit(): return render_template('analyzeLog.html', tables=[], charts=[], dbName=None, form=form) dbname = os.path.join(form.logFile.data) if not os.path.exists(dbname): flash('Database does not exist', 'error') return render_template('error.html', title='Database error') app.dbname = dbname try: conn = sqlite3.connect(dbname) except Exception as e: app.logger.error(traceback.format_exc()) flash('Error: %s' % (str(e)), 'error') return render_template('error.html', title='Error in database reporting') tables = [] for q in queries.queries: if not 'sql' in q or not q['sql']: continue app.logger.debug("running table query: %s - %s" % (q['title'], q['sql'])) start = datetime.now() try: df = pd.read_sql_query(q['sql'], conn) table = df.to_html() except Exception as e: table = "<h3>ERROR: %s</h3>" % str(e) end = datetime.now() delta = end - start tables.append({'data': table, 'title': q['title'], 'explanation': q['explanation'], 'sql': q['sql'], 'time_taken': str(delta)}) return render_template('analyzeLog.html', tables=tables, dbName=dbname, form=form) @app.route('/queryLog', methods=['GET', 'POST']) def queryLog(): "Run queries on selected log" db_folder = app.config['UPLOAD_FOLDER'] logFiles = glob.glob('%s/*.db' % db_folder) form = QueryLog() form.logFile.choices = [(f, f) for f in logFiles] form.queryOptions.choices = [('--Please select SQL', '<select>')] for q in queries.queries: sql = textwrap.dedent(q['sql']).lstrip() form.queryOptions.choices.append((sql, q['title'])) try: dbname = app.dbname if os.path.exists(dbname): form.logFile.data = dbname except: pass if not form.validate_on_submit(): return render_template('queryLog.html', data={}, dbName=None, form=form) dbname = os.path.join(form.logFile.data) if not os.path.exists(dbname): flash('Database does not exist', 'error') return render_template('error.html', title='Database error') try: conn = sqlite3.connect(dbname) except Exception as e: app.logger.error(traceback.format_exc()) flash('Error: %s' % (str(e)), 'error') return render_template('error.html', title='Error in database reporting') sqlQuery = form.sqlQuery.data app.logger.debug("running interactive query: %s" % (sqlQuery)) start = datetime.now() try: df = pd.read_sql_query(sqlQuery, conn) table = df.to_html() except Exception as e: table = "<h3>ERROR: %s</h3>" % str(e) end = datetime.now() delta = end - start data = ({'data': table, 'sql': sqlQuery, 'time_taken': str(delta)}) return render_template('queryLog.html', data=data, dbName=dbname, form=form) @app.route('/schema', methods=['GET']) def schema(): return render_template('schema.html') @app.route('/chartLog', methods=['GET', 'POST']) def chartLog(): "Display chart for selected log" db_folder = app.config['UPLOAD_FOLDER'] logFiles = glob.glob('%s/*.db' % db_folder) form = ChartLog() form.logFile.choices = [(f, f) for f in logFiles] form.chartId.choices = [(q['id'], q['id']) for q in queries.graphs] try: dbname = app.dbname if os.path.exists(dbname): form.logFile.data = dbname except: pass if not form.validate_on_submit(): return render_template('chartLog.html', chart={}, dbName=None, form=form) dbname = os.path.join(form.logFile.data) if not os.path.exists(dbname): flash('Database does not exist', 'error') return render_template('error.html', title='Database error') try: conn = sqlite3.connect(dbname) except Exception as e: app.logger.error(traceback.format_exc()) flash('Error: %s' % (str(e)), 'error') return render_template('error.html', title='Error in database reporting') chartId = form.chartId.data charts = [q for q in queries.graphs if q['id'] == chartId] if not charts: flash("Error: logic error couldn't find chartId", 'error') return render_template('error.html', title='Error in in configuration of chart reports') q = charts[0] app.logger.debug("running chart query: %s - %s" % (q['title'], q['sql'])) start = datetime.now() try: df = pd.read_sql_query(q['sql'], conn) except Exception as e: flash('Error: %s' % (str(e)), 'error') return render_template('error.html', title='Error in database reporting') end = datetime.now() delta = end - start if q['graph_type'] == 'line': chart = Chart(data=df, height=HEIGHT, width=WIDTH).mark_line().encode( X(q['x']['field'], axis=Axis(title=q['x']['title'], labelOverlap='greedy')), Y(q['y']['field'], axis=Axis(title=q['y']['title'])) ) else: chart = Chart(data=df, height=HEIGHT, width=WIDTH).mark_bar().encode( X(q['x']['field'], axis=Axis(title=q['x']['title'], labelOverlap='greedy')), Y(q['y']['field'], axis=Axis(title=q['y']['title'])) ) data = {'id': "chart", 'data': chart.to_json(), 'title': q['title'], 'explanation': q['explanation'], 'sql': q['sql'], 'time_taken': str(delta)} return render_template('chartLog.html', chart=data, dbName=dbname, form=form)
# | Change | User | Description | Committed | |
---|---|---|---|---|---|
#18 | 25220 | Robert Cowham |
Moved project files to new location: //guest/perforce_software/log-analyzer/psla/... Required by Swarm project structure for workshop. |
||
#17 | 24989 | Robert Cowham | Expand schema help for tableuse | ||
#16 | 24979 | Robert Cowham |
Tidy up charting to add db selection and titles. Clarify README |
||
#15 | 24321 | Robert Cowham | Allow canned queries to be included | ||
#14 | 24291 | Robert Cowham | Ensure imports will work for local files | ||
#13 | 24257 | Robert Cowham | Output SQL as part of results | ||
#12 | 23934 | Robert Cowham | Clean database and auto-create on log upload | ||
#11 | 23933 | Robert Cowham | Create new seperate charting page | ||
#10 | 23924 | Robert Cowham | Make bar charts and fix axis labels | ||
#9 | 23890 | Robert Cowham | Basic progressbar | ||
#8 | 23845 | Robert Cowham | Basic query form working | ||
#7 | 23833 | Robert Cowham | Document how to run with flask and make /logs configurable | ||
#6 | 23785 | Robert Cowham |
Tweak interface Remove unused module |
||
#5 | 23774 | Robert Cowham | Ensure chart labels don't overlap | ||
#4 | 23773 | Robert Cowham | Only display rest of page when analyzing | ||
#3 | 23772 | Robert Cowham | Allow user to select from all db files | ||
#2 | 23771 | Robert Cowham | Error handling for queries - refactor and move to seperate file | ||
#1 | 23765 | Robert Cowham | Moved things down one level to psla dir to make it easier to see what belongs | ||
//guest/perforce_software/utils/log_analyzer/app/routes.py | |||||
#10 | 23762 | Robert Cowham | New graph working | ||
#9 | 23761 | Robert Cowham | Create a parameterised loop for graphs | ||
#8 | 23760 | Robert Cowham | Simple basics with Altair chart | ||
#7 | 23723 | Robert Cowham | First basic chart with plotly | ||
#6 | 23718 | Robert Cowham |
New statements. Time query execution |
||
#5 | 23714 | Robert Cowham | New queries | ||
#4 | 23712 | Robert Cowham | Proper formatting of table data | ||
#3 | 23711 | Robert Cowham | More or less working with multiple SQL statements | ||
#2 | 23705 | Robert Cowham | Basics working in simplified form | ||
#1 | 23704 | Robert Cowham | Save before simplifying |