File information for file deleted by Sophos A/V

This is a query to find out file information deleted by Sophos AV.

--YOU CAN EDIT THIS AND SPECIFY THE TIME

--GO TO LINE 23 and EDIT '-2 hours'

SELECT
CAST(replace(datetime(sfj.time,'unixepoch','localtime'),'','Time') AS TEXT) dateTime,
   --'File' type,
CAST(CASE sfj.fileType
   WHEN 0 THEN 'Unknown'
   WHEN 1 THEN 'Portable Executable'
   WHEN 2 THEN 'Executable and Linkable format (ELF binary)'
END AS TEXT) fileType,
   sfj.filesize,
   sfj.pathname,
CAST(CASE sfj.eventtype
   WHEN 2 THEN 'Deleted'
END AS TEXT) eventType
FROM sophos_file_journal sfj
WHERE sfj.sophospid IN (
SELECT
    spp.sophosPID
FROM processes p 
LEFT JOIN sophos_process_properties spp
    ON spp.pid = p.pid
    WHERE PATH = 'C:\Program Files (x86)\Sophos\Sophos Anti-Virus\SavService.exe') 
    AND eventType = '2'
    AND datetime > STRFTIME('%H','NOW','-2 hours')
    AND sfj.pathname NOT LIKE 'C:\ProgramData\Sophos\%';

Question... should "STRFTIME('%H','NOW','-2 hours')" not be "DATETIME('NOW','-2 hours')" as the %H modifier for STRFTIME returns just an hour, not a full date time stamp?

Also, do you know whether the processes or sophos_process_properties tables survive a reboot? If not would the sophos_process_journal table with a filter of spj.eventType=0, be better?

Question... should "STRFTIME('%H','NOW','-2 hours')" not be "DATETIME('NOW','-2 hours')" as the %H modifier for STRFTIME returns just an hour, not a full date time stamp?

Also, do you know whether the processes or sophos_process_properties tables survive a reboot? If not would the sophos_process_journal table with a filter of spj.eventType=0, be better?

Hi JR, in this example query the use of STRFTIME is not equivalent to DATETIME. The use of STRFTIME in the example does not seem correct to me, since it simply returns the hours portion of the current time and compares that against an actual date-time stamp. Try this query to see what I mean:

select STRFTIME('%H','NOW','-2 hours') as time1, DATETIME('NOW','-2 hours') as time2

Could you confirm how long the processes and sophos_process_properties tables keep process information after the relevant process has terminated.

Many thanks

Jeremy

Hi Jeremy,

You can use both STRFTIME and DATETIME according to your convenience. Yes, it survives a reboot and the eventType is taken from the Sophos_file_journal.

Please let us know if you need a specific modification or if you have a certain requirement. We can then go ahead and modify the query accordingly. 

Regards,

JR