reportFileActivityOnUsbLast24hrs

/*
--author: @jkopacko
+= Descriptive names: N/A
+= Variable type: N/A
+= Value: N/A
+= Version 1.0 - N/A
+= Query type: Live Discover
+= OS Support: Windows
*/

SELECT datetime(time,'unixepoch','localtime') AS time,
CASE event_Type
   WHEN 0 THEN 'Created'
   WHEN 1 THEN 'Renamed'
   WHEN 2 THEN 'Deleted'
   WHEN 3 THEN 'Modified'
   WHEN 4 THEN 'Hardlink Created'
   WHEN 5 THEN 'Modified'
   WHEN 6 THEN 'Permissions Modified'
   WHEN 7 THEN 'Ownership Modified'
   WHEN 8 THEN 'Binary File Mapped'
END AS Action,
sophos_pid,
datetime(process_Start_Time,'unixepoch','localtime') AS processStartTime,
file,
file_extension,
path,
CASE path_type
   WHEN 0 THEN 'Unknown'
   WHEN 1 THEN 'RAW'
   WHEN 2 THEN 'NTFS'
   WHEN 22 THEN 'EXFAT'
   ELSE 'Check FLT File System Type in Schema'
END AS path_type,
datetime(creation_Time,'unixepoch','localtime') AS creationTime,
datetime(last_Access_Time,'unixepoch','localtime') AS lastAccess,
datetime(last_Write_Time,'unixepoch','localtime') AS lastWrite
FROM sophos_file_journal
WHERE time > STRFTIME('%s',"NOW",'-24 HOURS')
AND time < STRFTIME('%s',"NOW",'-0 HOUR')
AND PATH NOT LIKE 'C:\%%'
ORDER BY TIME DESC