Under Review

Find only new created files by extension

Hi,

I did a copy of the default live query: File access history

I'm only interested in new files that have been created in that timeframe.

The demand is a bit like the default "New applications deployed" query. But not only for applications.

In the default code of "file access history" I removed everything except "created"

SELECT
    STRFTIME('%Y-%m-%dT%H:%M:%SZ', DATETIME(file_journal.time, 'unixepoch')) AS date_time,
    process_journal.process_name AS process_name,
    CASE file_journal.event_type
        WHEN 0 THEN 'Created'
    END AS event_type,
    REPLACE(file_journal.path, RTRIM(file_journal.path, REPLACE(file_journal.path, '\', '')), '') AS file_name,
    process_journal.path AS process_path,
    file_journal.path AS file_path,
    file_journal.sophos_pid AS sophos_pid,
    process_journal.sha256 AS sha256,
    process_properties.ml_score AS ml_score,
    process_properties.pua_score AS pua_score,
    process_properties.local_rep AS local_rep,
    process_properties.global_rep AS global_rep
FROM sophos_file_journal AS file_journal
LEFT JOIN sophos_process_journal AS process_journal ON
    process_journal.sophos_pid = file_journal.sophos_pid
LEFT JOIN sophos_process_properties AS process_properties
    USING (sophos_pid)
WHERE
    file_journal.path LIKE '$$file_path$$'
    AND file_journal.time >= $$start_time$$
    AND file_journal.time <= $$end_time$$
ORDER BY file_journal.time DESC

Unfortunately it shows a lot of undescribed file access logs like this when searching for .exe files as an example.

Any Idea how to modify the script that it only finds the "Created" events?

Regards