def get_dag_duration_info() -> List[DagDurationInfo]: '''get duration of currently running DagRuns :return dag_info ''' driver = Session.bind.driver # pylint: disable=no-member durations = { 'pysqlite': func.julianday(func.current_timestamp() - func.julianday(DagRun.start_date)) * 86400.0, 'mysqldb': func.timestampdiff(text('second'), DagRun.start_date, func.now()), 'pyodbc': func.sum(func.datediff(text('second'), DagRun.start_date, func.now())), 'default': func.now() - DagRun.start_date } duration = durations.get(driver, durations['default']) sql_res = Session.query( # pylint: disable=no-member DagRun.dag_id, func.max(duration).label('duration')).group_by( DagRun.dag_id).filter(DagRun.state == State.RUNNING).all() res = [] for i in sql_res: if driver == 'mysqldb' or driver == 'pysqlite': dag_duration = i.duration else: dag_duration = i.duration.seconds res.append(DagDurationInfo(dag_id=i.dag_id, duration=dag_duration)) return res
def get_events_duration_statistics_from_db( db: Session, userid: int, start: datetime.datetime, end: datetime.datetime, ) -> EventsDurationStatistics: """get data of shortest, longest and average event duration from the db Args: db: db session. userid: requested user id number for statistics. start: start of date range. end: end of date range. Returns: NamedTuple of: shortest_event, longest_event, average_event """ event_duration = func.julianday(Event.end) - func.julianday(Event.start) user_to_event = (UserEvent, UserEvent.event_id == Event.id) by_user_id = UserEvent.user_id == userid events_duration_statistics = (db.query( (func.min(event_duration) * NIN_IN_DAY), (func.max(event_duration) * NIN_IN_DAY), (func.avg(event_duration) * NIN_IN_DAY), ).join(user_to_event).filter(by_user_id).filter( get_date_filter_between_dates(start, end)).all()) if events_duration_statistics[0][0]: return EventsDurationStatistics( shortest_event=round(events_duration_statistics[0][0]), longest_event=round(events_duration_statistics[0][1]), average_event=round(events_duration_statistics[0][2]), ) return EventsDurationStatistics(shortest_event=0, longest_event=0, average_event=0)
def get_dag_duration_info(): '''get duration of currently running DagRuns :return dag_info ''' driver = Session.bind.driver durations = { 'pysqlite': func.sum( (func.julianday(func.current_timestamp()) - func.julianday(DagRun.start_date)) * 86400.0 ), 'mysqldb': func.sum(func.timestampdiff(text('second'), DagRun.start_date, func.now())), 'default': func.sum(func.now() - DagRun.start_date) } duration = durations.get(driver, durations['default']) with session_scope(Session) as session: return session.query( DagRun.dag_id, DagRun.run_id, duration.label('duration') ).group_by( DagRun.dag_id, DagRun.run_id ).filter( DagRun.state == State.RUNNING ).all()
def spider_dashboard(project_id): Project.query.get_or_404(project_id) session['project_id'] = project_id last_runtime_query = db.session.query( SpiderInstance.spider_name, func.Max(JobExecution.date_created).label('last_runtime'), ).outerjoin(JobInstance, JobInstance.spider_name == SpiderInstance.spider_name)\ .outerjoin(JobExecution).filter(SpiderInstance.project_id == project_id)\ .group_by(SpiderInstance.id) last_runtime = dict((spider_name, last_runtime) for spider_name, last_runtime in last_runtime_query) avg_runtime_query = db.session.query( SpiderInstance.spider_name, func.Avg(func.julianday(JobExecution.end_time) - func.julianday(JobExecution.start_time)).label('avg_runtime'), ).outerjoin(JobInstance, JobInstance.spider_name == SpiderInstance.spider_name)\ .outerjoin(JobExecution).filter(SpiderInstance.project_id == project_id)\ .filter(JobExecution.end_time != None, JobExecution.start_time != None)\ .group_by(SpiderInstance.id) avg_runtime = dict((spider_name, avg_runtime) for spider_name, avg_runtime in avg_runtime_query) spiders = [] for spider in SpiderInstance.query.filter( SpiderInstance.project_id == project_id).all(): spider.last_runtime = last_runtime.get(spider.spider_name) if avg_runtime.get(spider.spider_name) is not None: spider.avg_runtime = str( datetime.timedelta(days=avg_runtime.get(spider.spider_name))) spiders.append(spider) return render_template("spider_dashboard.html", spiders=spiders)
def do_job(self): """ SELECT *, coalesce(end_time,now)-start_time FROM task t, subject s WHERE t.subject_id=s.id AND start_time > GetDate() - @days ORDER BY start_time :return: """ session = DBSession() now = datetime.now() time_worked = (func.julianday(func.coalesce(Task.end_time,now)) - func.julianday(Task.start_time)) * 86400 query = session.query(Task.start_time, func.coalesce(Task.end_time, now), time_worked, Subject.title, Task.title) \ .filter(Subject.id==Task.subject_id) \ .filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \ .order_by(Task.start_time) print '\n' table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title']) table.align["Title"] = "l" total_time = 0 day_total = 0 last_date = None for row in query: if last_date == None: last_date = row[0].date() if row[0].date() != last_date: table.add_row([ '', '', timedelta(seconds=round(day_total)), '', '' ]) last_date = row[0].date() day_total = 0 day_total += row[2] total_time += row[2] table.add_row([ row[0], row[1], timedelta(seconds=round(row[2])), row[3], row[4], ]) if day_total > 0: table.add_row([ '', '', timedelta(seconds=round(day_total)), '', '' ]) print table print 'Total Work time: %s' % timedelta(seconds=total_time) print
def do_job(self): """ SELECT *, coalesce(end_time,now)-start_time FROM task t, subject s WHERE t.subject_id=s.id AND start_time > GetDate() - @days ORDER BY start_time :return: """ session = DBSession() now = datetime.now() time_worked = (func.julianday(func.coalesce(Task.end_time, now)) - func.julianday(Task.start_time)) * 86400 query = session.query(Task.start_time, func.coalesce(Task.end_time, now), time_worked, Subject.title, Task.title) \ .filter(Subject.id == Task.subject_id) \ .filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \ .order_by(Task.start_time) print() table = PrettyTable(['Start', 'End', 'Time', 'Subject', 'Title']) table.align["Title"] = "l" total_time = 0 day_total = 0 last_date = None for row in query: if last_date == None: last_date = row[0].date() if row[0].date() != last_date: table.add_row( ['', '', timedelta(seconds=round(day_total)), '', '']) last_date = row[0].date() day_total = 0 day_total += row[2] total_time += row[2] table.add_row([ row[0], row[1], timedelta(seconds=round(row[2])), row[3], row[4], ]) if day_total > 0: table.add_row( ['', '', timedelta(seconds=round(day_total)), '', '']) print(table) print('Total Work time: %s\n' % timedelta(seconds=total_time))
def productname(servername, productname, days=3): users = db.session.query(models.User.name, models.History.time_in, func.sum(func.julianday(func.ifnull(models.History.calculated_timein, datetime.datetime.now())) - func.julianday( models.History.time_out)).label('time_sum')). \ filter(models.User.id == models.History.user_id). \ filter(models.History.product_id == models.Product.id). \ filter(models.Product.common_name == productname). \ distinct(models.User.name).group_by(models.User.name).all() days = datetime.datetime.utcnow() - datetime.timedelta(days=days) chart_data = db.session.query(func.count(models.History.user_id).label('users'), models.Product.license_total, extract('month', models.History.time_out).label('m'), extract('day', models.History.time_out).label('d'), extract('year', models.History.time_out).label('y')). \ filter(models.Product.id == models.History.product_id). \ filter(models.Server.id == models.Updates.server_id). \ filter(models.Updates.id == models.History.update_id). \ filter(models.Server.name == servername). \ filter(models.History.time_out > days). \ filter(models.Product.common_name == productname). \ distinct(models.History.user_id). \ group_by(models.Product.common_name, models.Server.name, 'm', 'd', 'y'). \ order_by(desc('y')).order_by(desc('m')).order_by(desc('d')).all() info = db.session.query(models.Product). \ filter(models.Server.id == models.Product.server_id). \ filter(models.Server.name == servername). \ filter(models.Product.common_name == productname).first() return render_template('pages/productname.html', users=users, chart_data=chart_data, info=info)
def username(username): workstations = db.session.query(models.Workstation, models.History). \ filter(models.User.id == models.History.user_id). \ filter(models.Workstation.id == models.History.workstation_id). \ group_by(models.Workstation.name).distinct(models.Workstation.name). \ filter(models.User.name == username).all() servers = db.session.query(models.Server, models.History). \ filter(models.User.id == models.History.user_id). \ filter(models.Updates.id == models.History.update_id). \ filter(models.Server.id == models.Updates.server_id). \ filter(models.User.name == username). \ group_by(models.Server.name).distinct(models.Server.name).all() products = db.session.query(models.Product.common_name, models.Product.type, models.History.time_in, func.sum(func.julianday(func.ifnull(models.History.calculated_timein, datetime.datetime.now())) - func.julianday( models.History.time_out)).label('time_sum')). \ filter(models.User.id == models.History.user_id). \ filter(models.User.name == username). \ filter(models.History.product_id == models.Product.id). \ group_by(models.Product.common_name).distinct(models.Product.common_name).all() return render_template('pages/username.html', workstations=workstations, servers=servers, products=products)
def workstationname(workstationname): users = db.session.query(User.name, History.time_in). \ filter(User.id == History.user_id). \ filter(Workstation.id == History.workstation_id). \ group_by(User.name).distinct(User.name). \ filter(Workstation.name == workstationname).all() servers = db.session.query(Server, History.time_in). \ filter(Workstation.id == History.workstation_id). \ filter(Updates.id == History.update_id). \ filter(Server.id == Updates.server_id). \ filter(Workstation.name == workstationname). \ group_by(Server.name).distinct(Server.name).all() products = db.session.query(Product.common_name, Product.type, History.time_in, func.sum(func.julianday(func.ifnull(History.calculated_timein, datetime.datetime.now())) - func.julianday( History.time_out)).label('time_sum')). \ filter(Workstation.id == History.workstation_id). \ filter(Workstation.name == workstationname). \ filter(History.product_id == Product.id). \ group_by(Product.common_name).distinct(Product.common_name).all() return render_template('pages/workstationname.html', users=users, servers=servers, products=products)
def workstations(): all_ws = db.session.query(Workstation.name, History.time_in, func.sum(func.julianday(func.ifnull(History.calculated_timein, datetime.datetime.now())) - func.julianday( History.time_out)).label('time_sum')). \ filter(Workstation.id == History.workstation_id). \ filter(History.product_id == Product.id). \ filter(Product.type == 'core'). \ distinct(Workstation.name).group_by(Workstation.name).all() return render_template('pages/workstations.html', ws=all_ws)
def users(): all_users = db.session.query(models.User.name, models.History.time_in, func.sum(func.julianday(func.ifnull(models.History.calculated_timein, datetime.datetime.now())) - func.julianday( models.History.time_out)).label('time_sum')). \ filter(models.User.id == models.History.user_id). \ filter(models.History.product_id == models.Product.id). \ filter(models.Product.type == 'core'). \ distinct(models.User.name).group_by(models.User.name).all() return render_template('pages/users.html', users=all_users)
def get_dag_duration_info(): '''get duration of currently running DagRuns :return dag_info ''' driver = Session.bind.driver # pylint: disable=no-member durations = { 'pysqlite': func.julianday(func.current_timestamp() - func.julianday(DagRun.start_date)) * 86400.0, 'mysqldb': func.timestampdiff(text('second'), DagRun.start_date, func.now()), 'default': func.now() - DagRun.start_date } duration = durations.get(driver, durations['default']) return Session.query( DagRun.dag_id, func.max(duration).label('duration')).group_by( DagRun.dag_id).filter(DagRun.state == State.RUNNING).all()
def do_job(self): """ SELECT sum(t.end_time - t.start_time) FOM task t GROUP BY day(t.start_time) WHERE start_time > GetDate() - @days :return: """ session = DBSession() daywork = func.sum(func.julianday(Task.end_time) - func.julianday(Task.start_time)) * 86400 day = func.date(Task.start_time) query = session.query(day, daywork) \ .group_by(day) \ .filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \ .filter(Task.end_time != None) \ .order_by(Task.start_time) print '\n' table = PrettyTable(['Day', 'Work Time', 'Graph']) table.align["Graph"] = "l" total_hours = timedelta(0) last_day = None for row in query: day = datetime.strptime(row[0], '%Y-%m-%d').date() if last_day: diff = (day - last_day) for i in range(diff.days - 1): table.add_row([last_day + timedelta(i + 1), 0, '']) worktime = timedelta(seconds=round(row[1])) total_hours += worktime table.add_row([day, worktime, '#' * int(round((row[1] * 60 / 86400)))]) last_day = day print table print 'Total Work time: %s' % total_hours print
def do_job(self): """ SELECT sum(t.end_time - t.start_time) FOM task t GROUP BY day(t.start_time) WHERE start_time > GetDate() - @days :return: """ session = DBSession() daywork = func.sum(func.julianday(Task.end_time) - func.julianday(Task.start_time)) * 86400 day = func.date(Task.start_time) query = session.query(day, daywork) \ .group_by(day) \ .filter(func.date(Task.start_time) > func.date('now', '-%s day' % self.args.days)) \ .filter(Task.end_time != None) \ .order_by(Task.start_time) print() table = PrettyTable(['Day', 'Work Time', 'Graph']) table.align["Graph"] = "l" total_hours = timedelta(0) last_day = None for row in query: day = datetime.strptime(row[0], '%Y-%m-%d').date() if last_day: diff = (day - last_day) for i in range(diff.days - 1): table.add_row([last_day + timedelta(i + 1), 0, '']) worktime = timedelta(seconds=round(row[1])) total_hours += worktime table.add_row([day, worktime, '#' * int(round((row[1] * 60 / 86400)))]) last_day = day print(table) print('Total Work time: %s\n' % total_hours)
def __init__(self, options): if options["dialect"] in ["postgres", "postgresql"]: # tables is a (dynamical) module containg Wrapper classes for our data base T = get_schema_rfl(options) # connect to the database db_uri = get_db_uri( options, "postgres") # "postgres" names the dialect we are using engine = create_engine(db_uri) engine.dialect.has_schema(engine, options["schema"]) self.schema = options["schema"] else: T = get_schema_rfl(options) # connect to the database db_uri = get_db_uri( options, "sqlite") # "postgres" names the dialect we are using engine = create_engine(db_uri) from sqlalchemy import event @event.listens_for(engine, "connect") def connect(dbapi_connection, connection_rec): dbapi_connection.enable_load_extension(True) dbapi_connection.execute( 'SELECT load_extension("libsqlitefunctions")') # ..how to bring all tables in T to the global namespace for tbl in T.__dict__.keys(): if not tbl[0].isupper(): continue exec(f"{tbl} = T.{tbl}") self.T = T self.engine = engine self.session = sessionmaker(bind=engine)() if options["dialect"] == "sqlite": # sqlite problematic when computing days self.to_days = lambda some_date: func.julianday(some_date) else: # dummy func because of sqlite self.to_days = lambda some_date: func.DATE(some_date)
def submit_by_day(cls, days=30): date_limit = datetime.now() - timedelta(days) # multi-database support (MySQL, PostgreSQL, SQLite) for date conversion engine = db.Session.get_bind() if "sqlite" in engine.driver: # could be 'sqlite', or 'pysqlite' fn = cast(func.julianday(cls.created_on), Integer) elif engine.driver == "postgresql": fn = cast(cls.created_on, sqlalchemy.types.Date) else: fn = func.to_days(cls.created_on) query = ( db.Session.query(fn.label("day"), func.count("*").label("count")) .filter(cls.created_on > date_limit) .group_by("day") .order_by("day ASC") ) results = [row.count for row in query.all()] return results
def productname(product_name): users = db.session.query(User.name, History.time_in, Server.name.label('servername'), func.sum(func.julianday(func.ifnull(History.calculated_timein, datetime.datetime.now())) - func.julianday( History.time_out)).label('time_sum')). \ filter(User.id == History.user_id). \ filter(History.product_id == Product.id). \ filter(Product.server_id == Server.id). \ filter(Product.common_name == product_name). \ distinct(User.name).group_by(User.name).all() # days = datetime.datetime.utcnow() - datetime.timedelta(days=days) # chart_data = db.session.query(func.count(History.user_id).label('users'), Product.license_total, # extract('month', History.time_out).label('m'), # extract('day', History.time_out).label('d'), # extract('year', History.time_out).label('y')). \ # filter(Product.id == History.product_id). \ # filter(Server.id == Updates.server_id). \ # filter(Updates.id == History.update_id). \ # filter(Server.name == server_name). \ # filter(Product.common_name == product_name). \ # distinct(History.user_id). \ # group_by(Product.common_name, Server.name, 'm', 'd', 'y'). \ # order_by(desc('y')).order_by(desc('m')).order_by(desc('d')).all() # filter(History.time_out > days). # info = db.session.query(Product). \ # filter(Server.id == Product.server_id). \ # filter(Server.name == server_name). \ # filter(Product.common_name == product_name).first() return render_template( 'pages/productname.html', users=users, # chart_data=chart_data, # info=info )
def _get_statistics(self): # this is the base method for obtaining base statistics about the running honeypot # the method has strong coupling because of the database structure assumptions # all honeypots utilizing this method must have the same table-object and column names # otherwise, the function must be overriden stats = {} stats['total_ips'] = self.session.query(func.count('*')).select_from( self.Source).scalar() stats['total_attacks'] = self.session.query( func.count('*')).select_from(self.Attack).scalar() stats['total_packets'] = self.session.query(func.sum( self.Attack.count)).scalar() stats['first_attack'] = self.session.query( self.Attack.src_id, func.min(self.Attack.start)).one_or_none() stats['last_attack'] = self.session.query( self.Attack.src_id, func.max(self.Attack.latest)).one_or_none() stats['longest_cont_attack'] = self.session.query( self.Attack.src_id, func.max( func.julianday(self.Attack.latest) - func.julianday(self.Attack.start)), self.Attack.start, self.Attack.latest, self.Attack.count).one_or_none() stats['largest_cont_attack'] = self.session.query( self.Attack.src_id, func.julianday(self.Attack.latest) - func.julianday(self.Attack.start), self.Attack.start, self.Attack.latest, func.max(self.Attack.count)).one_or_none() stats['avg_attack_duration'] = self.session.query( func.avg( func.julianday(self.Attack.latest) - func.julianday(self.Attack.start))).scalar() stats['top_attack'] = self.session.query( self.Attack.src_id, func.min(self.Attack.start).label('start'), func.max(self.Attack.latest).label('latest'), func.sum(self.Attack.count).label('total_count') ).group_by(self.Attack.src_id).\ order_by(desc('total_count')).first() # detailed (honeypot specific) details will be present as a dictionary under 'specific' key stats['specific'] = self._get_detailed_statistics() return stats
def dashboard(): day = func.strftime('%m/%d/%Y', Entry.created_on).label('day') avg = func.avg(Entry.val).label('avg') num_entries = func.count(Entry.id).label('num_entries') current_time = datetime.now() thirty_days_ago = current_time - timedelta(days=30) days_ago = (func.julianday(func.strftime('%Y-%m-%d', current_time)) - func.julianday(func.strftime( '%Y-%m-%d', Entry.created_on))).label('days_ago') recent_entries = db.session.query( day, avg, Entry.created_on, days_ago, num_entries ).filter(Entry.created_on >= thirty_days_ago)\ .filter_by( user_id = current_user.get_id(), parent_id = 0, entry_type = 0 ).order_by(day.desc()).group_by(day).all() # New entries object that has placeholders # for empty rows. entries_in_range = [] # Loop through entire day range. # If no entries found in that day # Add an emtpy flag. for day_ago in range(30 + 1): # Filter SQLAlchemy Results Object to find # result matching the day_ago in our loop. # Convert KeyedTuple object using _asdict() day_in_range = [ v._asdict() for v in recent_entries if math.floor(v.days_ago) == day_ago ] if (len(day_in_range)): # Result matching day_ago found. # Append to our new list entries_in_range.append(day_in_range) #flash('found entry {} - {} days ago'.format(day_in_range, day_ago)) else: date_ago = current_time - timedelta(days=day_ago) # Result didn't match this day_ago. # Append our empty flag. entries_in_range.append([{ 'has_no_entry': True, 'date_ago': date_ago }]) #flash("no entry {} days ago".format(day_ago)) return render_template('dashboard.html', title='Dashboard', entries_by_day=entries_in_range)
def _get_statistics(self): # overridden base method because generic honeypot can be ran on any port # extract statistics by port! stats = {} for attack in self.session.query( distinct(self.Attack.dst_port).label('dport')): stats[attack.dport] = {} for attack in self.session.query( func.count(self.Attack.src_id).label('src_count'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['total_ips'] = attack.src_count for attack in self.session.query( func.count('*').label('total_count'), func.sum(self.Attack.count).label('total_packets'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['total_attacks'] = attack.total_count stats[attack.dport]['total_packets'] = attack.total_packets for attack in self.session.query( self.Attack.src_id.label('src'), func.min(self.Attack.start).label('first_start'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['first_attack'] = (attack.src, attack.first_start) for attack in self.session.query( self.Attack.src_id.label('src'), func.max(self.Attack.latest).label('last_start'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['last_attack'] = (attack.src, attack.last_start) for attack in self.session.query( self.Attack.src_id.label('src'), func.max( func.julianday(self.Attack.latest) - func.julianday(self.Attack.start)).label('duration'), self.Attack.start, self.Attack.latest, self.Attack.count, self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['longest_cont_attack'] = (attack.src, attack.duration, attack.start, attack.latest, attack.count) for attack in self.session.query( self.Attack.src_id.label('src'), (func.julianday(self.Attack.latest) - func.julianday(self.Attack.start)).label('duration'), self.Attack.start, self.Attack.latest, func.max(self.Attack.count).label('pkt_sum'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['largest_cont_attack'] = (attack.src, attack.duration, attack.start, attack.latest, attack.pkt_sum) for attack in self.session.query( func.avg( func.julianday(self.Attack.latest) - func.julianday(self.Attack.start)).label('avg_duration'), self.Attack.dst_port.label('dport')).group_by('dport'): stats[attack.dport]['avg_attack_duration'] = attack.avg_duration for attack in self.session.query( self.Attack.src_id.label('src'), func.min(self.Attack.start).label('start_min'), func.max(self.Attack.latest).label('latest_max'), func.sum(self.Attack.count).label('total_count'), self.Attack.dst_port.label('dport') ).group_by('src','dport').\ order_by(desc('total_count')): if 'top_attack' not in stats[ attack.dport] or attack.total_count > stats[ attack.dport]['top_attack'][3]: stats[attack.dport]['top_attack'] = (attack.src, attack.start_min, attack.latest_max, attack.total_count) # detailed (honeypot specific) details will be present as a dictionary under 'specific' key stats['specific'] = self._get_detailed_statistics() return stats
def get_average_download_duration(): return DbSession.query(func.avg(Video.size)/func.avg((func.julianday(Video.downloaded_date) - func.julianday(Video.start_time))*86400), VideoType.name).filter(Video.start_time.isnot(None)).join(VideoType).group_by(Video.type).all()
def age(self): return (func.julianday(NOW) - func.julianday(self.date_of_birth)) / 365.25