EDR Query to list deleted files in a directory

Description

We've been asked a few times if its possible to write a quick query to list files deleted in a particular directory. This query below is a modification of the existing File Access History Query to just show deleted files for a specified directory (for example c:\windows\temp\% ).

Code

Define Input Variables

startTime in Date format

endTime in Date format

filePath as a Path

SQL Code

SELECT

    strftime('%Y-%m-%dT%H:%M:%SZ', datetime(sfj.time,'unixepoch')) dateTime,

    spj.processName,

     CASE sfj.eventType

         WHEN 0 THEN 'Created'

         WHEN 1 THEN 'Renamed'

         WHEN 2 THEN 'Deleted'

         WHEN 3 THEN 'Modified'

         WHEN 4 THEN 'HardLink Created'

         WHEN 5 THEN 'Timestamps Modified'

         WHEN 6 THEN 'Permissions Modified'

         WHEN 7 THEN 'Ownership Modified'

         WHEN 8 THEN 'Accessed'

         WHEN 9 THEN 'Binary File Mapped'

    END eventType,

    replace(sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '') fileName,

    spj.pathname processPath,

     sfj.pathname filePath,

     sfj.sophosPID,

     spj.sha256,

     spp.mlScore,

     spp.puaScore,

     spp.localRep,

     spp.globalRep

FROM sophos_file_journal sfj

LEFT JOIN sophos_process_journal spj

    ON spj.sophosPID = sfj.sophosPID

    AND spj.time = replace(sfj.sophosPID, rtrim(sfj.sophosPID, replace(sfj.sophosPID , ':', '')), '')/10000000-11644473600

LEFT JOIN sophos_process_properties spp

    ON spp.sophosPID = spj.sophosPID

WHERE sfj.pathname LIKE '$$filePath$$'

AND sfj.time > $$startTime$$

AND sfj.time < $$endTime$$

AND sfj.eventType = '2'

ORDER BY sfj.time DESC