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 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 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
