def db_set_config(key, value):
"""
Set a Config Value
"""
success = True
result = ""
try:
if re.search('(_key$|_pass$)', key) and len(value) > 0:
status, uuid = db_get_config('encryption_key')
if not status:
return False, "There has been an error setting the config value"
query = "REPLACE INTO config (conf, value) VALUES (:conf, AES_ENCRYPT(:val, :crypt))"
db.session.begin()
db.session.connection(mapper=Config).execute(sqltext(query), conf=key, val=value, crypt=uuid)
else:
query = "REPLACE INTO config (conf, value) VALUES (:conf, :val)"
db.session.begin()
db.session.connection(mapper=Config).execute(sqltext(query), conf=key, val=value)
db.session.commit()
except Exception as e:
db.session.rollback()
success = False
result = "There has been an error setting the config value: %s" % str(e)
api_log.error("[db_set_config] %s" % str(result))
return success, result
def db_set_config(key, value):
"""
Set a Config Value
"""
success = True
result = ""
try:
if re.search('(_key$|_pass$)', key) and len(value) > 0:
status, uuid = db_get_config('encryption_key')
if not status:
return False, "There has been an error setting the config value"
query = "REPLACE INTO config (conf, value) VALUES (:conf, AES_ENCRYPT(:val, :crypt))"
db.session.connection(mapper=Config).execute(sqltext(query),
conf=key,
val=value,
crypt=uuid)
else:
query = "REPLACE INTO config (conf, value) VALUES (:conf, :val)"
db.session.connection(mapper=Config).execute(sqltext(query),
conf=key,
val=value)
except Exception as e:
success = False
result = "There has been an error setting the config value: %s" % str(
e)
api_log.error("[db_set_config] %s" % str(result))
return success, result
def db_get_otx_event_trend(user='', pulse='', date_from='', date_to='', offset_tz=''):
"""Get the Trend of Events with Pulses:
Args:
user(string) : User Login - empty means any
pulse(string) : Number of Pulses to Display - empty means everything
date_from(string) : Date From - empty means everything
date_to(string) : Date To - empty means everything
offset_tz(string) : Timezone Offset
Returns:
trend_list (list) : List of event trend with pulses
"""
trend_list = {}
pulse_id = "0x%s" % pulse if pulse != '' else ''
try:
sp_call = sqltext("CALL otx_get_trend(:user, :pulse, :date_from, :date_to, :tz);")
result = db.session.connection(mapper=System).execute(sp_call, user=user, pulse=pulse_id, date_from=date_from, date_to=date_to, tz=offset_tz).fetchall()
for t_total, t_day in result:
trend_list[str(t_day)] = {'date': str(t_day),
'value': int(t_total)}
except Exception as err:
api_log.error("[db_get_otx_top_pulses] Error retrieving the top Pulses: %s" % str(err))
raise
return trend_list
def _deactivate_expired_organizations(self):
orgs_to_deactivate = Organization.query\
.filter_by(active=True)\
.filter(
and_(
or_(
Organization.paid_until == None,
Organization.paid_until < func.now()
),
func.timestampdiff(
sqltext("SECOND"),
Organization.created_at,
func.now(),
) > (Organization.trial_days * constants.SECONDS_PER_DAY),
)
)
for org in orgs_to_deactivate:
manager_url = url_for('manager.manager_app',
org_id=org.id,
_external=True) + "#settings"
# alert admins of deactivation
for admin in org.admins:
alert_email(
admin,
"[Action Required] %s scheduling is on hold" % org.name,
"In order to continue scheduling, please set up billing at:<br><a href='%s'>%s</a>"
% (manager_url, manager_url))
org.active = False
current_app.logger.info(
"Deactivated org %s because it is unpaid and the trial is over"
% org.id)
db.session.commit()
def _enqueue_schedules_mobius(self):
""" find and then queue all schedules that are due for mobius processing """
schedules_to_queue = Schedule2.query \
.join(Role) \
.join(Location) \
.join(Organization) \
.filter(
Schedule2.state.in_(["initial", "unpublished"]),
Organization.plan.in_(boss_plans),
Organization.active,
Role.archived == False,
func.timestampdiff(
sqltext("SECOND"),
func.now(),
Schedule2.start,
) < Organization.shifts_assigned_days_before_start * constants.SECONDS_PER_DAY,
).all()
for s in schedules_to_queue:
if s.state == "initial":
s.transition_to_unpublished()
s.transition_to_mobius_queue()
return len(schedules_to_queue) # For monitoring
def db_add_system(system_id,
name,
admin_ip,
vpn_ip=None,
profile='',
server_id=None,
sensor_id=None):
try:
sp_call = sqltext(
"CALL system_update('%s','%s','%s','%s','%s','','','','%s','%s')" %
(system_id, name, admin_ip, vpn_ip, profile, sensor_id, server_id))
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while adding the system into the database: %s" % str(
data)
if str(data[0]).find("updated") < 0 and str(
data[0]).find("created") < 0:
return False, "Something wrong happened while adding the system into the database: %s" % str(
data[0])
except Exception, e:
api_log.error(str(e))
db.session.rollback()
return False, 'Something wrong happened while adding the system into the database'
def db_get_otx_top_pulses(user='', top='', date_from='', date_to=''):
"""Get the Number of Events with Pulses:
Args:
user(string) : User Login - empty means any
top(int) : Number of Pulses to Display - empty means everything
date_from(string) : Date From - empty means everything
date_to(string) : Date To - empty means everything
Returns:
top_list (list): List of top pulses
"""
top_list = {}
try:
sp_call = sqltext(
"CALL otx_get_top_pulses(:user, :top, :date_from, :date_to);")
result = db.session.connection(mapper=System).execute(
sp_call, user=user, top=top, date_from=date_from,
date_to=date_to).fetchall()
for count, pulse_id in result:
top_list[pulse_id] = {'total': int(count)}
except Exception as err:
api_log.error(
"[db_get_otx_top_pulses] Error retrieving the top Pulses: %s" %
str(err))
raise
return top_list
def db_system_update_admin_ip(system_id, admin_ip):
if not is_valid_ipv4(admin_ip):
api_log.error('Invalid admin_ip %s' % str(admin_ip))
return False, 'Invalid admin ip %s' % str(admin_ip)
try:
sp_call = sqltext(
"CALL system_update('%s','','%s','','','','','','','')" %
(system_id, admin_ip))
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while updating system info in the database: %s" % str(
data)
if str(data[0]).find("updated") < 0 and str(
data[0]).find("created") < 0:
return False, "Something wrong happened while updating system info in the database: %s" % str(
data[0])
except Exception, e:
api_log.error(str(e))
db.session.rollback()
return False, 'Something wrong happened while updating system info in the database'
def db_get_config(key):
"""
Returns a config value
"""
query = "SELECT value, AES_DECRYPT(value, (SELECT value FROM config WHERE conf='encryption_key')) AS value_decrypt FROM config where conf = :conf"
try:
data = db.session.connection(mapper=Config).execute(
sqltext(query), conf=key).fetchall()
success = True
result = ""
if len(data) > 0:
result = data[0][1] if re.search(
'(_key$|_pass$)', key) and data[0][1] else data[0][0]
except NoResultFound:
success = True
result = ""
except Exception as e:
success = False
result = "There has been an error retrieving the config value: %s" % str(
e)
api_log.error("[db_get_config] %s" % str(result))
return success, result
def db_business_process():
try:
sp_call = sqltext("CALL business_processes()")
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
db.session.commit()
except Exception as err:
db.session.rollback()
return False, "Cannot run business process: %s" % str(err)
return True, ""
def db_business_process():
try:
sp_call = sqltext("CALL business_processes()")
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
db.session.commit()
except Exception as err:
db.session.rollback()
return False, "Cannot run business process: %s" % str(err)
return True, ""
def db_add_system(system_id, name, admin_ip, vpn_ip=None, profile='', server_id=None, sensor_id=None):
try:
sp_call = sqltext("CALL system_update('%s','%s','%s','%s','%s','','','','%s','%s')" % (system_id, name, admin_ip, vpn_ip, profile, sensor_id, server_id))
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while adding the system into the database: %s" % str(data)
if str(data[0]).find("updated") < 0 and str(data[0]).find("created") < 0:
return False, "Something wrong happened while adding the system into the database: %s" % str(data[0])
except Exception, e:
api_log.error(str(e))
db.session.rollback()
return False, 'Something wrong happened while adding the system into the database'
def db_get_otx_events(user):
"""Get the Number of Events with Pulses:
Returns:
events (int): Number of events with pulses
"""
try:
sp_call = sqltext("CALL otx_get_total_events(:user);")
result = db.session.connection(mapper=System).execute(sp_call, user=user).first()
events = int(result[0])
except Exception as err:
api_log.error("[db_get_otx_events] Error retrieving the top Pulses: %s" % str(err))
raise
return events
def get_contexts_stats():
"""
Get the context stats. Return a dict
whick key is the uuid of the context_id
"""
result = True, {}
try:
sql = sqltext("SELECT hex(entity_id) AS ctx_id, stat "
"FROM acl_entities_stats WHERE ts BETWEEN "
"TIMESTAMPADD(MINUTE, -30, NOW()) AND NOW()")
data = db.session.connection(mapper=Acl_Entities_Stats).execute(sql)
for entity, stats in data:
result[1][entity] = stats
except NoResultFound:
pass
return result
def db_system_update_hostname(system_id, hostname):
try:
sp_call = sqltext("CALL system_update('%s','%s','','','','','','','','')" % (system_id, hostname))
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while updating system info in the database: %s" % str(data)
if str(data[0]).find("updated") < 0 and str(data[0]).find("created") < 0:
return False, "Something wrong happened while updating system info in the database: %s" % str(data[0])
except Exception, e:
api_log.error(str(e))
db.session.rollback()
return False, 'Something wrong happened while updating system info in the database'
def get_contexts_stats():
"""
Get the context stats. Return a dict
whick key is the uuid of the context_id
"""
result = True, {}
try:
sql = sqltext("SELECT hex(entity_id) AS ctx_id, stat "
"FROM acl_entities_stats WHERE ts BETWEEN "
"TIMESTAMPADD(MINUTE, -30, NOW()) AND NOW()")
data = db.session.connection(mapper=Acl_Entities_Stats).execute(sql)
for entity, stats in data:
result[1][entity] = stats
except NoResultFound:
pass
return result
def db_remove_system(system_id):
try:
#system_id_bin = get_bytes_from_uuid(system_id)
#rc = db.session.query(System).filter(System.id == system_id_bin).delete()
sp_call = sqltext("CALL system_delete('%s')" % system_id)
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while removing the system from the database: %s" % str(data)
if str(data[0]).find("System deleted") < 0:
return False, "Something wrong happened while removing the system from the database: %s" % str(data[0])
except Exception as err:
db.session.rollback()
return False, "Something wrong happened while removing the system from the database: %s" % str(err)
return True, ""
def db_get_otx_events(user):
"""Get the Number of Events with Pulses:
Returns:
events (int): Number of events with pulses
"""
try:
sp_call = sqltext("CALL otx_get_total_events(:user);")
result = db.session.connection(mapper=System).execute(
sp_call, user=user).first()
events = int(result[0])
except Exception as err:
api_log.error(
"[db_get_otx_events] Error retrieving the top Pulses: %s" %
str(err))
raise
return events
def db_populate_user_permissions_table(login_user):
"""Populates the user_perm table
Args:
login_user(str): The user logged in
Returns:
result(bool):True if success, False otherwise
"""
result = True
sp_call = sqltext("CALL alienvault_api.fill_user_perms('%s')" % login_user)
try:
db.session.begin()
result_set = db.session.connection(mapper=UserPermissions).execute(sp_call)
data = result_set.fetchall()
db.session.commit()
if len(data) <= 0:
return False
except Exception as err:
db.session.rollback()
result = False
return result
def db_remove_system(system_id):
try:
#system_id_bin = get_bytes_from_uuid(system_id)
#rc = db.session.query(System).filter(System.id == system_id_bin).delete()
sp_call = sqltext("CALL system_delete('%s')" % system_id)
db.session.begin()
result = db.session.connection(mapper=System).execute(sp_call)
data = result.fetchall()
db.session.commit()
if len(data) <= 0:
return False, "Something wrong happened while removing the system from the database: %s" % str(
data)
if str(data[0]).find("System deleted") < 0:
return False, "Something wrong happened while removing the system from the database: %s" % str(
data[0])
except Exception as err:
db.session.rollback()
return False, "Something wrong happened while removing the system from the database: %s" % str(
err)
return True, ""
def db_populate_user_permissions_table(login_user):
"""Populates the user_perm table
Args:
login_user(str): The user logged in
Returns:
result(bool):True if success, False otherwise
"""
result = True
sp_call = sqltext("CALL alienvault_api.fill_user_perms('%s')" % login_user)
try:
db.session.begin()
result_set = db.session.connection(mapper=UserPermissions).execute(sp_call)
data = result_set.fetchall()
db.session.commit()
if len(data) <= 0:
return False
except Exception as err:
db.session.rollback()
result = False
return result
def db_get_otx_event_trend(user='',
pulse='',
date_from='',
date_to='',
offset_tz=''):
"""Get the Trend of Events with Pulses:
Args:
user(string) : User Login - empty means any
pulse(string) : Number of Pulses to Display - empty means everything
date_from(string) : Date From - empty means everything
date_to(string) : Date To - empty means everything
offset_tz(string) : Timezone Offset
Returns:
trend_list (list) : List of event trend with pulses
"""
trend_list = {}
pulse_id = "0x%s" % pulse if pulse != '' else ''
try:
sp_call = sqltext(
"CALL otx_get_trend(:user, :pulse, :date_from, :date_to, :tz);")
result = db.session.connection(mapper=System).execute(
sp_call,
user=user,
pulse=pulse_id,
date_from=date_from,
date_to=date_to,
tz=offset_tz).fetchall()
for t_total, t_day in result:
trend_list[str(t_day)] = {
'date': str(t_day),
'value': int(t_total)
}
except Exception as err:
api_log.error(
"[db_get_otx_top_pulses] Error retrieving the top Pulses: %s" %
str(err))
raise
return trend_list
def db_get_otx_top_pulses(user='', top='', date_from='', date_to=''):
"""Get the Number of Events with Pulses:
Args:
user(string) : User Login - empty means any
top(int) : Number of Pulses to Display - empty means everything
date_from(string) : Date From - empty means everything
date_to(string) : Date To - empty means everything
Returns:
top_list (list): List of top pulses
"""
top_list = {}
try:
sp_call = sqltext("CALL otx_get_top_pulses(:user, :top, :date_from, :date_to);")
result = db.session.connection(mapper=System).execute(sp_call, user=user, top=top, date_from=date_from, date_to=date_to).fetchall()
for count, pulse_id in result:
top_list[pulse_id] = {'total': int(count)}
except Exception as err:
api_log.error("[db_get_otx_top_pulses] Error retrieving the top Pulses: %s" % str(err))
raise
return top_list
def db_get_config(key):
"""
Returns a config value
"""
query = "SELECT value, AES_DECRYPT(value, (SELECT value FROM config WHERE conf='encryption_key')) AS value_decrypt FROM config where conf = :conf"
try:
data = db.session.connection(mapper=Config).execute(sqltext(query), conf=key).fetchall()
success = True
result = ""
if len(data) > 0:
result = data[0][1] if re.search('(_key$|_pass$)', key) and data[0][1] else data[0][0]
except NoResultFound:
success = True
result = ""
except Exception as e:
success = False
result = "There has been an error retrieving the config value: %s" % str(e)
api_log.error("[db_get_config] %s" % str(result))
return success, result
resp = es_client.search("email", doc_type="mail", **kwargs)
session["elapsed"] = c.elapsed = "%.4f" % (time.time() - startTime)
total = "{:,}".format(resp["hits"]["total"])
# return "%s" % [r["_source"] for r in resp["hits"]["hits"]][0]
allrecs = extract_records(resp)
session["fullresults"] = [rec["imsg"] for rec in allrecs]
c.session = session
else:
modelSession = model.meta.Session
query = modelSession.query(Archive)
query = query.filter_by(clist=self._listAbbreviation(c.listname))
start, end = self.date_range(dateRange, startDate, endDate)
query = query.filter(sqltext(u"tposted>=:start and tposted<:end")).params(start=start, end=end)
if authorRequired:
authComp = u"%%%s%%" % authorRequired
query = query.filter(model.archive_table.c.cfrom.like(authComp))
if phraseRequired:
phraseComp = u"%%%s%%" % phraseRequired
query = query.filter(model.archive_table.c.mtext.like(phraseComp))
if subjectPhraseRequired:
subjPhraseComp = u"%%%s%%" % subjectPhraseRequired
query = query.filter(model.archive_table.c.csubject.like(subjPhraseComp))
if c.listname == "profox":
if not chkNF:
query = query.filter(model.archive_table.c.csubject.op("not regexp")(u'[ [:punct:]]NF[ [:punct:]]'))
if not chkOT:
def _create_schedules(self):
""" Create schedules for active orgs """
default_tz = get_default_tz()
# Approach - Start with Roles. Join to Org so you know
# how much lead time for a schedule (demand_opends_days_before_start).
# Then, OUTER (left) join to Schedules. Look for schedules that
# are IN the window of that lead time. Then, becuase it's an OUTER join,
# filter by role IDs that do NOT have a schedule in that window.
# You are left with roles that need a schedule to be
# created in that window.
roles_needing_schedules = Role.query\
.join(Location)\
.join(Organization)\
.outerjoin(Schedule2,
and_(
Role.id == Schedule2.role_id,
# Convert to seconds to do this math. Note that `time-to-sec` is mysql-specific
func.timestampdiff(
sqltext("SECOND"),
func.now(),
Schedule2.start,
# If not offset by 7 - start a week early
) > current_app.config.get("SCHEDULES_CREATED_DAYS_BEFORE_START") * constants.SECONDS_PER_DAY,
),
)\
.filter(
Organization.active == True,
Role.archived == False,
Schedule2.id == None,
).all()
schedules_created = 0 # for return
# Make schedules until horizon for all roles that need them
start = None
schedule_horizon = default_tz.localize(datetime.utcnow() + timedelta(
days=current_app.config.get("SCHEDULES_CREATED_DAYS_BEFORE_START"))
)
# This is a half year of schedules.
# We discovered that during the apiv1 migration, some orgs only had a couple weeks
# worth of schedules. When _get_schedule_range() ran, it would get the dates for the next
# schedule. This requires a high ttl because it is making schedules in the past up to
# the 100 days in the future that we expect.
schedule_ttl = 27
for role in roles_needing_schedules:
start, stop = self._get_schedule_range(role)
current_ttl = schedule_ttl
while (start < schedule_horizon):
current_ttl -= 1
if current_ttl < 0:
raise Exception(
"Schedule creation process infinite looping - start %s role %s"
% (start, role))
Schedule2.create(role.id, start, stop)
schedules_created += 1
start, stop = self._get_schedule_range(role)
return schedules_created
