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
def group_exists_sql(group_id):
"""
Checks if the group exists
:param group_id: Group ID.
:return: True if group exists, False otherwise
"""
# Input Validation of group_id
if not InputValidator().group(group_id):
raise WazuhException(1722)
db_global = glob(common.database_path_global)
if not db_global:
raise WazuhException(1600)
conn = Connection(db_global[0])
query = "SELECT `group` FROM agent WHERE `group` = :group_id LIMIT 1"
request = {'group_id': group_id}
conn.execute(query, request)
for tuple in conn:
if tuple[0] != None:
return True
else:
return False
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
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)
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()
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 Exception as exception:
raise WazuhException(1654, exception)
finally:
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"
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)
def last_scan(agent_id):
"""
Gets the last scan of the agent.
:param agent_id: Agent ID.
:return: Dictionary: end, start.
"""
Agent(agent_id).get_basic_information() # check if the agent exists
# 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_last) FROM pm_event WHERE log = 'Ending rootcheck scan.'"
conn.execute(query)
time = conn.fetch()
data['end'] = datetime.utcfromtimestamp(time).strftime(
"%Y-%m-%d %H:%M:%S") if time is not None else "ND"
# start time
query = "SELECT max(date_last) FROM pm_event WHERE log = 'Starting rootcheck scan.'"
conn.execute(query)
time = conn.fetch()
data['start'] = datetime.utcfromtimestamp(time).strftime(
"%Y-%m-%d %H:%M:%S") if time is not None else "ND"
return data
def get_cis_local(agent_id=None,
offset=0,
limit=common.database_limit,
sort=None,
search=None):
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:
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 = 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 cis ' + sort['order']
else:
query += ' ORDER BY cis {0}'.format(sort['order'])
else:
query += ' ORDER BY cis ASC'
if limit:
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
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
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()
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()
def last_scan(agent_id):
"""
Gets the last scan of the agent.
:param agent_id: Agent ID.
:return: Dictionary: end, start.
"""
# 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_last) FROM pm_event WHERE log = 'Ending rootcheck scan.'"
conn.execute(query)
for tuple in conn:
data['end'] = tuple[0]
# 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[0]
return data
def last_scan_local(agent_id):
# 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_last) FROM pm_event WHERE log = 'Ending syscheck scan.'"
conn.execute(query)
for tuple in conn:
data['end'] = tuple[0]
# start time
query = "SELECT max(date_last) FROM pm_event WHERE log = 'Starting syscheck scan.'"
conn.execute(query)
for tuple in conn:
data['start'] = tuple[0]
return data
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 pm_event')
except Exception as exception:
raise exception
finally:
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"
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
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"
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
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
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)
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"
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")
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
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")
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
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
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
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
}
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
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
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)}
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}
def connect_to_db(self):
if not glob.glob(self.db_path):
raise WazuhException(1600)
return Connection(self.db_path)
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
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
