Ejemplo n.º 1
0
def last_scan(agent_id):
    """
    Gets the last scan of the agent.

    :param agent_id: Agent ID.
    :return: Dictionary: rootcheckEndTime, rootcheckTime.
    """
    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    data = {}
    # end time
    query = "SELECT max(date_first) FROM pm_event WHERE log = 'Ending rootcheck scan.'"
    conn.execute(query)
    for tuple in conn:
        data['rootcheckEndTime'] = tuple[0]

    # start time
    query = "SELECT max(date_first) FROM pm_event WHERE log = 'Starting rootcheck scan.'"
    conn.execute(query)
    for tuple in conn:
        data['rootcheckTime'] = tuple[0]

    return data
Ejemplo n.º 2
0
def _db_request(query, request={}):
    db_global = glob(common.database_path_global)
    if not db_global:
        raise WazuhException(1600)

    conn = Connection(db_global[0])
    conn.execute(query, request)

    return conn
Ejemplo n.º 3
0
def get_cis(agent_id=None, offset=0, limit=common.database_limit, sort=None, search=None):
    """
    Get all the CIS requirements used in the rootchecks of the agent.

    :param agent_id: Agent ID.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    query = "SELECT {0} FROM pm_event WHERE cis IS NOT NULL"
    fields = {}
    request = {}

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    # Search
    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " cis LIKE :search"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items
    conn.execute(query.format('COUNT(DISTINCT cis)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        allowed_sort_fields = fields.keys()
        for sf in sort['fields']:
            if sf not in allowed_sort_fields:
                raise WazuhException(1403, 'Allowed sort fields: {0}. Field: {1}'.format(allowed_sort_fields, sf))
        query += ' ORDER BY cis ' + sort['order']
    else:
        query += ' ORDER BY cis ASC'

    query += ' LIMIT :offset,:limit'
    request['offset'] = offset
    request['limit'] = limit


    conn.execute(query.format('DISTINCT cis'), request)

    data['items'] = []
    for tuple in conn:
        data['items'].append(tuple[0])

    return data
Ejemplo n.º 4
0
def get_cis(agent_id=None, offset=0, limit=common.database_limit, sort=None, search=None):
    """
    Get all the CIS requirements used in the rootchecks of the agent.

    :param agent_id: Agent ID.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    query = "SELECT {0} FROM pm_event WHERE cis IS NOT NULL"
    fields = {}
    request = {}

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    # Search
    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " cis LIKE :search"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items
    conn.execute(query.format('COUNT(DISTINCT cis)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        allowed_sort_fields = fields.keys()
        for sf in sort['fields']:
            if sf not in allowed_sort_fields:
                raise WazuhException(1403, 'Allowed sort fields: {0}. Field: {1}'.format(allowed_sort_fields, sf))
        query += ' ORDER BY cis ' + sort['order']
    else:
        query += ' ORDER BY cis ASC'

    query += ' LIMIT :offset,:limit'
    request['offset'] = offset
    request['limit'] = limit


    conn.execute(query.format('DISTINCT cis'), request)

    data['items'] = []
    for tuple in conn:
        data['items'].append(tuple[0])

    return data
Ejemplo n.º 5
0
def clear(agent_id=None, all_agents=False):
    """
    Clears the database.

    :param agent_id: For an agent.
    :param all_agents: For all agents.
    :return: Message.
    """

    # Clear DB
    if int(all_agents):
        db_agents = glob('{0}/*-*.db'.format(common.database_path_agents))
    else:
        Agent(agent_id).get_basic_information()  # check if the agent exists
        db_agents = glob('{0}/{1}-*.db'.format(common.database_path_agents,
                                               agent_id))

    if not db_agents:
        raise WazuhException(1600)

    for db_agent in db_agents:
        conn = Connection(db_agent)
        conn.begin()
        try:
            conn.execute('DELETE FROM pm_event')
        except WazuhException as e:
            raise e
        except Exception as exception:
            conn.commit()
            conn.vacuum()
            raise WazuhException(1654, exception)
        else:
            conn.commit()
            conn.vacuum()

    # Clear OSSEC info
    if int(all_agents):
        rootcheck_files = glob('{0}/queue/rootcheck/*'.format(
            common.ossec_path))
    else:
        if agent_id == "000":
            rootcheck_files = [
                '{0}/queue/rootcheck/rootcheck'.format(common.ossec_path)
            ]
        else:
            agent_info = Agent(agent_id).get_basic_information()
            rootcheck_files = glob(
                '{0}/queue/rootcheck/({1}) {2}->rootcheck'.format(
                    common.ossec_path, agent_info['name'], agent_info['ip']))

    for rootcheck_file in rootcheck_files:
        if path.exists(rootcheck_file):
            remove(rootcheck_file)

    return "Rootcheck database deleted"
Ejemplo n.º 6
0
    def _load_info_from_DB(self):
        """
        Gets attributes of existing agent.
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query = "SELECT {0} FROM agent WHERE id = :id"
        request = {'id': self.id}

        select = [
            "id", "name", "ip", "key", "os", "version", "date_add",
            "last_keepalive"
        ]

        conn.execute(query.format(','.join(select)), request)

        no_result = True
        for tuple in conn:
            no_result = False
            data_tuple = {}

            if tuple[0] != None:
                self.id = str(tuple[0]).zfill(3)
            if tuple[1] != None:
                self.name = tuple[1]
            if tuple[2] != None:
                self.ip = tuple[2]
            if tuple[3] != None:
                self.internal_key = tuple[3]
            if tuple[4] != None:
                self.os = tuple[4]
            if tuple[5] != None:
                self.version = tuple[5]
            if tuple[6] != None:
                self.dateAdd = tuple[6]
            if tuple[7] != None:
                self.lastKeepAlive = tuple[7]
            else:
                self.lastKeepAlive = 0

            if self.id != "000":
                self.status = Agent.calculate_status(self.lastKeepAlive)
            else:
                self.status = 'Active'
                self.ip = '127.0.0.1'

        if no_result:
            raise WazuhException(1701, self.id)
Ejemplo n.º 7
0
    def get_ossec_init(self):
        """
        Gets information from /etc/ossec-init.conf.

        :return: ossec-init.conf as dictionary
        """

        try:
            with open(self.OSSEC_INIT, 'r') as f:
                line_regex = re.compile('(^\w+)="(.+)"')
                for line in f:
                    match = line_regex.match(line)
                    if match and len(match.groups()) == 2:
                        key = match.group(1).lower()
                        if key == "version":
                            self.version = match.group(2)
                        elif key == "directory":
                            # Read 'directory' when ossec_path (__init__) is set by default.
                            # It could mean that get_init is True and ossec_path is not used.
                            if self.path == '/var/ossec':
                                self.path = match.group(2)
                        elif key == "date":
                            self.installation_date = match.group(2)
                        elif key == "type":
                            self.type = match.group(2)
        except:
            raise WazuhException(1005, self.OSSEC_INIT)

        # info DB
        conn = Connection(common.database_path_global)

        query = "SELECT * FROM info"
        conn.execute(query)

        for tuple in conn:
            if tuple[0] == 'max_agents':
                self.max_agents = tuple[1]
            elif tuple[0] == 'openssl_support':
                self.openssl_support = tuple[1]

        # Ruleset version
        ruleset_version_file = "{0}/ruleset/VERSION".format(self.path)
        try:
            with open(ruleset_version_file, 'r') as f:
                line_regex = re.compile('(^\w+)="(.+)"')
                for line in f:
                    match = line_regex.match(line)
                    if match and len(match.groups()) == 2:
                        self.ruleset_version = match.group(2)
        except:
            raise WazuhException(1005, ruleset_version_file)

        return self.to_dict()
Ejemplo n.º 8
0
    def _load_info_from_DB(self):
        """
        Gets attributes of existing agent.
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query = "SELECT {0} FROM agent WHERE id = :id"
        request = {'id': self.id}

        select = ["id", "name", "ip", "key", "os", "version", "date_add", "last_keepalive"]

        conn.execute(query.format(','.join(select)), request)

        no_result = True
        for tuple in conn:
            no_result = False
            data_tuple = {}

            if tuple[0] != None:
                self.id = str(tuple[0]).zfill(3)
            if tuple[1] != None:
                self.name = tuple[1]
            if tuple[2] != None:
                self.ip = tuple[2]
            if tuple[3] != None:
                self.internal_key = tuple[3]
            if tuple[4] != None:
                self.os = tuple[4]
            if tuple[5] != None:
                self.version = tuple[5]
            if tuple[6] != None:
                self.dateAdd = tuple[6]
            if tuple[7] != None:
                self.lastKeepAlive = tuple[7]
            else:
                self.lastKeepAlive = 0

            if self.id != "000":
                self.status = Agent.calculate_status(self.lastKeepAlive)
            else:
                self.status = 'Active'
                self.ip = '127.0.0.1'

        if no_result:
            raise WazuhException(1701, self.id)
Ejemplo n.º 9
0
def clear(agent_id=None, all_agents=False):
    """
    Clears the database.

    :param agent_id: For an agent.
    :param all_agents: For all agents.
    :return: Message.
    """

    # Clear DB
    if int(all_agents):
        db_agents = glob('{0}/*-*.db'.format(common.database_path_agents))
    else:
        db_agents = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))

    if not db_agents:
        raise WazuhException(1600)

    for db_agent in db_agents:
        conn = Connection(db_agent)
        conn.begin()
        try:
            conn.execute('DELETE FROM fim_event')
            conn.execute('DELETE FROM fim_file')
        except Exception as exception:
            raise exception
        finally:
            conn.commit()
            conn.vacuum()

    # Clear OSSEC info
    if int(all_agents):
        syscheck_files = glob('{0}/queue/syscheck/*'.format(common.ossec_path))
    else:
        if agent_id == "000":
            syscheck_files = ['{0}/queue/syscheck/syscheck'.format(common.ossec_path)]
        else:
            agent_info = Agent(agent_id).get_basic_information()
            syscheck_files = glob('{0}/queue/syscheck/({1}) {2}->syscheck'.format(common.ossec_path, agent_info['name'], agent_info['ip']))

    for syscheck_file in syscheck_files:
        if path.exists(syscheck_file):
            remove(syscheck_file)

    return "Syscheck database deleted"
Ejemplo n.º 10
0
    def _initialize(self):
        """
        Calculates all Wazuh installation metadata
        """

        # info DB if possible
        try:
            conn = Connection(common.database_path_global)

            query = "SELECT * FROM info"
            conn.execute(query)

            for tuple_ in conn:
                if tuple_[0] == 'max_agents':
                    self.max_agents = tuple_[1]
                elif tuple_[0] == 'openssl_support':
                    self.openssl_support = tuple_[1]
        except Exception:
            self.max_agents = "N/A"
            self.openssl_support = "N/A"

        # Ruleset version
        ruleset_version_file = os.path.join(self.path, 'ruleset', 'VERSION')
        try:
            with open(ruleset_version_file, 'r') as f:
                line_regex = re.compile(r'(^\w+)="(.+)"')
                for line in f:
                    match = line_regex.match(line)
                    if match and len(match.groups()) == 2:
                        self.ruleset_version = match.group(2)
        except Exception:
            raise WazuhException(1005, ruleset_version_file)

        # Timezone info
        try:
            self.tz_offset = strftime("%z")
            self.tz_name = strftime("%Z")
        except Exception:
            self.tz_offset = None
            self.tz_name = None

        return self.to_dict()
Ejemplo n.º 11
0
def clear_local(agent_id=None, all_agents=False):
    # Clear DB
    if int(all_agents):
        db_agents = glob('{0}/*-*.db'.format(common.database_path_agents))
    else:
        db_agents = glob('{0}/{1}-*.db'.format(common.database_path_agents,
                                               agent_id))

    if not db_agents:
        raise WazuhException(1600)

    for db_agent in db_agents:
        conn = Connection(db_agent)
        conn.begin()
        try:
            conn.execute('DELETE FROM fim_event')
            conn.execute('DELETE FROM fim_file')
        except Exception as exception:
            raise exception
        finally:
            conn.commit()
            conn.vacuum()

    # Clear OSSEC info
    if int(all_agents):
        syscheck_files = glob('{0}/queue/syscheck/*'.format(common.ossec_path))
    else:
        if agent_id == "000":
            syscheck_files = [
                '{0}/queue/syscheck/syscheck'.format(common.ossec_path)
            ]
        else:
            agent_info = Agent(agent_id).get_basic_information()
            syscheck_files = glob(
                '{0}/queue/syscheck/({1}) {2}->syscheck'.format(
                    common.ossec_path, agent_info['name'], agent_info['ip']))

    for syscheck_file in syscheck_files:
        if path.exists(syscheck_file):
            remove(syscheck_file)

    return "Syscheck database deleted"
Ejemplo n.º 12
0
    def get_ossec_init(self):
        """
        Gets information from /etc/ossec-init.conf.

        :return: ossec-init.conf as dictionary
        """

        try:
            with open(self.OSSEC_INIT, 'r') as f:
                line_regex = re.compile('(^\w+)="(.+)"')
                for line in f:
                    match = line_regex.match(line)
                    if match and len(match.groups()) == 2:
                        key = match.group(1).lower()
                        if key == "version":
                            self.version = match.group(2)
                        elif key == "directory":
                            # Read 'directory' when ossec_path (__init__) is set by default.
                            # It could mean that get_init is True and ossec_path is not used.
                            if self.path == '/var/ossec':
                                self.path = match.group(2)
                        elif key == "date":
                            self.installation_date = match.group(2)
                        elif key == "type":
                            self.type = match.group(2)
        except:
            raise WazuhException(1005, self.OSSEC_INIT)

        # info DB
        conn = Connection(common.database_path_global)

        query = "SELECT * FROM info"
        conn.execute(query)

        for tuple in conn:
            if tuple[0] == 'max_agents':
                self.max_agents = tuple[1]
            elif tuple[0] == 'openssl_support':
                self.openssl_support = tuple[1]

        return self.to_dict()
Ejemplo n.º 13
0
    def get_agents_summary():
        """
        Counts the number of agents by status.

        :return: Dictionary with keys: total, Active, Disconnected, Never connected
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query_all = "SELECT COUNT(*) FROM agent"

        query = "SELECT COUNT(*) FROM agent WHERE {0}"
        request = {}
        query_active = query.format(
            '(last_keepalive >= :time_active or id = 0)')
        query_disconnected = query.format('last_keepalive < :time_active')
        query_never = query.format('last_keepalive IS NULL AND id != 0')

        limit_seconds = 600 * 3 + 30
        result = datetime.now() - timedelta(seconds=limit_seconds)
        request['time_active'] = result.strftime('%Y-%m-%d %H:%M:%S')

        conn.execute(query_all)
        total = conn.fetch()[0]

        conn.execute(query_active, request)
        active = conn.fetch()[0]

        conn.execute(query_disconnected, request)
        disconnected = conn.fetch()[0]

        conn.execute(query_never, request)
        never = conn.fetch()[0]

        return {
            'Total': total,
            'Active': active,
            'Disconnected': disconnected,
            'Never connected': never
        }
Ejemplo n.º 14
0
    def get_agents_summary():
        """
        Counts the number of agents by status.

        :return: Dictionary with keys: total, Active, Disconnected, Never connected
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query_all = "SELECT COUNT(*) FROM agent"

        query = "SELECT COUNT(*) FROM agent WHERE {0}"
        request = {}
        query_active = query.format('(last_keepalive >= :time_active or id = 0)')
        query_disconnected = query.format('last_keepalive < :time_active')
        query_never = query.format('last_keepalive IS NULL AND id != 0')

        limit_seconds = 600*3 + 30
        result = datetime.now() - timedelta(seconds=limit_seconds)
        request['time_active'] = result.strftime('%Y-%m-%d %H:%M:%S')

        conn.execute(query_all)
        total = conn.fetch()[0]

        conn.execute(query_active, request)
        active = conn.fetch()[0]

        conn.execute(query_disconnected, request)
        disconnected = conn.fetch()[0]

        conn.execute(query_never, request)
        never = conn.fetch()[0]

        return {'Total': total, 'Active': active, 'Disconnected': disconnected, 'Never connected': never}
Ejemplo n.º 15
0
def get_all_groups(offset=0,
                   limit=common.database_limit,
                   sort=None,
                   search=None,
                   hash_algorithm='md5'):
    """
    Gets the existing groups.

    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """
    def get_hash(file, hash_algorithm='md5'):
        filename = "{0}/{1}".format(common.shared_path, file)

        # check hash algorithm
        try:
            algorithm_list = hashlib.algorithms_available
        except Exception as e:
            algorithm_list = hashlib.algorithms

        if not hash_algorithm in algorithm_list:
            raise WazuhException(
                1723, "Available algorithms are {0}.".format(algorithm_list))

        hashing = hashlib.new(hash_algorithm)

        try:
            with open(filename, 'rb') as f:
                hashing.update(f.read())
        except IOError:
            return None

        return hashing.hexdigest()

    # Connect DB
    db_global = glob(common.database_path_global)
    if not db_global:
        raise WazuhException(1600)

    conn = Connection(db_global[0])
    query = "SELECT {0} FROM agent WHERE `group` = :group_id"

    # Group names
    data = []
    for entry in listdir(common.shared_path):
        full_entry = path.join(common.shared_path, entry)
        if not path.isdir(full_entry):
            continue

        # Group count
        request = {'group_id': entry}
        conn.execute(query.format('COUNT(*)'), request)

        # merged.mg and agent.conf sum
        merged_sum = get_hash(entry + "/merged.mg")
        conf_sum = get_hash(entry + "/agent.conf")

        item = {'count': conn.fetch()[0], 'name': entry}

        if merged_sum:
            item['merged_sum'] = merged_sum

        if conf_sum:
            item['conf_sum'] = conf_sum

        data.append(item)

    if search:
        data = search_array(data,
                            search['value'],
                            search['negation'],
                            fields=['name'])

    if sort:
        data = sort_array(data, sort['fields'], sort['order'])
    else:
        data = sort_array(data, ['name'])

    return {'items': cut_array(data, offset, limit), 'totalItems': len(data)}
Ejemplo n.º 16
0
def print_db(agent_id=None,
             status='all',
             pci=None,
             cis=None,
             offset=0,
             limit=common.database_limit,
             sort=None,
             search=None):
    """
    Returns a list of events from the database.

    :param agent_id: Agent ID.
    :param status: Filters by status: outstanding, solved, all.
    :param pci: Filters by PCI DSS requirement.
    :param cis: Filters by CIS.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents,
                                          agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    request = {}
    fields = {
        'status': 'status',
        'event': 'log',
        'oldDay': 'date_first',
        'readDay': 'date_last'
    }

    partial = """SELECT {0} AS status, date_first, date_last, log, pci_dss, cis
        FROM pm_event AS t
        WHERE date_last {1} (SELECT datetime(date_last, '-86400 seconds') FROM pm_event WHERE log = 'Ending rootcheck scan.')"""

    if status == 'all':
        query = "SELECT {0} FROM (" + partial.format("'outstanding'", '>') + ' UNION ' + partial.format("'solved'", '<=') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"
    elif status == 'outstanding':
        query = "SELECT {0} FROM (" + partial.format("'outstanding'", '>') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"
    elif status == 'solved':
        query = "SELECT {0} FROM (" + partial.format("'solved'", '<=') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"

    if pci:
        query += ' AND pci_dss = :pci'
        request['pci'] = pci

    if cis:
        query += ' AND cis = :cis'
        request['cis'] = cis

    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(
            x + ' LIKE :search'
            for x in ('status', 'date_first', 'date_last', 'log')) + ")"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items

    conn.execute(query.format('COUNT(*)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        if sort['fields']:
            allowed_sort_fields = fields.keys()
            # Check if every element in sort['fields'] is in allowed_sort_fields
            if not set(sort['fields']).issubset(allowed_sort_fields):
                uncorrect_fields = list(
                    map(lambda x: str(x),
                        set(sort['fields']) - set(allowed_sort_fields)))
                raise WazuhException(
                    1403, 'Allowed sort fields: {0}. Fields: {1}'.format(
                        allowed_sort_fields, uncorrect_fields))

            query += ' ORDER BY ' + ','.join([
                '{0} {1}'.format(fields[i], sort['order'])
                for i in sort['fields']
            ])
        else:
            query += ' ORDER BY date_last {0}'.format(sort['order'])
    else:
        query += ' ORDER BY date_last DESC'

    if limit:
        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

    select = ["status", "date_first", "date_last", "log", "pci_dss", "cis"]

    conn.execute(query.format(','.join(select)), request)

    data['items'] = []
    for tuple in conn:
        data_tuple = {}

        if tuple[0] != None:
            data_tuple['status'] = tuple[0]
        if tuple[1] != None:
            data_tuple['oldDay'] = tuple[1]
        if tuple[2] != None:
            data_tuple['readDay'] = tuple[2]
        if tuple[3] != None:
            data_tuple['event'] = tuple[3]
        if tuple[4] != None:
            data_tuple['pci'] = tuple[4]
        if tuple[5] != None:
            data_tuple['cis'] = tuple[5]

        data['items'].append(data_tuple)

    return data
Ejemplo n.º 17
0
def get_agent_group(group_id,
                    offset=0,
                    limit=common.database_limit,
                    sort=None,
                    search=None,
                    select=None):
    """
    Gets the agents in a group

    :param group_id: Group ID.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connect DB
    db_global = glob(common.database_path_global)
    if not db_global:
        raise WazuhException(1600)

    conn = Connection(db_global[0])
    valid_select_fiels = [
        "id", "name", "ip", "last_keepalive", "os_name", "os_version",
        "os_platform", "os_uname", "version", "config_sum", "merged_sum",
        "manager_host"
    ]
    search_fields = {"id", "name", "os_name"}

    # Init query
    query = "SELECT {0} FROM agent WHERE `group` = :group_id"
    fields = {'id': 'id', 'name': 'name'}  # field: db_column
    request = {'group_id': group_id}

    # Select
    if select:
        if not set(select['fields']).issubset(valid_select_fiels):
            uncorrect_fields = map(
                lambda x: str(x),
                set(select['fields']) - set(valid_select_fiels))
            raise WazuhException(1724, "Allowed select fields: {0}. Fields {1}".\
                    format(valid_select_fiels, uncorrect_fields))
        select_fields = select['fields']
    else:
        select_fields = valid_select_fiels

    # Search
    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(x + ' LIKE :search'
                                    for x in search_fields) + " )"
        request['search'] = '%{0}%'.format(
            int(search['value']) if search['value'].isdigit(
            ) else search['value'])

    # Count
    conn.execute(query.format('COUNT(*)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        if sort['fields']:
            allowed_sort_fields = select_fields
            # Check if every element in sort['fields'] is in allowed_sort_fields.
            if not set(sort['fields']).issubset(allowed_sort_fields):
                raise WazuhException(1403, 'Allowed sort fields: {0}. Fields: {1}'.\
                    format(allowed_sort_fields, sort['fields']))

            order_str_fields = [
                '{0} {1}'.format(fields[i], sort['order'])
                for i in sort['fields']
            ]
            query += ' ORDER BY ' + ','.join(order_str_fields)
        else:
            query += ' ORDER BY id {0}'.format(sort['order'])
    else:
        query += ' ORDER BY id ASC'

    # OFFSET - LIMIT
    if limit:
        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

    # Data query
    conn.execute(query.format(','.join(select_fields)), request)

    non_nested = [{field:tuple_elem for field,tuple_elem \
            in zip(select_fields, tuple) if tuple_elem} for tuple in conn]
    map(lambda x: setitem(x, 'id', str(x['id']).zfill(3)), non_nested)

    data['items'] = [plain_dict_to_nested_dict(d, ['os']) for d in non_nested]

    return data
Ejemplo n.º 18
0
class WazuhDBQuery(object):
    """
    This class describes a database query for wazuh
    """
    def __init__(self,
                 offset,
                 limit,
                 table,
                 sort,
                 search,
                 select,
                 query,
                 fields,
                 default_sort_field,
                 db_path,
                 count,
                 get_data,
                 default_sort_order='ASC',
                 filters={},
                 min_select_fields=set(),
                 date_fields=set(),
                 extra_fields=set()):
        """
        Wazuh DB Query constructor

        :param offset: First item to return.
        :param limit: Maximum number of items to return.
        :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
        :param select: Select fields to return. Format: {"fields":["field1","field2"]}.
        :param filters: Defines field filters required by the user. Format: {"field1":"value1", "field2":["value2","value3"]}
        :param query: query to filter in database. Format: field operator value.
        :param search: Looks for items with the specified string. Format: {"fields": ["field1","field2"]}
        :param table: table to do the query
        :param fields: all available fields
        :param default_sort_field: by default, return elements sorted by this field
        :param db_path: database path
        :param default_sort_order: by default, return elements sorted in this order
        :param min_select_fields: fields that must be always be selected because they're necessary to compute other fields
        :param count: whether to compute totalItems or not
        :param date_fields: database fields that represent a date
        :param get_data: whether to return data or not
        """
        self.offset = offset
        self.limit = limit
        self.table = table
        self.sort = sort
        self.search = search
        self.select = None if not select else select.copy()
        self.fields = fields.copy()
        self.query = self._default_query()
        self.request = {}
        self.default_sort_field = default_sort_field
        self.default_sort_order = default_sort_order
        self.query_filters = []
        self.count = count
        self.data = get_data
        self.total_items = 0
        self.min_select_fields = min_select_fields
        self.query_operators = {
            "=": "=",
            "!=": "!=",
            "<": "<",
            ">": ">",
            "~": 'LIKE'
        }
        self.query_separators = {',': 'OR', ';': 'AND', '': ''}
        # To correctly turn a query into SQL, a regex is used. This regex will extract all necessary information:
        # For example, the following regex -> (name!=wazuh;id>5),group=webserver <- would return 3 different matches:
        #   (name != wazuh ;
        #    id   > 5      ),
        #    group=webserver
        self.query_regex = re.compile(
            r'(\()?' +  # A ( character.
            r'([\w.]+)' +  # Field name: name of the field to look on DB
            '([' + ''.join(self.query_operators.keys()) +
            "]{1,2})" +  # Operator: looks for =, !=, <, > or ~.
            r"([\w _\-\.:/']+)" +  # Value: A string.
            r"(\))?" +  # A ) character
            "([" + ''.join(self.query_separators.keys()) +
            "])?"  # Separator: looks for ;, , or nothing.
        )
        self.date_regex = re.compile(r"\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}")
        self.date_fields = date_fields
        self.extra_fields = extra_fields
        self.q = query
        self.legacy_filters = filters
        self.inverse_fields = {v: k for k, v in self.fields.items()}
        if db_path:
            if not glob.glob(db_path):
                raise WazuhException(1600)
            self.conn = Connection(db_path)

    def _add_limit_to_query(self):
        if self.limit:
            if self.limit > common.maximum_database_limit:
                raise WazuhException(1405, str(self.limit))
            self.query += ' LIMIT :offset,:limit'
            self.request['offset'] = self.offset
            self.request['limit'] = self.limit
        elif self.limit == 0:  # 0 is not a valid limit
            raise WazuhException(1406)

    def _sort_query(self, field):
        return '{} {}'.format(self.fields[field], self.sort['order'])

    def _add_sort_to_query(self):
        if self.sort:
            if self.sort['fields']:
                sort_fields, allowed_sort_fields = set(
                    self.sort['fields']), set(self.fields.keys())
                # check every element in sort['fields'] is in allowed_sort_fields
                if not sort_fields.issubset(allowed_sort_fields):
                    raise WazuhException(
                        1403, "Allowerd sort fields: {}. Fields: {}".format(
                            allowed_sort_fields,
                            ', '.join(sort_fields - allowed_sort_fields)))
                self.query += ' ORDER BY ' + ','.join(
                    [self._sort_query(i) for i in sort_fields])
            else:
                self.query += ' ORDER BY {0} {1}'.format(
                    self.default_sort_field, self.sort['order'])
        else:
            self.query += ' ORDER BY {0} {1}'.format(self.default_sort_field,
                                                     self.default_sort_order)

    def _add_search_to_query(self):
        if self.search:
            self.query += " AND NOT" if bool(
                self.search['negation']) else ' AND'
            self.query += " (" + " OR ".join(
                f'({x.split(" as ")[0]} LIKE :search AND {x.split(" as ")[0]} IS NOT NULL)'
                for x in self.fields.values()) + ')'
            self.query = self.query.replace('WHERE  AND', 'WHERE')
            self.request['search'] = "%{0}%".format(self.search['value'])

    def _parse_select_filter(self, select_fields):
        if select_fields:
            set_select_fields = set(select_fields['fields'])
            set_fields_keys = set(self.fields.keys()) - self.extra_fields
            if not set_select_fields.issubset(set_fields_keys):
                raise WazuhException(1724, "Allowed select fields: {0}. Fields {1}". \
                                     format(', '.join(self.fields.keys()), ', '.join(set_select_fields - set_fields_keys)))

            select_fields['fields'] = set_select_fields
        else:
            select_fields = {'fields': set(self.fields.keys())}

        return select_fields

    def _add_select_to_query(self):
        self.select = self._parse_select_filter(self.select)

    def _parse_query(self):
        """
        A query has the following pattern: field operator value separator field operator value...
        An example of query: status=never connected;name!=pepe
            * Field must be a database field (it must be contained in self.fields variable)
            * operator must be one of = != < >
            * value can be anything
            * Separator can be either ; for 'and' or , for 'or'.

        :return: A list with processed query (self.fields)
        """
        if not self.query_regex.match(self.q):
            raise WazuhException(1407, self.q)

        level = 0
        for open_level, field, operator, value, close_level, separator in self.query_regex.findall(
                self.q):
            if field not in self.fields.keys():
                raise WazuhException(
                    1408, "Available fields: {}. Field: {}".format(
                        ', '.join(self.fields), field))
            if operator not in self.query_operators:
                raise WazuhException(
                    1409, "Valid operators: {}. Used operator: {}".format(
                        ', '.join(self.query_operators), operator))

            if open_level:
                level += 1
            if close_level:
                level -= 1

            if not self._pass_filter(value):
                op_index = len(
                    list(
                        filter(lambda x: field in x['field'],
                               self.query_filters)))
                self.query_filters.append({
                    'value':
                    None if value == "null" else value,
                    'operator':
                    self.query_operators[operator],
                    'field':
                    '{}${}'.format(field, op_index),
                    'separator':
                    self.query_separators[separator],
                    'level':
                    level
                })

    def _parse_legacy_filters(self):
        """
        Parses legacy filters.
        """
        # some legacy filters can contain multiple values to filter separated by commas. That must split in a list.
        legacy_filters_as_list = {
            name: value.split(',') if isinstance(value, str) else
            (value if isinstance(value, list) else [value])
            for name, value in self.legacy_filters.items()
        }
        # each filter is represented using a dictionary containing the following fields:
        #   * Value     -> Value to filter by
        #   * Field     -> Field to filter by. Since there can be multiple filters over the same field, a numeric ID
        #                  must be added to the field name.
        #   * Operator  -> Operator to use in the database query. In legacy filters the only available one is =.
        #   * Separator -> Logical operator used to join queries. In legacy filters, the AND operator is used when
        #                  different fields are filtered and the OR operator is used when filtering by the same field
        #                  multiple times.
        #   * Level     -> The level defines the number of parenthesis the query has. In legacy filters, no
        #                  parenthesis are used except when filtering over the same field.
        self.query_filters += [{
            'value': None if subvalue == "null" else subvalue,
            'field': '{}${}'.format(name, i),
            'operator': '=',
            'separator': 'OR' if len(value) > 1 else 'AND',
            'level': 0 if i == len(value) - 1 else 1
        } for name, value in legacy_filters_as_list.items()
                               for subvalue, i in zip(value, range(len(value)))
                               if not self._pass_filter(subvalue)]
        if self.query_filters:
            # if only traditional filters have been defined, remove last AND from the query.
            self.query_filters[-1]['separator'] = '' if not self.q else 'AND'

    def _parse_filters(self):
        if self.legacy_filters:
            self._parse_legacy_filters()
        if self.q:
            self._parse_query()
        if self.search or self.query_filters:
            self.query += " WHERE " if 'WHERE' not in self.query else ' AND '

    def _process_filter(self, field_name, field_filter, q_filter):
        if field_name == "status":
            self._filter_status(q_filter)
        elif field_name in self.date_fields and not self.date_regex.match(
                q_filter['value']):
            # filter a date, but only if it is in timeframe format.
            # If it matches the same format as DB (YYYY-MM-DD hh:mm:ss), filter directly by value (next if cond).
            self._filter_date(q_filter, field_name)
        else:
            if q_filter['value'] is not None:
                self.request[field_filter] = q_filter[
                    'value'] if field_name != "version" else re.sub(
                        r'([a-zA-Z])([v])', r'\1 \2', q_filter['value'])
                if q_filter['operator'] == 'LIKE':
                    self.request[field_filter] = "%{}%".format(
                        self.request[field_filter])
                self.query += '{} {} :{}'.format(
                    self.fields[field_name].split(' as ')[0],
                    q_filter['operator'], field_filter)
                if not field_filter.isdigit():
                    # filtering without being uppercase/lowercase sensitive
                    self.query += ' COLLATE NOCASE'
            else:
                self.query += '{} IS null'.format(self.fields[field_name])

    def _add_filters_to_query(self):
        self._parse_filters()
        curr_level = 0
        for q_filter in self.query_filters:
            field_name = q_filter['field'].split('$', 1)[0]
            field_filter = q_filter['field'].replace('.', '_')

            self.query += '((' if curr_level < q_filter['level'] else '('

            self._process_filter(field_name, field_filter, q_filter)

            self.query += ('))' if curr_level > q_filter['level'] else
                           ')') + ' {} '.format(q_filter['separator'])
            curr_level = q_filter['level']

    def _get_total_items(self):
        self.conn.execute(self.query.format(self._default_count_query()),
                          self.request)
        self.total_items = self.conn.fetch()[0]

    def _get_data(self):
        self.conn.execute(
            self.query.format(','.join(
                map(lambda x: f"{self.fields[x]} as '{x}'",
                    self.select['fields'] | self.min_select_fields))),
            self.request)

    def _format_data_into_dictionary(self):
        return {
            'items': [{
                key: value
                for key, value in zip(
                    self.select['fields'] | self.min_select_fields, db_tuple)
                if value is not None
            } for db_tuple in self.conn],
            'totalItems':
            self.total_items
        }

    def _filter_status(self, status_filter):
        raise NotImplementedError

    def _filter_date(self, date_filter, filter_db_name):
        # date_filter['value'] can be either a timeframe or a date in format %Y-%m-%d %H:%M:%S
        if date_filter['value'].isdigit() or re.match(r'\d+[dhms]',
                                                      date_filter['value']):
            query_operator = '>' if date_filter[
                'operator'] == '<' or date_filter['operator'] == '=' else '<'
            self.request[date_filter['field']] = get_timeframe_in_seconds(
                date_filter['value'])
            self.query += "({0} IS NOT NULL AND CAST(strftime('%s', {0}) AS INTEGER) {1}" \
                          " CAST(strftime('%s', 'now', 'localtime') AS INTEGER) - :{2}) ".format(self.fields[filter_db_name],
                                                                                                 query_operator,
                                                                                                 date_filter['field'])
        elif re.match(r'\d{4}-\d{2}-\d{2}', date_filter['value']):
            self.query += "{0} IS NOT NULL AND {0} {1} :{2}".format(
                self.fields[filter_db_name], date_filter['operator'],
                date_filter['field'])
            self.request[date_filter['field']] = date_filter['value']
        else:
            raise WazuhException(1412, date_filter['value'])

    def run(self):
        """
        Builds the query and runs it on the database
        """

        self._add_select_to_query()
        self._add_filters_to_query()
        self._add_search_to_query()
        if self.count:
            self._get_total_items()
        self._add_sort_to_query()
        self._add_limit_to_query()
        if self.data:
            self._get_data()
            return self._format_data_into_dictionary()

    def reset(self):
        """
        Resets query to its initial value. Useful when doing several requests to the same DB.
        """
        self.query = self._default_query()
        self.query_filters = []
        self.select['fields'] -= self.extra_fields

    def _default_query(self):
        """
        :return: The default query
        """
        return "SELECT {0} FROM " + self.table

    def _default_count_query(self):
        return "COUNT(*)"

    @staticmethod
    def _pass_filter(db_filter):
        return db_filter == "all"
Ejemplo n.º 19
0
def files(agent_id=None, event=None, filename=None, filetype='file', md5=None, sha1=None, hash=None, summary=False, offset=0, limit=common.database_limit, sort=None, search=None):
    """
    Return a list of files from the database that match the filters

    :param agent_id: Agent ID.
    :param event: Filters by event: added, readded, modified, deleted.
    :param filename: Filters by filename.
    :param filetype: Filters by filetype: file or registry.
    :param md5: Filters by md5 hash.
    :param sha1: Filters by sha1 hash.
    :param hash: Filters by md5 or sha1 hash.
    :param summary: Returns a summary grouping by filename.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    agent_os = Agent(agent_id).get_basic_information()['os']

    if "windows" in agent_os.lower():
        windows_agent = True
    else:
        windows_agent = False

    fields = {'scanDate': 'date', 'modificationDate': 'mtime', 'file': 'path', 'size': 'size', 'user': '******', 'group': 'gname'}

    # Query
    query = "SELECT {0} FROM fim_event, fim_file WHERE fim_event.id_file = fim_file.id AND fim_file.type = :filetype"
    request = {'filetype': filetype}

    if event:
        query += ' AND fim_event.type = :event'
        request['event'] = event

    if filename:
        query += ' AND path = :filename'
        request['filename'] = filename

    if md5:
        query += ' AND md5 = :md5'
        request['md5'] = md5

    if sha1:
        query += ' AND sha1 = :sha1'
        request['sha1'] = sha1

    if hash:
        query += ' AND (md5 = :hash OR sha1 = :hash)'
        request['hash'] = hash

    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(x + ' LIKE :search' for x in ('path', "date", 'size', 'md5', 'sha1', 'uname', 'gname', 'inode', 'perm')) + " )"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items
    if summary:
        query += ' group by path'
        conn.execute("SELECT COUNT(*) FROM ({0}) AS TEMP".format(query.format("max(date)")), request)
    else:
        conn.execute(query.format('COUNT(*)'), request)

    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        allowed_sort_fields = fields.keys()
        for sf in sort['fields']:
            if sf not in allowed_sort_fields:
                raise WazuhException(1403, 'Allowed sort fields: {0}. Field: {1}'.format(allowed_sort_fields, sf))

        query += ' ORDER BY ' + ','.join(['{0} {1}'.format(fields[i], sort['order']) for i in sort['fields']])
    else:
        query += ' ORDER BY date DESC'

    query += ' LIMIT :offset,:limit'
    request['offset'] = offset
    request['limit'] = limit

    if summary:
        select = ["max(date)", "mtime", "fim_event.type", "path"]
    else:
        select = ["date", "mtime", "fim_event.type", "path", "size", "perm", "uid", "gid", "md5", "sha1", "uname", "gname", "inode"]

    conn.execute(query.format(','.join(select)), request)

    data['items'] = []

    for tuple in conn:
        data_tuple = {}

        if tuple[0] != None:
            data_tuple['scanDate'] = tuple[0]
        if tuple[1] != None:
            data_tuple['modificationDate'] = tuple[1]  # modificationDate
        else:
            data_tuple['modificationDate'] = tuple[0]  # scanDate
        if tuple[2] != None:
            data_tuple['event'] = tuple[2]
        if tuple[3] != None:
            data_tuple['file'] = tuple[3]

        if not summary:
            try:
                permissions = filemode(int(tuple[5], 8))
            except TypeError:
                permissions = None

            if tuple[4] != None:
                data_tuple['size'] = tuple[4]
            if tuple[8] != None:
                data_tuple['md5'] = tuple[8]
            if tuple[9] != None:
                data_tuple['sha1'] = tuple[9]
            if tuple[12] != None:
                data_tuple['inode'] = tuple[12]

            if not windows_agent:
                if tuple[6] != None:
                    data_tuple['uid'] = tuple[6]
                if tuple[7] != None:
                    data_tuple['gid'] = tuple[7]

                if tuple[10] != None:
                    data_tuple['user'] = tuple[10]
                if tuple[11] != None:
                    data_tuple['group'] = tuple[11]

                if tuple[5] != None:
                    data_tuple['octalMode'] = tuple[5]
                if permissions:
                    data_tuple['permissions'] = permissions


        data['items'].append(data_tuple)

    return data
Ejemplo n.º 20
0
    def remove_bulk_agents(agent_ids_list: KeysView, logger):
        """
        Removes files created by agents in worker nodes. This function doesn't remove agents from client.keys since the
        client.keys file is overwritten by the master node.
        :param agent_ids_list: List of agents ids to remove.
        :param logger: Logger to use
        :return: None.
        """
        def remove_agent_file_type(agent_files: List[str]):
            """
            Removes files if they exist
            :param agent_files: Path regexes of the files to remove
            :return: None
            """
            for filetype in agent_files:

                filetype_glob = filetype.format(ossec_path=common.ossec_path,
                                                id='*',
                                                name='*',
                                                ip='*')
                filetype_agent = {
                    filetype.format(ossec_path=common.ossec_path,
                                    id=a['id'],
                                    name=a['name'],
                                    ip=a['ip'])
                    for a in agent_info
                }

                for agent_file in set(
                        glob.iglob(filetype_glob)) & filetype_agent:
                    logger.debug2("Removing {}".format(agent_file))
                    if os.path.isdir(agent_file):
                        shutil.rmtree(agent_file)
                    else:
                        os.remove(agent_file)

        if not agent_ids_list:
            return  # the function doesn't make sense if there is no agents to remove

        logger.info("Removing files from {} agents".format(
            len(agent_ids_list)))
        logger.debug("Agents to remove: {}".format(', '.join(agent_ids_list)))
        # the agents must be removed in groups of 997: 999 is the limit of SQL variables per query. Limit and offset are
        # always included in the SQL query, so that leaves 997 variables as limit.
        for agents_ids_sublist in itertools.zip_longest(*itertools.repeat(
                iter(agent_ids_list), 997),
                                                        fillvalue='0'):
            agents_ids_sublist = list(
                filter(lambda x: x != '0', agents_ids_sublist))
            # Get info from DB
            agent_info = Agent.get_agents_overview(
                q=",".join(["id={}".format(i) for i in agents_ids_sublist]),
                select={'fields': ['ip', 'id', 'name']},
                limit=None)['items']
            logger.debug2("Removing files from agents {}".format(
                ', '.join(agents_ids_sublist)))

            files_to_remove = [
                '{ossec_path}/queue/agent-info/{name}-{ip}',
                '{ossec_path}/queue/rootcheck/({name}) {ip}->rootcheck',
                '{ossec_path}/queue/diff/{name}',
                '{ossec_path}/queue/agent-groups/{id}',
                '{ossec_path}/queue/rids/{id}',
                '{ossec_path}/var/db/agents/{name}-{id}.db'
            ]
            remove_agent_file_type(files_to_remove)

            logger.debug2("Removing agent group assigments from database")
            # remove agent from groups
            db_global = glob.glob(common.database_path_global)
            if not db_global:
                raise WazuhException(1600)

            conn = Connection(db_global[0])
            agent_ids_db = {
                'id_agent{}'.format(i): int(i)
                for i in agents_ids_sublist
            }
            conn.execute(
                'delete from belongs where {}'.format(' or '.join([
                    'id_agent = :{}'.format(i) for i in agent_ids_db.keys()
                ])), agent_ids_db)
            conn.commit()

            # Tell wazuhbd to delete agent database
            wdb_conn = WazuhDBConnection()
            wdb_conn.delete_agents_db(agents_ids_sublist)

        logger.info("Agent files removed")
Ejemplo n.º 21
0
    def remove_bulk_agents(agent_ids_list, logger):
        """
        Removes files created by agents in worker nodes. This function doesn't remove agents from client.keys since the
        client.keys file is overwritten by the master node.
        :param agent_ids_list: List of agents ids to remove.
        :return: None.
        """
        def remove_agent_file_type(glob_args, agent_args, agent_files):
            for filetype in agent_files:
                for agent_file in set(glob.iglob(filetype.format(common.ossec_path, *glob_args))) & \
                                  {filetype.format(common.ossec_path, *(a[arg] for arg in agent_args)) for a in
                                   agent_info}:
                    logger.debug2("Removing {}".format(agent_file))
                    if os.path.isdir(agent_file):
                        shutil.rmtree(agent_file)
                    else:
                        os.remove(agent_file)

        if not agent_ids_list:
            return  # the function doesn't make sense if there is no agents to remove

        logger.info("Removing files from {} agents".format(
            len(agent_ids_list)))
        logger.debug("Agents to remove: {}".format(', '.join(agent_ids_list)))
        # the agents must be removed in groups of 997: 999 is the limit of SQL variables per query. Limit and offset are
        # always included in the SQL query, so that leaves 997 variables as limit.
        for agents_ids_sublist in itertools.zip_longest(*itertools.repeat(
                iter(agent_ids_list), 997),
                                                        fillvalue='0'):
            agents_ids_sublist = list(
                filter(lambda x: x != '0', agents_ids_sublist))
            # Get info from DB
            agent_info = Agent.get_agents_overview(
                q=",".join(["id={}".format(i) for i in agents_ids_sublist]),
                select={'fields': ['ip', 'id', 'name']},
                limit=None)['items']
            logger.debug2("Removing files from agents {}".format(
                ', '.join(agents_ids_sublist)))

            # Remove agent files that need agent name and ip
            agent_files = [
                '{}/queue/agent-info/{}-{}',
                '{}/queue/rootcheck/({}) {}->rootcheck'
            ]
            remove_agent_file_type(('*', '*'), ('name', 'ip'), agent_files)

            # remove agent files that need agent name
            agent_files = ['{}/queue/diff/{}']
            remove_agent_file_type(('*', ), ('name', ), agent_files)

            # Remove agent files that only need agent id
            agent_files = [
                '{}/queue/agent-groups/{}', '{}/queue/rids/{}',
                '{}/queue/db/{}.db', '{}/queue/db/{}.db-wal',
                '{}/queue/db/{}.db-shm'
            ]
            remove_agent_file_type(('*', ), ('id', ), agent_files)

            # remove agent files that need agent name and id
            agent_files = ['{}/var/db/agents/{}-{}.db']
            remove_agent_file_type(('*', '*'), ('id', 'name'), agent_files)

            # remove agent from groups
            db_global = glob.glob(common.database_path_global)
            if not db_global:
                raise WazuhException(1600)

            conn = Connection(db_global[0])
            agent_ids_db = {
                'id_agent{}'.format(i): int(i)
                for i in agents_ids_sublist
            }
            conn.execute(
                'delete from belongs where {}'.format(' or '.join([
                    'id_agent = :{}'.format(i) for i in agent_ids_db.keys()
                ])), agent_ids_db)
            conn.commit()
        logger.info("Agent files removed")
Ejemplo n.º 22
0
def last_scan(agent_id):
    """
    Gets the last scan of the agent.

    :param agent_id: Agent ID.
    :return: Dictionary: end, start.
    """
    my_agent = Agent(agent_id)
    # if agent status is never connected, a KeyError happens
    try:
        agent_version = my_agent.get_basic_information(
            select={'fields': ['version']})['version']
    except KeyError:
        # if the agent is never connected, it won't have either version (key error) or last scan information.
        return {'start': 'ND', 'end': 'ND'}

    if WazuhVersion(agent_version) < WazuhVersion('Wazuh v3.7.0'):
        db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents,
                                              agent_id))
        if not db_agent:
            raise WazuhException(1600)
        else:
            db_agent = db_agent[0]
        conn = Connection(db_agent)

        data = {}
        # end time
        query = "SELECT max(date_last) FROM pm_event WHERE log = 'Ending rootcheck scan.'"
        conn.execute(query)
        for tuple in conn:
            data['end'] = tuple['max(date_last)'] if tuple[
                'max(date_last)'] is not None else "ND"

        # start time
        query = "SELECT max(date_last) FROM pm_event WHERE log = 'Starting rootcheck scan.'"
        conn.execute(query)
        for tuple in conn:
            data['start'] = tuple['max(date_last)'] if tuple[
                'max(date_last)'] is not None else "ND"

        return data
    else:
        fim_scan_info = WazuhDBQuerySyscheck(
            agent_id=agent_id,
            query='module=fim',
            offset=0,
            sort=None,
            search=None,
            limit=common.database_limit,
            select={
                'fields': ['end', 'start']
            },
            fields={
                'end': 'end_scan',
                'start': 'start_scan',
                'module': 'module'
            },
            table='scan_info',
            default_sort_field='start_scan').run()['items'][0]

        return fim_scan_info
Ejemplo n.º 23
0
def files(agent_id=None,
          event=None,
          filename=None,
          filetype='file',
          md5=None,
          sha1=None,
          hash=None,
          summary=False,
          offset=0,
          limit=common.database_limit,
          sort=None,
          search=None):
    """
    Return a list of files from the database that match the filters

    :param agent_id: Agent ID.
    :param event: Filters by event: added, readded, modified, deleted.
    :param filename: Filters by filename.
    :param filetype: Filters by filetype: file or registry.
    :param md5: Filters by md5 hash.
    :param sha1: Filters by sha1 hash.
    :param hash: Filters by md5 or sha1 hash.
    :param summary: Returns a summary grouping by filename.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents,
                                          agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    agent_info = Agent(agent_id).get_basic_information()
    if 'os' in agent_info and 'platform' in agent_info['os']:
        if agent_info['os']['platform'].lower() == 'windows':
            windows_agent = True
        else:
            windows_agent = False
    else:
        # We do not know if it is a windows or linux agent.
        # It is set to windows agent in order to avoid wrong data (uid, gid, ...)
        windows_agent = True

    fields = {
        'scanDate': 'date',
        'modificationDate': 'mtime',
        'file': 'path',
        'size': 'size',
        'user': '******',
        'group': 'gname'
    }

    # Query
    query = "SELECT {0} FROM fim_event, fim_file WHERE fim_event.id_file = fim_file.id AND fim_file.type = :filetype"
    request = {'filetype': filetype}

    if event:
        query += ' AND fim_event.type = :event'
        request['event'] = event

    if filename:
        query += ' AND path = :filename'
        request['filename'] = filename

    if md5:
        query += ' AND md5 = :md5'
        request['md5'] = md5

    if sha1:
        query += ' AND sha1 = :sha1'
        request['sha1'] = sha1

    if hash:
        query += ' AND (md5 = :hash OR sha1 = :hash)'
        request['hash'] = hash

    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(
            x + ' LIKE :search'
            for x in ('path', "date", 'size', 'md5', 'sha1', 'uname', 'gname',
                      'inode', 'perm')) + " )"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items
    if summary:
        query += ' group by path'
        conn.execute(
            "SELECT COUNT(*) FROM ({0}) AS TEMP".format(
                query.format("max(date)")), request)
    else:
        conn.execute(query.format('COUNT(*)'), request)

    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        allowed_sort_fields = fields.keys()
        for sf in sort['fields']:
            if sf not in allowed_sort_fields:
                raise WazuhException(
                    1403, 'Allowed sort fields: {0}. Field: {1}'.format(
                        allowed_sort_fields, sf))

        query += ' ORDER BY ' + ','.join([
            '{0} {1}'.format(fields[i], sort['order']) for i in sort['fields']
        ])
    else:
        query += ' ORDER BY date DESC'

    query += ' LIMIT :offset,:limit'
    request['offset'] = offset
    request['limit'] = limit

    if summary:
        select = ["max(date)", "mtime", "fim_event.type", "path"]
    else:
        select = [
            "date", "mtime", "fim_event.type", "path", "size", "perm", "uid",
            "gid", "md5", "sha1", "uname", "gname", "inode"
        ]

    conn.execute(query.format(','.join(select)), request)

    data['items'] = []

    for tuple in conn:
        data_tuple = {}

        if tuple[0] != None:
            data_tuple['scanDate'] = tuple[0]
        if tuple[1] != None:
            data_tuple['modificationDate'] = tuple[1]  # modificationDate
        else:
            data_tuple['modificationDate'] = tuple[0]  # scanDate
        if tuple[2] != None:
            data_tuple['event'] = tuple[2]
        if tuple[3] != None:
            data_tuple['file'] = tuple[3]

        if not summary:
            try:
                permissions = filemode(int(tuple[5], 8))
            except TypeError:
                permissions = None

            if tuple[4] != None:
                data_tuple['size'] = tuple[4]
            if tuple[8] != None:
                data_tuple['md5'] = tuple[8]
            if tuple[9] != None:
                data_tuple['sha1'] = tuple[9]
            if tuple[12] != None:
                data_tuple['inode'] = tuple[12]

            if not windows_agent:
                if tuple[6] != None:
                    data_tuple['uid'] = tuple[6]
                if tuple[7] != None:
                    data_tuple['gid'] = tuple[7]

                if tuple[10] != None:
                    data_tuple['user'] = tuple[10]
                if tuple[11] != None:
                    data_tuple['group'] = tuple[11]

                if tuple[5] != None:
                    data_tuple['octalMode'] = tuple[5]
                if permissions:
                    data_tuple['permissions'] = permissions

        data['items'].append(data_tuple)

    return data
Ejemplo n.º 24
0
def print_db(agent_id=None, status='all', pci=None, cis=None, offset=0, limit=common.database_limit, sort=None, search=None):
    """
    Returns a list of events from the database.

    :param agent_id: Agent ID.
    :param status: Filters by status: outstanding, solved, all.
    :param pci: Filters by PCI DSS requirement.
    :param cis: Filters by CIS.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connection
    db_agent = glob('{0}/{1}-*.db'.format(common.database_path_agents, agent_id))
    if not db_agent:
        raise WazuhException(1600)
    else:
        db_agent = db_agent[0]

    conn = Connection(db_agent)

    request = {}
    fields = {'status': 'status', 'event': 'log', 'oldDay': 'date_first', 'readDay': 'date_last'}

    partial = """SELECT {0} AS status, date_first, date_last, log, pci_dss, cis
        FROM pm_event AS t
        WHERE date_last {1} (SELECT datetime(date_last, '-86400 seconds') FROM pm_event WHERE log = 'Ending rootcheck scan.')"""

    if status == 'all':
        query = "SELECT {0} FROM (" + partial.format("'outstanding'", '>') + ' UNION ' + partial.format("'solved'", '<=') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"
    elif status == 'outstanding':
        query = "SELECT {0} FROM (" + partial.format("'outstanding'", '>') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"
    elif status == 'solved':
        query = "SELECT {0} FROM (" + partial.format("'solved'", '<=') + \
            ") WHERE log NOT IN ('Starting rootcheck scan.', 'Ending rootcheck scan.', 'Starting syscheck scan.', 'Ending syscheck scan.')"

    if pci:
        query += ' AND pci_dss = :pci'
        request['pci'] = pci

    if cis:
        query += ' AND cis = :cis'
        request['cis'] = cis

    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(x + ' LIKE :search' for x in ('status', 'date_first', 'date_last', 'log')) + ")"
        request['search'] = '%{0}%'.format(search['value'])

    # Total items

    conn.execute(query.format('COUNT(*)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting

    if sort:
        allowed_sort_fields = fields.keys()
        for sf in sort['fields']:
            if sf not in allowed_sort_fields:
                raise WazuhException(1403, 'Allowed sort fields: {0}. Field: {1}'.format(allowed_sort_fields, sf))
        query += ' ORDER BY ' + ','.join(['{0} {1}'.format(fields[i], sort['order']) for i in sort['fields']])
    else:
        query += ' ORDER BY date_last DESC'

    query += ' LIMIT :offset,:limit'
    request['offset'] = offset
    request['limit'] = limit

    select = ["status", "date_first", "date_last", "log", "pci_dss", "cis"]

    conn.execute(query.format(','.join(select)), request)

    data['items'] = []
    for tuple in conn:
        data_tuple = {}

        if tuple[0] != None:
            data_tuple['status'] = tuple[0]
        if tuple[1] != None:
            data_tuple['oldDay'] = tuple[1]
        if tuple[2] != None:
            data_tuple['readDay'] = tuple[2]
        if tuple[3] != None:
            data_tuple['event'] = tuple[3]
        if tuple[4] != None:
            data_tuple['pci'] = tuple[4]
        if tuple[5] != None:
            data_tuple['cis'] = tuple[5]

        data['items'].append(data_tuple)

    return data
Ejemplo n.º 25
0
    def get_agents_overview(status="all",
                            offset=0,
                            limit=common.database_limit,
                            sort=None,
                            search=None):
        """
        Gets a list of available agents with basic attributes.

        :param status: Filters by agent status: Active, Disconnected or Never connected.
        :param offset: First item to return.
        :param limit: Maximum number of items to return.
        :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
        :param search: Looks for items with the specified string.
        :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query = "SELECT {0} FROM agent"
        fields = {
            'id': 'id',
            'name': 'name',
            'ip': 'ip',
            'status': 'last_keepalive'
        }
        select = ["id", "name", "ip", "last_keepalive"]
        request = {}

        if status != "all":
            limit_seconds = 600 * 3 + 30
            result = datetime.now() - timedelta(seconds=limit_seconds)
            request['time_active'] = result.strftime('%Y-%m-%d %H:%M:%S')

            if status.lower() == 'active':
                query += ' AND (last_keepalive >= :time_active or id = 0)'
            elif status.lower() == 'disconnected':
                query += ' AND last_keepalive < :time_active'
            elif status.lower() == "never connected":
                query += ' AND last_keepalive IS NULL AND id != 0'

        # Search
        if search:
            query += " AND NOT" if bool(search['negation']) else ' AND'
            query += " (" + " OR ".join(x + ' LIKE :search'
                                        for x in ('id', 'name', 'ip')) + " )"
            request['search'] = '%{0}%'.format(search['value'])

        if "FROM agent AND" in query:
            query = query.replace("FROM agent AND", "FROM agent WHERE")

        # Count
        conn.execute(query.format('COUNT(*)'), request)
        data = {'totalItems': conn.fetch()[0]}

        # Sorting
        if sort:
            allowed_sort_fields = fields.keys()
            for sf in sort['fields']:
                if sf not in allowed_sort_fields:
                    raise WazuhException(
                        1403, 'Allowed sort fields: {0}. Field: {1}'.format(
                            allowed_sort_fields, sf))

            order_str_fields = []
            for i in sort['fields']:
                # Order by status ASC is the same that order by last_keepalive DESC.
                if i == 'status':
                    if sort['order'] == 'asc':
                        str_order = "desc"
                    else:
                        str_order = "asc"
                else:
                    str_order = sort['order']
                order_str_fields.append('{0} {1}'.format(fields[i], str_order))

            if sort['fields']:
                query += ' ORDER BY ' + ','.join(order_str_fields)
        else:
            query += ' ORDER BY id ASC'

        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

        conn.execute(query.format(','.join(select)), request)

        data['items'] = []

        for tuple in conn:
            data_tuple = {}

            if tuple[0] != None:
                data_tuple['id'] = str(tuple[0]).zfill(3)
            if tuple[1] != None:
                data_tuple['name'] = tuple[1]
            if tuple[2] != None:
                data_tuple['ip'] = tuple[2]

            if tuple[3] != None:
                lastKeepAlive = tuple[3]
            else:
                lastKeepAlive = 0

            if data_tuple['id'] == "000":
                data_tuple['status'] = "Active"
                data_tuple['ip'] = '127.0.0.1'
            else:
                data_tuple['status'] = Agent.calculate_status(lastKeepAlive)

            data['items'].append(data_tuple)

        return data
Ejemplo n.º 26
0
def get_agents_without_group(offset=0,
                             limit=common.database_limit,
                             sort=None,
                             search=None,
                             select=None):
    """
    Gets the agents in a group

    :param group_id: Group ID.
    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connect DB
    db_global = glob(common.database_path_global)
    if not db_global:
        raise WazuhException(1600)

    conn = Connection(db_global[0])
    valid_select_fiels = {
        "id", "name", "ip", "last_keepalive", "os_name", "os_version",
        "os_platform", "os_uname", "version", "config_sum", "merged_sum",
        "manager_host", "status"
    }
    # fields like status need to retrieve others to be properly computed.
    dependent_select_fields = {'status': {'last_keepalive', 'version'}}
    search_fields = {"id", "name", "os_name"}

    # Init query
    query = "SELECT {0} FROM agent WHERE `group` IS NULL AND id != 0"
    fields = {'id': 'id', 'name': 'name'}  # field: db_column
    request = {}

    # Select
    if select:
        select_fields_param = set(select['fields'])

        if not select_fields_param.issubset(valid_select_fiels):
            uncorrect_fields = select_fields_param - valid_select_fiels
            raise WazuhException(1724, "Allowed select fields: {0}. Fields {1}".\
                    format(', '.join(list(valid_select_fiels)), ', '.join(uncorrect_fields)))

        select_fields = select_fields_param
    else:
        select_fields = valid_select_fiels

    # add dependent select fields to the database select query
    db_select_fields = set()
    for dependent, dependent_fields in dependent_select_fields.items():
        if dependent in select_fields:
            db_select_fields |= dependent_fields
    db_select_fields |= (select_fields - set(dependent_select_fields.keys()))

    # Search
    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " (" + " OR ".join(x + ' LIKE :search'
                                    for x in search_fields) + " )"
        request['search'] = '%{0}%'.format(
            int(search['value']) if search['value'].isdigit(
            ) else search['value'])

    # Count
    conn.execute(query.format('COUNT(*)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        if sort['fields']:
            allowed_sort_fields = db_select_fields
            # Check if every element in sort['fields'] is in allowed_sort_fields.
            if not set(sort['fields']).issubset(allowed_sort_fields):
                raise WazuhException(1403, 'Allowed sort fields: {0}. Fields: {1}'.\
                    format(allowed_sort_fields, sort['fields']))

            order_str_fields = [
                '{0} {1}'.format(fields[i], sort['order'])
                for i in sort['fields']
            ]
            query += ' ORDER BY ' + ','.join(order_str_fields)
        else:
            query += ' ORDER BY id {0}'.format(sort['order'])
    else:
        query += ' ORDER BY id ASC'

    # OFFSET - LIMIT
    if limit:
        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

    # Data query
    conn.execute(query.format(','.join(db_select_fields)), request)

    non_nested = [{field:tuple_elem for field,tuple_elem \
            in zip(db_select_fields, tuple) if tuple_elem} for tuple in conn]

    if 'id' in select_fields:
        map(lambda x: setitem(x, 'id', str(x['id']).zfill(3)), non_nested)

    if 'status' in select_fields:
        try:
            map(
                lambda x: setitem(
                    x, 'status',
                    Agent.calculate_status(x['last_keepalive'], x['version'] ==
                                           None)), non_nested)
        except KeyError:
            pass

    # return only the fields requested by the user (saved in select_fields) and not the dependent ones
    non_nested = [{k: v
                   for k, v in d.items() if k in select_fields}
                  for d in non_nested]

    data['items'] = [plain_dict_to_nested_dict(d, ['os']) for d in non_nested]

    return data
Ejemplo n.º 27
0
def get_all_groups_sql(offset=0,
                       limit=common.database_limit,
                       sort=None,
                       search=None):
    """
    Gets the existing groups.

    :param offset: First item to return.
    :param limit: Maximum number of items to return.
    :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
    :param search: Looks for items with the specified string.
    :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
    """

    # Connect DB
    db_global = glob(common.database_path_global)
    if not db_global:
        raise WazuhException(1600)

    conn = Connection(db_global[0])

    # Init query
    query = "SELECT DISTINCT {0} FROM agent WHERE `group` IS NOT null"
    fields = {'name': 'group'}  # field: db_column
    select = ["`group`"]
    request = {}

    # Search
    if search:
        query += " AND NOT" if bool(search['negation']) else ' AND'
        query += " ( `group` LIKE :search )"
        request['search'] = '%{0}%'.format(search['value'])

    # Count
    conn.execute(query.format('COUNT(DISTINCT `group`)'), request)
    data = {'totalItems': conn.fetch()[0]}

    # Sorting
    if sort:
        if sort['fields']:
            allowed_sort_fields = fields.keys()
            # Check if every element in sort['fields'] is in allowed_sort_fields.
            if not set(sort['fields']).issubset(allowed_sort_fields):
                raise WazuhException(
                    1403, 'Allowed sort fields: {0}. Fields: {1}'.format(
                        allowed_sort_fields, sort['fields']))

            order_str_fields = [
                '`{0}` {1}'.format(fields[i], sort['order'])
                for i in sort['fields']
            ]
            query += ' ORDER BY ' + ','.join(order_str_fields)
        else:
            query += ' ORDER BY `group` {0}'.format(sort['order'])
    else:
        query += ' ORDER BY `group` ASC'

    # OFFSET - LIMIT
    if limit:
        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

    # Data query
    conn.execute(query.format(','.join(select)), request)

    data['items'] = []

    for tuple in conn:
        if tuple[0] != None:
            data['items'].append(tuple[0])

    return data
Ejemplo n.º 28
0
    def get_agents_overview(status="all", offset=0, limit=common.database_limit, sort=None, search=None):
        """
        Gets a list of available agents with basic attributes.

        :param status: Filters by agent status: Active, Disconnected or Never connected.
        :param offset: First item to return.
        :param limit: Maximum number of items to return.
        :param sort: Sorts the items. Format: {"fields":["field1","field2"],"order":"asc|desc"}.
        :param search: Looks for items with the specified string.
        :return: Dictionary: {'items': array of items, 'totalItems': Number of items (without applying the limit)}
        """

        db_global = glob(common.database_path_global)
        if not db_global:
            raise WazuhException(1600)

        conn = Connection(db_global[0])

        # Query
        query = "SELECT {0} FROM agent"
        fields = {'id': 'id', 'name': 'name', 'ip': 'ip', 'status': 'last_keepalive'}
        select = ["id", "name", "ip", "last_keepalive"]
        request = {}

        if status != "all":
            limit_seconds = 600*3 + 30
            result = datetime.now() - timedelta(seconds=limit_seconds)
            request['time_active'] = result.strftime('%Y-%m-%d %H:%M:%S')

            if status.lower() == 'active':
                query += ' AND (last_keepalive >= :time_active or id = 0)'
            elif status.lower() == 'disconnected':
                query += ' AND last_keepalive < :time_active'
            elif status.lower() == "never connected":
                query += ' AND last_keepalive IS NULL AND id != 0'

        # Search
        if search:
            query += " AND NOT" if bool(search['negation']) else ' AND'
            query += " (" + " OR ".join(x + ' LIKE :search' for x in ('id', 'name', 'ip')) + " )"
            request['search'] = '%{0}%'.format(search['value'])

        if "FROM agent AND" in query:
            query = query.replace("FROM agent AND", "FROM agent WHERE")

        # Count
        conn.execute(query.format('COUNT(*)'), request)
        data = {'totalItems': conn.fetch()[0]}

        # Sorting
        if sort:
            allowed_sort_fields = fields.keys()
            for sf in sort['fields']:
                if sf not in allowed_sort_fields:
                    raise WazuhException(1403, 'Allowed sort fields: {0}. Field: {1}'.format(allowed_sort_fields, sf))

            order_str_fields = []
            for i in sort['fields']:
                # Order by status ASC is the same that order by last_keepalive DESC.
                if i == 'status':
                    if sort['order'] == 'asc':
                        str_order = "desc"
                    else:
                        str_order = "asc"
                else:
                    str_order = sort['order']
                order_str_fields.append('{0} {1}'.format(fields[i], str_order))

            if sort['fields']:
                query += ' ORDER BY ' + ','.join(order_str_fields)
        else:
            query += ' ORDER BY id ASC'

        query += ' LIMIT :offset,:limit'
        request['offset'] = offset
        request['limit'] = limit

        conn.execute(query.format(','.join(select)), request)

        data['items'] = []

        for tuple in conn:
            data_tuple = {}

            if tuple[0] != None:
                data_tuple['id'] = str(tuple[0]).zfill(3)
            if tuple[1] != None:
                data_tuple['name'] = tuple[1]
            if tuple[2] != None:
                data_tuple['ip'] = tuple[2]

            if tuple[3] != None:
                lastKeepAlive = tuple[3]
            else:
                lastKeepAlive = 0

            if data_tuple['id'] == "000":
                data_tuple['status'] = "Active"
                data_tuple['ip'] = '127.0.0.1'
            else:
                data_tuple['status'] = Agent.calculate_status(lastKeepAlive)

            data['items'].append(data_tuple)

        return data