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
