Пример #1
0
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
Пример #2
0
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
Пример #3
0
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
Пример #4
0
    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()
Пример #5
0
    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
Пример #6
0
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'
Пример #7
0
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
Пример #8
0
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'
Пример #9
0
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
Пример #10
0
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, ""
Пример #11
0
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, ""
Пример #12
0
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'
Пример #13
0
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
Пример #14
0
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
Пример #15
0
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'
Пример #16
0
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
Пример #17
0
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, ""
Пример #18
0
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
Пример #19
0
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
Пример #20
0
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, ""
Пример #21
0
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
Пример #22
0
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
Пример #23
0
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
Пример #24
0
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
Пример #25
0
            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:
Пример #26
0
    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