Browse Live Discover and Response Queries by Category
Approved

NOTE: Please review the comments section.

File information for file deleted by Sophos A/V

Hi

I cannot find a table which lists files that have been deleted by the A/V scan due to detected malware. I'm trying to find the file's date time stamp and file size.

I've tried the sophos_file_journal table but it doesn't include the files that were deleted by the scan.

Thanks all.

Jeremy Lloyd1
Parents

  • 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\%';

  • in reply to RaviSoni

    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?

  • in reply to Jainidhya

    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

  • in reply to Jeremy Lloyd1

    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 

Comment
  • in reply to Jeremy Lloyd1

    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 

Children
  • in reply to Jainidhya

    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

Unfiltered HTML